Generate Restore DB Scripts from prev backups v1.0

CREATE procedure GenRestoreDatabaseScript_sp @p_dbname varchar(50), @p_datetime datetime,  @p_usefullbackupend datetime, @p_stats varchar(20) = 10, @p_stopat datetime = null as
SET NOCOUNT ON 
--
-- Name: GenRestoreDatabaseScript_sp
-- By: Chris Kempster, Jan 2002
-- Version: 1.0
--
-- Parameters
-- @p_dbnameDatabase to generate restore scripts for
--@p_datetimeStart time to search for last FULL backup 
--@p_usefullbackupendEnd time to search for last FULL backup
--@p_statsdisplay STATS (default 10%)
--@p_stopatFor Point In Time (PTR) recovery, date which to recover to
--
-- Current Restrictions
--1)  doesnt factor in more than 1 device, only works of FROM DISK= 
--2)  doesnt work with the TAPE option
--3)  doesnt restore specific FILE or FILEGROUPS
--4)  doesnt restore to a specific MARK, only STOPAT
--5)  not tested for replicated or clustered databases

-- How it works
--1)  locates a FULL backup between  @p_datetime  and  @p_usefullbackupend
--2)  locates any differentials and log backups associated with this FULL (by locating the next FULL backup after the one it found and using anything between these for the DB)
--3)  supports multiple appended backups to a single backup device (file)
--4)  will script the FULL backup, then the last DIFFERENTIAL then all LOGs after this differential, finally doing a WITH RECOVERY for the last LOG or full/differential (whater the last backup type was)

-- Examples
--
--Generates script starting from the last FULL backup between the two dates below using a stat recalc of 40 for DB tmp
--exec GenRestoreDatabaseScript_sp 'tmpdb', 'Jan 3, 2002 9:00 AM', 'Jan 3, 2002 10:00 PM', 40
--
--Generates script to restore DB to 'Jan 3, 2002 11:45 AM' using available log files
--exec GenRestoreDatabaseScript_sp 'tmpdb', 'Jan 3, 2002 9:10 AM', 'Jan 3, 2002 9:20 AM', 40, 'Jan 3, 2002 11:45 AM'



DECLARE @v_lastfullbackupINTEGER
DECLARE @v_nextdayfullbackup INTEGER
DECLARE @v_lastdiffbackup INTEGER
DECLARE @v_lastlogbackup INTEGER
DECLARE @v_logfilestopat VARCHAR(100)
DECLARE @v_restoreoptions_all_db VARCHAR(150)
DECLARE @v_errorVARCHAR(150)

-- Locate last backup details for the database

-- the @p_datetime defines the FULL backup file to be restored from (last file is always used for the particular date)
set @v_lastfullbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and  BKS.backup_start_date >=  @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'D')
set @v_nextdayfullbackup = (select min(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >=  @p_usefullbackupend and BKS.type = 'D')
-- ensures restore doesnt use files assoc with next full backup (if any)
if @v_nextdayfullbackup is null or @v_nextdayfullbackup = ''
set @v_nextdayfullbackup =  99999999
set @v_lastdiffbackup = (select max(BKS.backup_set_id)from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >=  @p_datetime and BKS.backup_start_date <= @p_usefullbackupend   and BKS.type = 'I' and BKS.backup_set_id < @v_nextdayfullbackup)
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >=  @p_datetime and BKS.backup_start_date <= @p_usefullbackupend   and BKS.type = 'L' and BKS.backup_set_id < @v_nextdayfullbackup)

-- Set restore options
-- EDIT THIS SINGLE LINE BELOW TO ADD YOUR OWN OPTIONS FOR ALL RESTORES

if @p_stats is not null and isnumeric(@p_stats) = 1 and @p_stats between 1 and 100 
set @v_restoreoptions_all_db = ', STATS = ' + cast(@p_stats as varchar)
else begin
raiserror('Invalid STATS parameter.  Numeric values from 1 to 100 only.', 16,1)
return 1
end

if @p_stopat is not null begin
set @v_logfilestopat =  ', STOPAT = ''' + cast(@p_stopat as varchar) + ''''
-- set max log file to where we need to stop
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_set_id > @v_lastfullbackup and   BKS.backup_set_id  < @v_nextdayfullbackup and BKS.type = 'L'  and @p_stopat between backup_start_date and backup_finish_date)

if @v_lastlogbackup is null or @v_lastlogbackup = '' begin
raiserror('Invalid STOP AT date.  No log files exist or date does not fit in specific log backup range.', 16,1)
return 1
end
end

-- ########################################################## 
-- Generated script of recover commands for a given DB name and date
-- Step 1.  FULL database recovery
-- ########################################################## 

print '-- Recover last full database backup'

select 
'RESTORE DATABASE [' + BKS.database_name + '] ' + 
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) + 
+ @v_restoreoptions_all_db +
case when
(select count(*) 
 from msdb.dbo.backupset BKS2
 where BKS.database_name = BKS2.database_name 
 and BKS2.backup_start_date > BKS.backup_start_date
 andBKS2.type in ('L', 'I')) >= 1 
then
', NORECOVERY'
else
', RECOVERY'
end

from 
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'D' -- full backup

IF @@rowcount <= 0 BEGIN
set @v_error = 'ERROR - Could not find any full backups made during the day specified - ' + CAST(@p_datetime AS VARCHAR)
raiserror(@v_error, 16,1)
return 1
END

-- ########################################################## 
-- Step 2. DIFFERENTIAL database recovery
-- ########################################################## 

print '-- Recover last differential'

select 
'RESTORE DATABASE [' + BKS.database_name + '] ' + 
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) + 
+ @v_restoreoptions_all_db +
case when
(select count(*) 
 from msdb.dbo.backupset BKS2
 where BKS.database_name = BKS2.database_name 
 and BKS2.backup_start_date > BKS.backup_start_date
 andBKS2.type in ('L')) >= 1 
then
', NORECOVERY'
else
', RECOVERY'
end
from 
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastdiffbackup
andBKS.backup_set_id > @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'I' -- differential backup

-- ########################################################## 
-- Step 3. LOG file recovery (not including last log)
-- ########################################################## 

print '-- Recover log files (not including last log)'

-- if there is a last diff backup, then gets logs after this backup, otherwise logs only after the full

IF @v_lastdiffbackup is not null
select 
'RESTORE LOG [' + BKS.database_name + '] ' + 
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) + 
+ @v_restoreoptions_all_db +
', NORECOVERY'
from 
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastdiffbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
ELSE
select 
'RESTORE LOG [' + BKS.database_name + '] ' + 
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) + 
+ @v_restoreoptions_all_db +
', NORECOVERY'
from 
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastfullbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log

-- ########################################################## 
-- Step 4. LOG file recovery (last file)
-- ########################################################## 

print '-- Recover last log file'

select 
'RESTORE LOG [' + BKS.database_name + '] ' + 
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) + 
+ @v_restoreoptions_all_db +
case when @v_logfilestopat is null then
', RECOVERY'
else
@v_logfilestopat +
', RECOVERY'
end
from 
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF--, msdb.dbo.backupfile BF
where
BKS.backup_set_id = @v_lastlogbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
GO