Saturday, August 20, 2011

Database Backup from Command prompt

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"

No comments: