Restore latest backup of database to same server as new database

30 07 2014

Some, in order to save money, do not purchase a second server for development. If development activities do not require changes to the database system then it is not a horrible idea to just use the same server. Other usage of this script is to have a copy of the production database for a sandbox, reporting, or testing. Here I wrote this script that will run in a maintenance plan to update or create a development database based off of the latest full backup of the source or production database.

/*
Name: Backup_Restore_Maintenance_Plan
Desc: Allows for a single database, the source, to be restored as a new database,
	the target, on the same server
	
Author: Christopher Provolt
Date Created: 29JULY2014

Notes:
Ensure source / target databases and the paths for the data / log files are set correctly 
prior to executing this script

*/
USE master
DECLARE @name			varchar(100)
DECLARE @location		varchar(500)
DECLARE @log_path		varchar(500)
DECLARE @data_path		varchar(500)
DECLARE @log_logical	varchar(100)
DECLARE @data_logical	varchar(100)
DECLARE @target			varchar(100)
DECLARE @source			varchar(100)
DECLARE @datapath		varchar(300)
DECLARE @logpath		varchar(300)

/* Define source and target databases */
set @source = 'AdventureWorks2012'
set @target = 'AW2'

/* define paths and file names for data and log files for target db */
set @datapath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Data\' + @target +'.mdf'
set @logpath = 'F:\MSSQL12.MSSQLSERVER\MSSQL\Logs\' + @target + '_log.ldf'

/* determine the location of the latest backup for the source DB */
SELECT  @name = x.database_name,
        @location = x.physical_device_name
        
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1 AND x.database_name = @source

 /* get logical names of the source database files for recovery */
select @data_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'ROWS'
select @log_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'LOG'

/*Restore the database to its new home */
RESTORE DATABASE @target FROM DISK=@location
WITH REPLACE,
MOVE @data_logical TO @datapath, 
MOVE @log_logical TO @logpath