Automate SQL Server Backups using SQLCMD and Windows Task Scheduler

By: Levi Masonde | Updated: 2023-06-27 | Comments (2) | Related: > Backup

Problem

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?

Solution

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.

Conclusion

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.

Next Steps

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

Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-27

Automate SQL Server Database Backups using SQLCMD and Windows Task Scheduler