SQL – Create Restore script from DB backup files

This script is primarily used to create the restore script for a logshipped DB on the DR server. Run it on the primary then copy output to the DR server that is set up as LS secondary. Restored DB is create in NORRECOVERY mode so LS chain and jobs can just pick up once it has completed. MOVE statements are created for each datafile including size of each datafile so DBA can move the file location on DR server easily if disk setup is different.

Example output:

RESTORE DATABASE <your_DB> FROM 
 DISK ='\\<your_prod_server>\D$\Backup1\BAK\<your_DB>_1.bak'
,DISK ='\\<your_prod_server>\D$\Backup2\BAK\<your_DB>_2.bak'
WITH NORECOVERY, REPLACE
,MOVE '<your_DB>_Data1' TO 'D:\Data1\<your_DB>_Data1.ndf' --107,520mb
,MOVE '<your_DB>_Data2' TO 'D:\Data2\<your_DB>_Data2.ndf' --97,280mb
,MOVE '<your_DB>_Data3' TO 'D:\Data3\<your_DB>_Data3.ndf' --92,160mb
....

Script:

--Script to create SQL for OnDemand restore job
--Run this on LS primary then copy output to job on DR server
--Created by Liam Gavin 16th June 2013

declare @db_name varchar(100), @destination_drive char(1)
set nocount on

select @db_name = '<DB_name>' --DB to create job for
 , @destination_drive = 'D:\' --Mount point drive letter on DR server

declare @server_name varchar(100)
 , @backupset_id int
 , @backup_str varchar(8000)
 , @datafile_str varchar(8000)
 , @output varchar(8000)
 , @jobname varchar(100)

set @server_name = case when charindex('\', @@SERVERNAME) > 0 then 
 LEFT(@@SERVERNAME, charindex('\', @@SERVERNAME) - 1)
 else @@SERVERNAME
 end
 
if not exists (select 1 from sys.databases where name = @db_name)
begin
 PRINT 'DB is not on this server'
 GOTO FINISH
end

--Check user is running on the primary
if LEFT(@@SERVERNAME, 2) not in ('xx', 'xx')
begin
 PRINT 'Please re-run on the PROD (CH) server'
 GOTO FINISH
end 
 
select top 1 @backupset_id = backup_set_id
FROM msdb..backupset
where type = 'D'
and database_name = @db_name
order by backup_start_date desc

if @backupset_id is null
begin
 PRINT 'There is no current backup of a DB with this name'
 GOTO FINISH
end

set @backup_str = ''
set @jobname = 'On Demand Restore FULL'

SELECT @backup_str = ',DISK =''' + case 
 when b.physical_device_name not like '%' + @server_name + '%' then '\\' + @server_name + '\' 
 else '' end 
 + replace(b.physical_device_name, ':', '$') + '''' 
 + CHAR(13) + CHAR(10) + @backup_str
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b 
ON a.media_set_id = b.media_set_id
WHERE a.backup_set_id = @backupset_id
ORDER BY replace(b.physical_device_name, ':', '$') desc

set @backup_str = ' ' + RIGHT(@backup_str, len(@backup_str) - 1)

set @datafile_str = ''
 
select @datafile_str = ',MOVE ''' + s.name + ''' TO ''' + @destination_drive + RIGHT(physical_name, len(physical_name) - 1) + '''' 
 + ' --' + CONVERT(VARCHAR(20),CONVERT(MONEY,CONVERT(INT, (1024.0 * (size / 128.0)) / 1000.0)),1) + 'mb'
 + CHAR(13) + CHAR(10)
 + @datafile_str
from sys.master_files s
join sys.databases d
on d.database_id = s.database_id
where d.name = @db_name 
order by size 
 
set @datafile_str = replace(@datafile_str, '.00mb', 'mb')
 
select @output = 'IF @@servername = ''' + @@SERVERNAME + '''
BEGIN
 RAISERROR (''ATTEMPTED RESTORE ON PRIMARY SERVER'', 20, 1)
END

exec master.dbo.csp_KillDBUsers ''' + @db_name + '''

--Restore full backup
RESTORE DATABASE ' + @db_name + ' FROM 
' + @backup_str + 'WITH NORECOVERY, REPLACE
' + @datafile_str

--Check if a diff is set up, if diff backup is older than full then it w
if exists (select 1 from msdb..backupset where type = 'I' and database_name = @db_name)
begin
 select top 1 @backupset_id = backup_set_id
 FROM msdb..backupset
 where type = 'I'
 and database_name = @db_name
 order by backup_start_date desc

set @backup_str = ''

SELECT @backup_str = ',DISK =''' + case 
 when b.physical_device_name not like '%' + @server_name + '%' then '\\' + @server_name + '\' 
 else '' end 
 + replace(b.physical_device_name, ':', '$') + '''' 
 + CHAR(13) + CHAR(10) + @backup_str
 FROM msdb..backupset a
 INNER JOIN msdb..backupmediafamily b 
 ON a.media_set_id = b.media_set_id
 WHERE a.backup_set_id = @backupset_id
 ORDER BY replace(b.physical_device_name, ':', '$') desc

set @backup_str = ' ' + RIGHT(@backup_str, len(@backup_str) - 1)

--Diif backup, we don't need MOVEs
 select @output = @output + '
--Restore diff backup 
RESTORE DATABASE ' + @db_name + ' FROM 
' + @backup_str + 'WITH NORECOVERY, REPLACE
' 
 set @jobname = @jobname + ' & DIFF'
end

select @jobname + ' - ' + @db_name as [JOB NAME:]

set @output = @output

SELECT @output as [SCRIPT:]

FINISH:
Posted in Uncategorized | Leave a comment