Update Reporting from Live (part 2 of 2)

… I created a SQL Job on the Reporting server called “Restore from Live”.

Step-1 “Kill any connections”

Before a database can be restored it needs to be unused. Removing connections in this way is more reliable then changing the database to “Single-user-mode”.

DECLARE @kill VARCHAR(8000) = '';

SELECT  @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';'
	FROM [master].[dbo].[sysprocesses]
	WHERE dbid = DB_ID('LiveDatabaseName')
	AND spid > 50;

EXEC (@kill);

Step-2 “Full Restore”

This step restores a Full backup. Changing the database name and file locations as required.

When complete the database will be left in a “Restoring” state. *It can be brought online either by completing the next step or by manual recovery EG: “Restore Database [LiveDatabaseName] with Recovery;”.

RESTORE DATABASE [LiveDatabaseName] 

FROM DISK = N'\\LiveServerName\SQLBackups\LiveDatabaseName\LiveDatabaseName_FULL.bak' 

WITH NORECOVERY, REPLACE;

Step-3 “Diff Restore”

This step restores a Diff backup similar to the last step, however it brings the database back online after completion. If this step ever fails see * above.

RESTORE DATABASE [LiveDatabaseName] 

FROM DISK = N'\\LiveServerName\SQLBackups\LiveDatabaseName\LiveDatabaseName_DIFF.bak' 

WITH RECOVERY;

Step-4 “Switch to Simple Recovery Mode”

This step changes the database to Simple recovery mode where the log-files are re-used and do not require management (EG: regular log backups). This is appropriate for report servers where the data is already backed up from live “for recovery” (IE: outside of the backups detailed in these 2 posts).

ALTER DATABASE [LiveDatabaseName] set recovery SIMPLE;

Step-5 “Remove Orphans”

This deprecated command changes the password for the “mssql” login to match the password from the “mssql” user. Login passwords are not captured by backups.

sp_change_users_login 'auto_fix', 'mssql';

Footnote

I wrote these 2 jobs using minimal variables and dynamic SQL, and using a generous number of jobs and job-steps, in the hope that this will be robust and easy to manage. (And because I really like simplifying such things)