SP to generate SQL safe commands for restore using latest backup sets

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