How to Use Batch Script to Backup SQL Server Databases

Why use batch script to backup SQL Server database

In Windows, batch scripts are command sequences that stored in text files and can be used to automate repetitive tasks. When it comes to SQL Server backup, batch scripting may not be the easiest way to learn, but it is certainly a productive one when you need to back up databases in bulk or on a regular basis.

In the following article, I will share the batch scrips to back up SQL databases (even all databases of an instance), and the way to automate the task.

• How to backup SQL Server database with batch script
• Easy alternative: auto backup multiple SQL Server databases or instances

How to backup SQL Server database with batch script

Before you start, please make sure the SQLCMD utility is installed on your machine, because it’s the prerequisite for entering Transact-SQL statements, system procedures, and script files.

If you are running SQL Server 2014 or earlier versions, the utility is shipped with SSMS. But from SQL Server 2016 onwards, you need to install it separately. Download SQLCMD utility and learn its syntax in this page.

With this utility, you can write batch script to backup SQL Server database. The basic command is:

SqlCmd -E -S servername -Q "BACKUP DATABASE databasename TO DISK ='filepath\filename.bak'"

If you only want to backup one or a few databases without additional requirements, just enter this command in text editor and fill in your own information. But if you want to keep different versions of backup, and each file contains the specific backup time, then you can use the following script:

ECHO OFF
:: set path to save backup files e.g. D:\backup
set BACKUPPATH=

:: set name of the server and instance
set SERVERNAME=

:: set database name
set DATABASENAME=

:: filename format Name-Date
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)

set DATESTAMP=%mydate%_%mytime%
set BACKUPFILENAME=%BACKUPPATH%\%DATABASENAME%-%DATESTAMP%.bak

SqlCmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT, NOUNLOAD, NAME = N'%DATABASENAME% backup', NOSKIP, STATS = 10, NOFORMAT"
ECHO.

Fill in your backup path, server name and database name, then save this backup script with .bat extension. Thus you can double-click the batch file to backup SQL database anytime you want.

Batch script to backup all SQL databases of an instance

In some case, you may want to back up not just one or two databases, but all the databases in a SQL Server instance. What kind of backup script can help you do this?

In fact, you just need to build a database list and specify the ones you want to exclude from it. For example, I want backup all my SQL databases (except for system databases) to D:\Backup, then the following batch script will work:

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=D:\SQLDBList.txt
SqlCmd -E -S DESKTOP-AC51C0M -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S DESKTOP-AC51C0M -Q "BACKUP DATABASE [%%I] TO Disk='D:\Backup\%%I-%NowDate%.bak'"
ECHO.)

REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

You could modify the path to build the DBlist (it will be auto deleted afterwards) and the folder to save the backups. Also, this script will not back up system databases ('master','model','msdb','tempdb'), you could add other databases that you want to exclude.

For easier management, some users may want to create a time-labeled folder to keep all bak files created by each backup separately. So I would also provide an alternative script for your reference.

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=D:\SQLDBList.txt
SqlCmd -E -S DESKTOP-AC51C0M -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

mkdir D:\Backup
mkdir D:\Backup\%NowDate%
REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S DESKTOP-AC51C0M -Q "BACKUP DATABASE [%%I] TO Disk='D:\Backup\%NowDate%\%%I.bak'"
ECHO.)

REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

It differs from the previous script only in creating and naming the backup files. You can choose as needed.

Automate SQL database backup batch file with Task Scheduler

With above steps, you’ve got a batch file to backup SQL database. To execute it automatically, you can use Windows Task Scheduler.

1. Search for “task scheduler” directly, or navigate to Control Pane > Administrative Tools > Task Scheduler.

2. Click Create Basic Task and follow the wizard to choose a Trigger. Options include daily, weekly, monthly, etc.

3. Set the Action as “Start a program”, and browse your local drive to select the batch script.

After completing the setup, Task Scheduler will execute the selected batch script according to the schedule you set.

Alternative: Auto backup multiple SQL Server databases or instances

The main benefits of using batch script to backup SQL Server databases are the ability to backup a large number of databases at once, the simplicity to perform and automate backup. As long as these points can be fulfilled, it is not a bad idea to use efficient alternative with GUI.

For example, the enterprise backup software AOMEI Cyber Backup contains a SQL Server Backup feature that supports SQL 2005-2022. With it, you can protect any Windows PC or server signed as client within LAN. And you are free to select databases or the entire instance to backup and restore.

As for automation, you could set up a schedule to run the backup on daily/weekly/monthly basis. Full backup and differential backup are both supported. Besides SQL databases, it also supports virtual machine backup (for VMware ESXi and Hyper-V).

You can download the 30-day free trial and get a up-to 60% off discount:

After installing AOMEI Cyber Backup on the server machine, launch the main interface, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

Choose Download proxy program or Copy link and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device to Confirm it.

After that, click  -> Authentication to validate the database instance. And then you can follow the steps below to back up SQL database.

✍How to batch backup SQL databases automatically:

1. Click Backup Task -> Create New Task, and select Backup Type as Microsoft SQL Backup.

2. Tap Device Name to select the SQL instances and databases for backup. You can select one or multiple databases as you need.

3. Specify a local path or network path as backup Target. To back up SQL database to remote location, you could click Add a new network storage. The added path will be saved in Add storage.

4. Set up a Schedule to set up an automatic SQL dabase backup such as daily/weekly/monthly, and set the backup method as Full/Incremental/Differential.

✍More useful features:

• Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

Now you can click Start Backup to create and execute the SQL Server automatic backup to the target you specified.

Once the task is executed, you don't need to worry about follow-up. All the selected databases will be backed up automatically according to the schedule you set.

Conclusion

Among all the methods, using batch script to backup SQL Server databases may not be the simples one, but it does have some advantages in terms of automatic and batch backup. For example, you could back up all the databases in an instance at once and automate the task flexibly with Windows task scheduler.

However, if you find it truly troublesome to customize a batch script, SQL backup software like AOMEI Cyber Backup is also a good idea. It will walk you through the backup process intuitively, and allows you to select as many databases as you like on a machine. If you want to auto backup SQL databases, then the schedule options can also satisfy your needs.