How can we bulk backup/restore databases in SQL Server

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

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

DECLARE @date CHAR(8)
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 )
          AS ( SELECT DISTINCT
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''' +@rest_dbf
                                            ELSE ' TO ''' +@rest_log
                                          END
                                        + REVERSE(LEFT(REVERSE(physical_name),
                                                       CHARINDEX('\',
                                                              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
SELECT
	'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 d.name = cte.databasename
WHERE d.name not in('master','tempdb','model','msdb') --skipped DBs
GO

The result

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'