We can make use of "SQLCMD" to execute scripts from Command prompt. If you are new to it I would suggest to go over my introductory posts on them here.
Example 1: Writing a batch file with the TSQL script to backup a DB.
Save the below sample script as a batch file (lets say, DBBackup.Bat).
echo off
cls
set /p DBNAME=Enter database name:
set BACKUP=c:\%DBNAME%.bak
set SQLSERVERNAME=Enter_Your_SQL_ServerName_here
echo.
sqlcmd -E -S %SQLSERVERNAME% -d master -Q "BACKUP DATABASE [%DBNAME%] TO DISK = N'%BACKUP%'"
echo.
pause
We can now go to the SQL Command prompt by typing SQLCMD in Start > Run dialogue box. On executing DBBackup.Bat it would prompt us to enter the name of the database which has to be backed up. The backup file would be stored in C:\ drive. This is just a sample which can be modified or extended according to our need.
Just in case we have already have a Stored procedure to backup the DB for us. Then we can still make use of that SP directly within the Batch file.
Example 2: Calling a .SQL file within the batchfile to do the DB backup.
Save the below script as a SQL file. Say, "Backup.SQL"
/*
Have provided the simpler script for demo purpose. You can get the DB name, backup name as parameters as well. And convert this into a solid Stored proc.
Change TESTDW with your database name.
*/
BACKUP DATABASE TESTDW TO DISK = N'c:\testdw.bak'
GO
Now the batch file DBBackup.BAT can be modified as follows:
echo off
cls
echo.
sqlcmd -E -S Your_ServerName -d master -i "C:\Backup.sql"
echo.
pause
In both the samples above I have used trusted connection. If you want to pass username and password change the SQLCMD script to use -U and -P as shown below instead of -E:
sqlcmd -S Your_ServerName -UyourUserName -PyourPwd -d master -i "C:\Backup.sql"
Comments