MS SQL: Automatically restore latest backup
A common need is to restore the latest production backup to a test system or user acceptance test system on a regular basis. Depending on your system (database) size this can be time consuming. You would prefer to have this done during the night right after the backups run. If you don’t have any third party solution for backups where this feature is built in it can be a bit tricky. The reason why is that the automated backups in a Microsoft SQL Server maintenance plan have somewhat unpredictable names.
Solution to this problem is a simple T-SQL script that you can put in a maintenance plan, to run every night or just on schedule as you please. The script grabs the latest backup an makes the restore.
The script can be downloaded from Github – T-SQL Automatic restore of latest backup
First we declare and set some variables. @FromDatabaseName and @ToDatabaseName is pretty self explanatory, name of the source and destination databases.
[sql]
— Declare variables to be set
DECLARE
@FromDatabaseName nvarchar(128),
@ToDatabaseName nvarchar(128);
SET @FromDatabaseName = ‘Example.Production.DB’;
SET @ToDatabaseName = ‘Example.Test.DB’;
— Get latest database backup for the FromDatabase
DECLARE @BackupFile nvarchar(260);
[/sql]
Then we need the actual backup filename that we can grab from the msdb database.
[sql]
SELECT @BackupFile=[physical_device_name] FROM [msdb].[dbo].[backupmediafamily]
WHERE [media_set_id] =(SELECT TOP 1 [media_set_id] FROM msdb.dbo.backupset
WHERE database_name=@FromDatabaseName AND type=’D’ ORDER BY backup_start_date DESC);
[/sql]
Then we need to get the destination database actual filenames on disc. Both database and log file is needed. Since we are restore a backup for a different database then the destination we need this information to restore to the correct files and not overwrite the source database instead. This information can be found in the sys database.
[sql]
— Get ToDatabase filenames
DECLARE
@ToDatabaseFile nvarchar(260),
@ToDatabaseLog nvarchar(260);
SELECT @ToDatabaseFile = f.physical_name FROM sys.master_files f RIGHT JOIN sys.databases d ON f.database_id = d.database_id
WHERE d.name = @ToDatabaseName AND f.type_desc = ‘ROWS’;
SELECT @ToDatabaseLog = f.physical_name FROM sys.master_files f RIGHT JOIN sys.databases d ON f.database_id = d.database_id
WHERE d.name = @ToDatabaseName AND f.type_desc = ‘LOG’;
[/sql]
Then we do the actual restore. Before we begin that operation we put the database in SINGLE_USER mode to make sure that we don’t have any locks or open connections to the destination database. To be able to do ALTER on a database specified in a variable we encapsulate that command in the EXEC function.
[sql]
— Restore the database
EXEC(‘ALTER DATABASE [‘ + @ToDatabaseName + ‘] SET SINGLE_USER WITH ROLLBACK IMMEDIATE’);
EXEC(‘RESTORE DATABASE [‘ + @ToDatabaseName + ‘] FROM DISK = ”’ + @BackupFile + ”’ WITH FILE = 1,
MOVE ”DatabaseLogicName” TO ”’ + @ToDatabaseFile + ”’,
MOVE ”DatabaseLogicName_log” TO ”’ + @ToDatabaseLog + ”’, NOUNLOAD, REPLACE, STATS = 5′);
EXEC(‘ALTER DATABASE [‘ + @ToDatabaseName + ‘] SET MULTI_USER’);
[/sql]
In the restore command we alter the destination filenames to match the destination database. In the two MOVE statements we need to the logical names of each file as they look in the source database. This can be looked up via the database properties under the files tab. Then we put the database back into MULTI_USER mode again.
Hackviking aka Kristofer Källsbo 