Automate SQL Server Database Backups using SQLCMD and Windows Task Scheduler
Creating database backups is key to addressing data corruption or database failures. This procedure is repetitive and time-consuming, especially if there are multiple databases and instances of SQL Server. In addition, creating backups manually can be prone to errors. Is there a way to automate this tedious process?
You can automate the process of creating database backups using a SQLCMD batch file and Windows Task Scheduler. In this SQL tutorial, you will learn how to create a SQL file containing code to create backups for all your databases and a batch file to execute the SQL commands. Then, you will create a scheduled task with Windows Task Scheduler to automate the process.
Creating SQL Server Database Backups
Having backups of essential data in your database is always good practice. So, if you are working on a SQL Server database for the first time, you must ensure that you have a backup before you make any changes to the database.
To create backups for all databases on your SQL Server instance, you can use the code below. This code should be saved to a file named DbBackup.sql. The SQL code below finds all databases, excluding system databases and databases offline and then creates backups for each database.
-- source: https://www.MSSQLTips.com --declare variables DECLARE @dbname VARCHAR(50) -- database name DECLARE @file_path VARCHAR(256) -- path DECLARE @file_name VARCHAR(256) -- DECLARE @file_date VARCHAR(20) -- used for filename --set path for the backup file SET @file_path = 'C:\SQLCMD\backup\' -- ensure this directory exists on your machine SELECT @file_date = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') --declare the cursor DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping --open cursor OPEN db_cursor FETCH NEXT FROM db_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN --set file names based on the database name and datetime when the backup was made SET @file_name = @file_path + @dbname + '_' + @file_date + '.BAK' --create backup BACKUP DATABASE @dbname TO DISK = @file_name --fetch the next database on the server instance FETCH NEXT FROM db_cursor INTO @dbname END --close cursor CLOSE db_cursor DEALLOCATE db_cursor
After creating the SQL file, create a batch file named DbBackUp.bat in the same folder and add the following code that will use the SQL commands in DbBackup.sql. You will need to change the name of the server, DESKTOP-M5LVFVI\KNITNETSERVER, to your SQL Server instance name.
@echo off --connecting to your database using sqlcmd and calling your sql file. Any errors will be sent to the error.log file. sqlcmd -S DESKTOP-M5LVFVI\KNITNETSERVER -b -E -i DbBackUp.sql > error.log --print error message or success message if %errorlevel% neq 0 ( echo There was an error running the script. Please check the error.log file for more information. ) else ( echo The script ran successfully. ) --Make sure the CMD prompt does not close automatically. pause
These are the parameters I am using for SQLCMD.
- -b (terminate batch job if there is an error)
- -E (use trusted connection)
- -i (input_file)
- -S (server\instance)
The output on the command line should be similar to that shown below:
Now, navigate to the directory you specified in the script to check the database backups.
SQL Server Database Restore
Once you have database backups, you can use these backups to restore your databases if needed.
To recover your database using the backups, create a SQL file named RecoverDB.sql and add the following code to it.
-- source: https://www.MSSQLTips.com DECLARE @dbname VARCHAR(50) -- database name DECLARE @file_path VARCHAR(256) -- path --Set the file for the backup file to be used for recovery SET @file_path = 'C:\SQLCMD\backup\MSSQLTIPsDB_20230429_095316.BAK' -- ensure this file exists on your machine --set the name for the database to be recovered, this should match the first part of the backup file SET @dbname = 'MSSQLTIPsDB' RESTORE DATABASE @dbname FROM DISK = @file_path WITH REPLACE, RECOVERY;
After creating the SQL file, create a new batch file called RecoverDB.bat in the same directory as the SQL file and add the following code to it. You will need to change the name of the server, DESKTOP-M5LVFVI\KNITNETSERVER, to your SQL Server instance name.
@echo off sqlcmd -S DESKTOP-M5LVFVI\KNITNETSERVER -b -E -i RecoverDB.sql > error.log --print error message or success message if %errorlevel% neq 0 ( echo There was an error running the script. Please check the error.log file for more information. ) else ( echo The script ran successfully. ) --Make sure the CMD prompted does not close automatically. pause
Save the file and double-click on the bat file to run the script. When you run the script your database will be restored as shown below.
After you restore your database backup, the current state of the database will be changed to the state of the database when the backup was created. This can be helpful if your database gets corrupted or you want to roll back the state of your database for another reason.
Automating SQL Server Backup Execution
To complete the automation, you can use Windows Task Scheduler to schedule a time for the batch file to run.
Use the Windows search bar to search for "task scheduler" and open the application.
Click on "Create Basic Task," as shown below.
Name your task and give a description.
Select start date and time and how often it should run.
Choose action, this asks you what action you want to perform, in our case we will run a program.
After selecting Start a Program, browse to the batch file and select it.
Finish by reviewing the items and click Finish to save.
This SQL tutorial shows how to create database backups and automate the process. This is useful if you must regularly create database backup for your databases. This enables you to stop worrying about backups, and if there is an error in the process you can review the error.log files.
- If you prefer using SSMS, learn How to monitor backup and restore progress in SQL Server.
- You can Check to make sure a SQL Server backup is useable.
- Simple script to backup all SQL Server databases
About the author
View all my tips
Article Last Updated: 2023-06-27