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
