Quick Backup/Restore Script

By bradyupton in Backup/Restore, Upgrades/Migrations 0 Comment

Throughout the years I’ve probably performed hundreds if not thousands of migrations or “refreshes” to lower environments. It’s pretty simple to run a quick maintenance plan or SQL job to backup all databases, but the more complex piece is creating a restore script to point to that backup location and the backup name. It can be done, but it takes a little longer, IMO. A lot of backup file names contain numeric characters such as date or time of backup.

John Morehouse created a dynamic script that simplifies everything and it can be easily modified to fit particular needs. I’ve had this script bookmarked for years and use it at least once a week. Kudos to John for sharing this with the SQL community. I’ll link the script below, but here’s a quick snippet.

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

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
          AS ( SELECT DISTINCT
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''D:\SQLData\'
                                            ELSE ' TO ''E:\SQLTLogs\'
                                          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 MoveCmd
               FROM     sys.master_files sm2
  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + movecmdCTE.MoveCmd
FROM sys.databases d
	INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

To change the backup/restore path you can modify this section of code to fit your needs:

SET @path = '\\UNCPath\Folder\'

Another modification I perform initially is changing the where clause, for example, if I don’t want system databases I can change:

WHERE d.name LIKE '%DatabaseName%'

to

WHERE d.database_id > 4

A lot of times I don’t need to move the data and log files to a new location because I’m overwriting other databases so I just comment all of the MoveCmdCTE CTE code out:

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

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

--;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
--          AS ( SELECT DISTINCT
--                        DB_NAME(database_id) ,
--                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
--                                        + CASE Type
--                                            WHEN 0 THEN ' TO ''D:\SQLData\'
--                                            ELSE ' TO ''E:\SQLTLogs\'
--                                          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 MoveCmd
--               FROM     sys.master_files sm2
--  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' --+ movecmdCTE.MoveCmd
FROM sys.databases d
	--INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

There’s a lot of option here and this script is quick and easy.

Link to script: https://gist.github.com/airtank20/a826c6f37439482edd5070e8aaeb1ee1

Related Articles.

Leave a Comment.