Take SQL Server database backup using batch file

In this post, I will share the script to take SQL database backup from batch file. There may be scenarios where you want to automate SQL database backups, in that case this script will be useful. Although there is SQL Server Agent feature available which helps you to automate and perform different set of powerful tasks including database backups. But at times you may need a custom solution like a batch file which could take the backup.

We will use sqlcmd command for this purpose. Following are the parameters we need to pass to sqlcmd.

• -S (server name)
• -U (user name)
• -P (password)
• -d (database name)
• -Q (query)

In our script we will first define the parameter's values required to connect the desired database server. These are server, dataBase, user and password.

Next we will define variable targetPath, declaring the target folder path where we want to save the backup file.

These are the 5 variables we need to pass as arguments to sqlcmd.

Often we prefer to add timestamp in backup file name. To add current date/time stamp in file name please refer to my last post Get date-time value to string variable in batch file. However I want to keep this post simple, so I am not using timestamp string sufix in filename for this example.

Here is the final script:

:: Off commands display
@echo off

:: This batch file create backup for SQL Server Database
:: Written by Muhammad Idrees


:: set database connection parameters
:: ---------------------------------------------------------------------------------------------

:: Your server name. I am using server name 'MyServer'
SET server=MyServer

:: Your database name. I am using database name 'MyDatabase'
SET dataBase=MyDatabase

:: Your database-server's user name. My user name is 'sa'
SET user=sa

:: Your database-server's password. My password is '123'
SET password=123

:: ---------------------------------------------------------------------------------------------

:: Set destination path name to save the backup file.
SET targetPath=C:\Backups\

:: Set full name of the backup.
SET fullName=MyDatabase_FullBackup 

:: *****************************************************************************

sqlcmd -S %server% -U %user% -P %password% -d %dataBase% -Q "BACKUP DATABASE %dataBase% TO  DISK = '%targetPath%%dataBase%.bak' WITH NOFORMAT, INIT,  NAME = N'%fullName%', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;"

ECHO Backup finished...

I hope this helps someone looking for batch file solution of taking SQL server backups. I welcome your feedback and suggestions in the comments section below.

Related Posts:

• Get date-time value to string variable in batch file