How can we bulk backup/restore databases in SQL Server

How can we bulk backup/restore databases in SQL Server
How can we bulk backup/restore databases in SQL Server

Many times we may need to backup several small databases and transfer them to another system or restore them to the same one. His process backup/restore of each base one by one will be time consuming and may lead to errors. In this article we will look at one T-SQL script that I have written for this work and how it is used.

The information we need is in the system views master_files and databases. To extract this information we will need to use cursor ή Common Table Expression (CTE). I have written this particular script using CTE.

In newer versions from SQL Server 2017 and later, with the function STRING_AGG we can join in one line all the paths for the files needed for the restore. But because we want the script to work in older versions as well, we will use the function stuff along with Xml PATH.

All we'll need to tweak are the parameters backup_loc, rest_dbf, rest_log and skipped dbs (located at the end of the script). In these parameters we define where the backup files should be placed and where they will be restored. We also have the possibility to exclude databases from the process such as the systemic ones that I have already excluded.

The script

SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @backup_loc VARCHAR(125)  
SET @backup_loc = 'X:\backups\'  -- Σε ποιά τοποθεσία θα παρθούν τα backups

DECLARE @rest_dbf VARCHAR(125)
SET @rest_dbf = 'D:\Databases\'   -- Σε ποιά τοποθεσία θα γίνουν restore τα datafiles

DECLARE @rest_log VARCHAR(125)
SET @rest_log = 'L:\Logfiles\';  -- Σε ποια τοποθεσία θα γίνουν restore τα logfiles

WITH CTE ( DatabaseName, Npath )
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''' +@rest_dbf
                                            ELSE ' TO ''' +@rest_log
                                        + REVERSE(LEFT(REVERSE(physical_name),
                                                              1) - 1)) + ''''
                               FROM     sys.master_files sm1
                               WHERE    sm1.database_id = sm2.database_ID
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Npath
               FROM     sys.master_files sm2
--select * from CTE
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5' as Backup_Commands_onSource,
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + CTE.Npath as Restore_Commands_onTarget
FROM sys.databases d
	INNER JOIN CTE ON = cte.databasename
WHERE not in('master','tempdb','model','msdb') --skipped DBs

The result

How can we bulk backup/restore databases in SQL Server

We use the generated results for the process. The first column in the system where we will do the backups and after transferring the files (the .bak created from the backups), we execute the second column of restore statements in the other system.

The example of the statement for backup of the first base.

BACKUP DATABASE bi_test TO DISK = 'X:\backups\bi_test_20200825.bak' WITH COMPRESSION, COPY_ONLY, STATS=5

The example of the statement to restore the first base.

RESTORE DATABASE bi_test FROM DISK = 'X:\backups\bi_test_20200825.bak' WITH RECOVERY, REPLACE, STATS=5  , MOVE 'bi_test' TO 'D:\Databases\bi_test.mdf'  , MOVE 'bi_test_log' TO 'L:\Logfiles\bi_test_log.ldf'  , MOVE 'bi_test2' TO 'D:\Databases\bi_test2.ndf'
Share it

Leave a reply