Automated Backup of Multiple Databases in SQL Server using Transact Script

Posted on by

When it comes to backing up multiple databases on a large SQL Server instance, manual selection can be time-consuming and prone to errors. In this post, we will demonstrate how to automate the process using a Transact-SQL script that selects specific databases based on a naming convention.

Introduction

SQL Server provides various methods for backing up databases, including using the built-in backup wizard. However, when dealing with hundreds of databases, manually selecting each one can be challenging. In this solution, we will use a T-SQL script to automate the backup process by targeting databases that match a specific naming pattern.

Step 1: Define Variables and Paths

The first step in creating the script is to define variables for the path where you want to store the backup files and a variable for the file name. You can adjust these values according to your needs.

DECLARE @path VARCHAR(256) -- Path for backup files
DECLARE @fileName VARCHAR(256) -- Filename for backup
DECLARE @fileDate VARCHAR(20) -- Used for file name

SET @path = 'D:\MSSQLBCK'

In this example, we set the path to D:\MSSQLBCK, which is a logical location for your backup files. You can modify this value as per your preferences.

Step 2: Get the Current Date and Time

To include the current date in the file name, you need to get the current date and time using the GETDATE() function.

SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

This line retrieves the current date and converts it into a format that can be used as part of the file name.

Step 3: Create a Cursor to Iterate Through Databases

To iterate through the databases you want to back up, create a cursor that targets specific databases based on their names. In this case, we are selecting databases with names starting with ‘PRI-‘.

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name LIKE 'PRI%'

This line creates a cursor named db_cursor that will retrieve the names of all databases starting with ‘PRI-‘. You can modify this condition to match your specific naming pattern.

Step 4: Backup Each Database

Once you have iterated through the list of databases, use a WHILE loop to backup each one. Use the BACKUP DATABASE statement followed by the database name and the path where you want to store the backup file.

SET @fileName = @path + @name + '\' + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

This line sets the file name for each backup by concatenating the path, database name, and current date. It then backs up the selected database to the specified location.

Step 5: Close and Deallocate the Cursor

After iterating through all databases, close and deallocate the cursor to free up resources.

CLOSE db_cursor
DEALLOCATE db_cursor

This line closes and releases any system resources associated with the db_cursor cursor.

Conclusion

Automating database backups using T-SQL scripts can significantly improve efficiency and reduce manual errors. By following these steps, you can create a script that targets specific databases based on a naming convention, making it easier to manage large SQL Server instances. Remember to adjust variables such as path and file name formats according to your needs.

Note: Please modify the WHERE condition in step 3 to match your specific database naming pattern.