I had a need to restore backups to different servers and use the most current backup sets, Full, Differential and Transaction log. The existing policies inside SQL safe did not really give me what I wanted so I created a SP to give me the latest backup sets to restore a DB from one server to another generating the TSQL commands for SQL Safe that could be used in a SQL Agent Job. This allowed me to restore a DB to any server with the current backups and since it was in a SQL job I could do other things such as change DB compatibility and grant permissions to the DB that were different, etc. It really helped to automate a server migration by allowing restores to be there every day for development work in the new environment.
If you find it useful then enjoy it and see how you can leverage it. The SP below is for Full and Differential but the code to do Transaction logs is in there but commented out. It has to be run on the server where the SQL Safe DB is located; I used a Linked Server with R/O Login on the SQL servers where I needed to use it. You could use Powershell with it as well just depends on what your needs are.
CREATE PROCEDURE [dbo].[usp_Get_FULL_DIFF_TSQL_Restore_Commands_SingleOutput_v3] /********************************************************************************************************************************** Procedure Name : dbo.usp_Get_FULL_DIFF_TSQL_Restore_Commands Description : Sample Call : Exec dbo.usp_Get_FULL_DIFF_TSQL_Restore_Commands @DBName=’MydB’,@DestServer=’DBTST01′,@ServInstance=’DB01′ Comments : @DBName is the Database name to be restored. @DestServer is the Destionation Server it will be restored on. @ServInstance is the Source Server Version : 1.0 History : Date: User: Comments: 06/17/2014 B. ODwyer Created
**********************************************************************************************************************************/ ( @DBName VARCHAR(128) , @DestServer VARCHAR(128) , @ServInstance VARCHAR(128) , @FinalOutput VARCHAR(4000) OUTPUT )
AS
BEGIN
–DECLARE — @DBName VARCHAR(128) — , @DestServer VARCHAR(128) — , @ServInstance VARCHAR(128)
–SET @DBName = ‘MyDb’ –SET @DestServer = ‘DBTST01′ –SET @ServInstance = ‘DB01′
;WITH FullCTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY A.start_datetime ASC ) AS Rownum , CASE E.name WHEN ” THEN F.name ELSE F.name + ‘\’ + E.name END AS ServInstance , D.name AS DBName –, E.name AS Instance_Name , F.name AS Server_Name , H.[filename] –, A.[action_type] — 0 backup, 1 restore, 2 verify –, A.[instance_id] –, A.[database_id] –, A.[backup_set_id] –, A.[backup_type] , CASE A.[backup_type] WHEN 0 THEN ‘Full’ WHEN 1 THEN ‘Diff’ WHEN 2 THEN ‘Log’ END AS Btype –, A.[action_guid] –, A.[queued_datetime] , DATEADD(HH,A.utc_offset,A.[start_datetime]) AS start_datetime , DATEADD(HH,A.utc_offset,A.[end_datetime]) AS end_datetime , CAST(A.[uncompressed_kb]/1024.0 AS DECIMAL(10,3)) AS uncompressed_mb , A.[compressed_kb]/1024.0 AS compressed_mb –, A.[action_status] — 1 running/executing, 2 completed/success, 3 terminated/failed, 4 canceled –, A.[username] –, A.[utc_offset] , A.[is_deleted] , DATEADD(HH,A.utc_offset,A.[deleted_date]) AS deleted_date , B.result_text –, A.[retry_settings] –, A.[policy_uid] FROM [SQLsafeRepository].[dbo].[actions] AS A WITH (NOLOCK,NOWAIT) INNER JOIN [SQLsafeRepository].[dbo].[actions_results] AS B WITH (NOLOCK,NOWAIT) ON B.[action_id] = A.[action_id] INNER JOIN [SQLsafeRepository].[dbo].[databases] AS D WITH (NOLOCK,NOWAIT) ON D.database_id = A.database_id INNER JOIN [SQLsafeRepository].[dbo].[instances] AS E WITH (NOLOCK,NOWAIT) ON E.instance_id = A.instance_id INNER JOIN [SQLsafeRepository].[dbo].[servers] AS F WITH (NOLOCK,NOWAIT) ON F.server_id = E.server_id INNER JOIN [SQLsafeRepository].[dbo].[actions_backuparchives_relate] AS G WITH (NOLOCK,NOWAIT) ON G.action_id = A.action_id –AND sa.[uncompressed_kb] > 0 INNER JOIN [SQLsafeRepository].[dbo].[backup_archives] AS H WITH (NOLOCK,NOWAIT) ON H.backup_archive_id = G.backup_archive_id
WHERE DATEADD(hh,A.utc_offset,A.start_datetime) >= DATEADD(WW,-1,CURRENT_TIMESTAMP) AND D.name = @DBName AND F.name = @ServInstance AND A.action_type = 0 AND A.[backup_type] = 0 AND A.[action_status] = 2 AND A.[deleted_date] IS NULL AND A.uncompressed_kb > 0 AND DATEADD(hh,A.utc_offset,A.end_datetime) < CURRENT_TIMESTAMP
)
–SELECT * FROM FULLCTE
, FullResults AS ( SELECT Rownum , ServInstance , Server_Name , DBName , [filename] , Btype , start_datetime , end_datetime , uncompressed_mb , compressed_mb
FROM FULLCTE WHERE DBName = @DBName AND Server_Name = @ServInstance AND Rownum = (SELECT MAX(Rownum) FROM FULLCTE) )
–SELECT * FROM FullResults
, DIFFCTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY A.start_datetime ASC ) AS Rownum , CASE E.name WHEN ” THEN F.name ELSE F.name + ‘\’ + E.name END AS ServInstance , D.name AS DBName –, E.name AS Instance_Name , F.name AS Server_Name , H.[filename] –, A.[action_type] — 0 backup, 1 restore, 2 verify –, A.[instance_id] –, A.[database_id] –, A.[backup_set_id] –, A.[backup_type] , CASE A.[backup_type] WHEN 0 THEN ‘Full’ WHEN 1 THEN ‘Diff’ WHEN 2 THEN ‘Log’ END AS Btype –, A.[action_guid] –, A.[queued_datetime] , DATEADD(HH,A.utc_offset,A.[start_datetime]) AS start_datetime , DATEADD(HH,A.utc_offset,A.[end_datetime]) AS end_datetime , CAST(A.[uncompressed_kb]/1024.0 AS DECIMAL(10,3)) AS uncompressed_mb , A.[compressed_kb]/1024.0 AS compressed_mb –, A.[action_status] — 1 running/executing, 2 completed/success, 3 terminated/failed, 4 canceled –, A.[username] –, A.[utc_offset] , A.[is_deleted] , DATEADD(HH,A.utc_offset,A.[deleted_date]) AS deleted_date , B.result_text –, A.[retry_settings] –, A.[policy_uid] FROM [SQLsafeRepository].[dbo].[actions] AS A WITH (NOLOCK,NOWAIT) INNER JOIN [SQLsafeRepository].[dbo].[actions_results] AS B WITH (NOLOCK,NOWAIT) ON B.[action_id] = A.[action_id] INNER JOIN [SQLsafeRepository].[dbo].[databases] AS D WITH (NOLOCK,NOWAIT) ON D.database_id = A.database_id INNER JOIN [SQLsafeRepository].[dbo].[instances] AS E WITH (NOLOCK,NOWAIT) ON E.instance_id = A.instance_id INNER JOIN [SQLsafeRepository].[dbo].[servers] AS F WITH (NOLOCK,NOWAIT) ON F.server_id = E.server_id INNER JOIN [SQLsafeRepository].[dbo].[actions_backuparchives_relate] AS G WITH (NOLOCK,NOWAIT) ON G.action_id = A.action_id –AND sa.[uncompressed_kb] > 0 INNER JOIN [SQLsafeRepository].[dbo].[backup_archives] AS H WITH (NOLOCK,NOWAIT) ON H.backup_archive_id = G.backup_archive_id INNER JOIN FullResults AS FC ON FC.Server_Name = F.name
WHERE DATEADD(hh,A.utc_offset,A.start_datetime) >= FC.end_datetime AND D.name = @DBName AND F.name = @ServInstance AND A.action_type = 0 AND A.[backup_type] = 1 AND A.[action_status] = 2 AND A.[deleted_date] IS NULL AND A.uncompressed_kb > 0 AND DATEADD(hh,A.utc_offset,A.end_datetime) < CURRENT_TIMESTAMP –AND DATEADD(hh,A.utc_offset,A.start_datetime) > FC.start_datetime
–SELECT * FROM DIFFCTE
, DiffResults AS ( SELECT Rownum , ServInstance , Server_Name , DBName , [filename] , Btype , start_datetime , end_datetime , uncompressed_mb , compressed_mb
FROM DIFFCTE WHERE DBName = @DBName AND Server_Name = @ServInstance AND Rownum = (SELECT MAX(Rownum) FROM DIFFCTE) )
–SELECT * FROM DiffResults
–, TRANSCTE
–AS –(
–SELECT — ROW_NUMBER() OVER(ORDER BY A.start_datetime ASC ) AS Rownum — , CASE E.name — WHEN ” THEN F.name — ELSE F.name + ‘\’ + E.name — END AS ServInstance — , D.name AS DBName — –, E.name AS Instance_Name — –, F.name AS Server_Name — , H.[filename] — –, A.[action_type] — –, A.[instance_id] — –, A.[database_id] — –, A.[backup_set_id] — –, A.[backup_type] — , CASE A.[backup_type] — WHEN 0 THEN ‘Full’ — WHEN 1 THEN ‘Diff’ — WHEN 2 THEN ‘Log’ — END AS Btype — –, A.[action_guid] — –, A.[queued_datetime] — , DATEADD(HH,-5,A.[start_datetime]) AS start_datetime — , DATEADD(HH,-5,A.[end_datetime]) AS end_datetime — , CAST(A.[uncompressed_kb]/1024.0 AS DECIMAL(10,3)) AS uncompressed_mb — , A.[compressed_kb]/1024.0 AS compressed_mb — –, A.[action_status] — –, A.[username] — –, A.[utc_offset] — , A.[is_deleted] — , DATEADD(HH,-5,A.[deleted_date]) AS deleted_date — , B.result_text — –, A.[retry_settings] — –, A.[policy_uid] –FROM — [SQLsafeRepository].[dbo].[actions] AS A WITH (NOLOCK,NOWAIT) — INNER JOIN [SQLsafeRepository].[dbo].[actions_results] AS B WITH (NOLOCK,NOWAIT) — ON — B.[action_id] = A.[action_id] — INNER JOIN [SQLsafeRepository].[dbo].[databases] AS D WITH (NOLOCK,NOWAIT) — ON — D.database_id = A.database_id — INNER JOIN [SQLsafeRepository].[dbo].[instances] AS E WITH (NOLOCK,NOWAIT) — ON — E.instance_id = A.instance_id — INNER JOIN [SQLsafeRepository].[dbo].[servers] AS F WITH (NOLOCK,NOWAIT) — ON — F.server_id = E.server_id — INNER JOIN [SQLsafeRepository].[dbo].[actions_backuparchives_relate] AS G WITH (NOLOCK,NOWAIT) — ON — G.action_id = A.action_id — –AND sa.[uncompressed_kb] > 0 — INNER JOIN [SQLsafeRepository].[dbo].[backup_archives] AS H WITH (NOLOCK,NOWAIT) — ON — H.backup_archive_id = G.backup_archive_id — INNER JOIN DiffResults AS K — ON — K.Server_Name = F.name
–WHERE — DATEADD(hh,-5,A.start_datetime) >= K.end_datetime — AND D.name = @DBName — AND F.name = @ServInstance — AND A.action_type = 0 — AND A.[backup_type] = 2 — AND A.[deleted_date] IS NULL — AND DATEADD(hh,-5,A.end_datetime) < CURRENT_TIMESTAMP –)
—-SELECT * FROM TRANSCTE
–, TRNCTE1
–AS –( –SELECT Rownum, [filename] FROM TRANSCTE WHERE Rownum <(SELECT MAX(Rownum) FROM TRANSCTE) –)
–, TRNCTE2
–AS –( –SELECT Rownum, [filename] FROM TRANSCTE WHERE Rownum = (SELECT MAX(Rownum) FROM TRANSCTE) –)
, GETFinal1 AS ( SELECT CASE WHEN NOT EXISTS (SELECT Rownum FROM DiffResults) THEN ‘DECLARE @ResultCode INT
EXEC @ResultCode = [master].[dbo].[xp_ss_restore] @database = N”’[email protected]+”’, @filename = N”’+[filename]+”’, @server = N”’[email protected]+”’, @disconnectusers = N”1”, @windowsusername = N”mydom\mynetid”, @encryptedwindowspassword = N”myencryptedpassword”
IF(@ResultCode != 0) RAISERROR(”One or more operations failed to complete.”, 16, 1); ‘ ELSE ‘DECLARE @ResultCode INT
EXEC @ResultCode = [master].[dbo].[xp_ss_restore] @database = N”’[email protected]+”’, @filename = N”’+[filename]+”’, @server = N”’[email protected]+”’, @recoverymode = N”NoRecovery”, @disconnectusers = N”1”, @windowsusername = N”mydom\mynetid”, @encryptedwindowspassword = N”myencryptedpassword”
IF(@ResultCode != 0) RAISERROR(”One or more operations failed to complete.”, 16, 1); ‘ END AS RESULT FROM FullResults
) –UNION ALL , GETFinal2 AS ( SELECT ‘ EXEC @ResultCode = [master].[dbo].[xp_ss_restore] @database = N”’ + @DBName + ”’, @filename = N”’ + [filename] + ”’, @server = N”’ + @DestServer + ”’, @windowsusername = N”mydom\mynetid”, @encryptedwindowspassword = N”myencryptedpassword”
IF(@ResultCode != 0) RAISERROR(”One or more operations failed to complete.”, 16, 1); ‘ AS RESULT FROM DiffResults
SELECT @FinalOutput =( SELECT RESULT FROM GETFinal1) + ISNULL((SELECT RESULT FROM GETFinal2),”) –SELECT @FinalOutput –UNION ALL
–SELECT –‘ –EXEC @ResultCode = [master].[dbo].[xp_ss_restore] — @database = N”’ + @DBName + ”’, — @filename = N”’ + [filename] + ”’, — @server = N”’ + @DestServer + ”’, — @recoverymode = N”NoRecovery”, — @windowsusername = N”mydom\mynetid”, — @encryptedwindowspassword = N”myencryptedpassword”
–IF(@ResultCode != 0) — RAISERROR(”One or more operations failed to complete.”, 16, 1); –‘ AS RESULT –FROM — TRNCTE1
–UNION ALL
–SELECT –‘ –EXEC @ResultCode = [master].[dbo].[xp_ss_restore] — @database = N”’ + @DBName + ”’, — @filename = N”’ + [filename] + ”’, — @server = N”’ + @DestServer + ”’, — @windowsusername = N”mydom\mynetid”, — @encryptedwindowspassword = N”myencryptedpassword”
–IF(@ResultCode != 0) — RAISERROR(”One or more operations failed to complete.”, 16, 1); –‘ AS RESULT –FROM — TRNCTE2
; END RETURN
GO
Hi Brian, I know this post is old, but the script has lots of syntax issues like dashs and others and I can spend a few hours fixing it, but can you attach the code in a text file are email it to me at [email protected] Thanks.