… 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';
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)