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 Qiling 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:
✍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 Proceed 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 Qiling 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.
Related Articles
- Guide | Backup and Restore SQL Database with Command Line
You can backup and restore SQL database via command line, which is especially useful in Express editions that lack schedule backup capabilities. Here's a quick guide for you to backup database using SQLCMD. - How to Use PowerShell Script to Backup Multiple SQL Databases
Have multiple SQL databases that need to be backed up? Go through this article to learn two effective backup solutions – PowerShell script and Qiling Centralized Disk Master. - Schedule Database Backup in SQL Server 2012/2014/2016/2019
You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included. - Backup SQL Server Database and Restore to Another Server | Guide
Here's a simple guide to backup SQL Server databases and restore to another server. In addition, you can also migrate databases via Copy Database Wizard, or try a centralized backup solution. Keep reading to learn the details.