Example Batch File to Take Sql Server Database Backup

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. ClickCreate Basic Task and follow the wizard to choose aTrigger. Options include daily, weekly, monthly, etc.

3. Set theAction 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 centralized backup software AOMEI Centralized Backupper Database contains a SQL Server Backup feature that supports SQL 2005-2019. 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 supported.

Here's a trial version for you to try:

After installing Centralized Backupper Database on the central machine, you can install the Agent package on all clients, and Request Control over the computers you want to manage. After than, you can try following steps to create a SQL Server Backup task.

1. Navigate to Home tab and choose SQL Server Backupto get started.

2. In Step 1, click + Add Computers to detect all the controlled computers with SQL Server database, and select the one you want to backup.

3. Click on Step 2, and hit + Add to specify the source data in the pop-up window. You can select multiple databases or even multiple instances to backup.

4. Click onStep 3 to select destination. HitAdd Storage on the popping out window, here you can enter the path and authentication to specify a network share NAS. The added path will be saved for your latter use.

5. Now you can click Schedule to automate the backup, and choose backup type (full/differential) in Advanced tab. After that, click Start Backup.

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

There are several methods to backup SQL Server databases, batch script 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 Centralized Backupper Database Edition 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.

Example Batch File to Take Sql Server Database Backup

Source: https://www.ubackup.com/articles/batch-script-to-backup-sql-server-database-1021.html

Share this post