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 stored in text files that can automate repetitive tasks, including SQL Server backup. While batch scripting may not be the most intuitive way to learn, it's a productive method for backing up databases in bulk or on a regular schedule.
To back up SQL databases, you can use batch scripts to automate the process. You can create a script to backup a single database or all databases of an instance.
- 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 starting, ensure the SQLCMD utility is installed on your machine, as it is a prerequisite for entering Transact-SQL statements, system procedures, and script files.
If you're using SQL Server 2014 or earlier, SQLCMD is included with SSMS, but for SQL Server 2016 and later, you'll need to download and install it separately. You can find more information on the SQLCMD utility and its syntax on this page.
You can use the following command to backup a SQL Server database using a batch script:
SqlCmd -E -S servername -Q "BACKUP DATABASE databasename TO DISK ='filepath\filename.bak'"
If you only need to back up one or a few databases without any additional requirements, you can simply enter a command in a text editor and fill in your own information. However, if you want to keep different versions of backups, with each file containing a specific backup time, you can use a 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 -The command to backup a SQL Server database is: BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT, NOUNLOAD, NAME = N'%DATABASENAME% backup', NOSKIP, STATS = 10, NOFORMAT. This command is executed on a server named %SERVERNAME% in master mode, using the -Q flag.
ECHO.
To automate the SQL database backup process, fill in the backup path, server name, and database name in the script, then save it with a .bat extension. This allows you to double-click the batch file to initiate a backup whenever needed.
Batch script to backup all SQL databases of an instance
You can use a script that loops through all the databases in a SQL Server instance and performs a backup for each one. This script can be written in T-SQL and use a cursor or a WHILE loop to iterate over the list of databases. The script can then use the BACKUP DATABASE statement to backup each database.
Here is a brief summary of the task:
@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
The command "FOR /F" is used to parse the output of the "Date /T" command, which displays the current date in the format "Day of the Week, Month DD, YYYY". The "tokens=1,2,3,4 delims=/ " option tells the "FOR" loop to split the output into four tokens (fields) separated by the "/" character.
REM Build a list of databases to backup
SET DBList=D:\SQLDBList.txt
SqlCmd -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 can modify the script to build the DBlist and delete it afterwards, as well as change the folder to save backups. The script currently excludes system databases ('master','model','msdb','tempdb') from backups, but you can add other databases to exclude as needed.
To make backup management easier, some users may want to create a time-labeled folder to keep all .bak files created by each backup separate. I can 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)
The command `FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C` can be used to set the current date in a batch script. It uses the `Date /T` command to get the current date, and then uses a `FOR` loop to extract the day, month, and year from the output, which are then used to set the `NowDate` variable.
REM Build a list of databases to backup
SET DBList=D:\SQLDBList.txt
SqlCmd -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
The script creates a backup of the original file by copying it to a new file with a '.bak' extension. It then renames the original file to a new name, which can be customized as needed.
Automate SQL database backup batch file with Task Scheduler
To backup a SQL database, create a batch file with the following steps: add the SQL Server Management Studio (SSMS) executable to the path, set the path to the backup file, and run the SQL command to backup the database. Once you have the batch file, you can schedule it to run automatically using Windows Task Scheduler. This allows the backup process to run without manual intervention, ensuring your database is regularly backed up.
1. To access the Task Scheduler, you can either search for "task scheduler" directly in your computer's search bar, or navigate to the Control Panel, then select Administrative Tools, and finally click on Task Scheduler.
2. To create a basic task in Windows Task Scheduler, click on "Create Basic Task" and follow the wizard to choose a trigger. This can be set to occur daily, weekly, monthly, or at a specific date and time.
3. To set up the task to run a batch script, set the "Action" as "Start a program", then select the batch script from your local drive.
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
Using a batch script to backup SQL Server databases offers several benefits, including the ability to backup multiple databases at once, and the simplicity to perform and automate backups. As long as these needs are met, utilizing an efficient alternative with a graphical user interface (GUI) is not a bad idea.
Qiling Backup, an enterprise backup software, offers a SQL Server Backup feature that supports SQL 2005-2022, allowing users to protect Windows PCs or servers and select databases or instances to backup and restore.
Automation is also possible, allowing for scheduled backups on a daily, weekly, or monthly basis, with full and differential backup options supported. This feature is not limited to SQL databases, as 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 It helps you to automatically delete older backup versions, saving storage space.
- Email Notification This feature allows you to receive email notifications when a task is either abnormal or successful.
Once the backup is complete, you can click on the "Proceed" button 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. The selected databases will be backed up automatically according to the schedule you set.
Conclusion
Using a batch script to backup SQL Server databases may not be the simplest method, but it offers advantages in terms of automatic and batch backup. This approach allows for simultaneous backup of all databases in an instance, and can be automated using Windows task scheduler, providing a flexible and efficient backup solution.
If you find it troublesome to customize a batch script, you can consider using SQL backup software like Qiling Backup, which guides you through the backup process and allows you to select multiple databases to back up at once. It also offers schedule options for auto-backup SQL databases.
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.