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_dbname		Database to generate restore scripts for
--	@p_datetime		Start time to search for last FULL backup 
--	@p_usefullbackupend	End time to search for last FULL backup
--	@p_stats		display STATS (default 10%)
--	@p_stopat		For 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_lastfullbackup		INTEGER
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_error			VARCHAR(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
				 and	BKS2.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
		and	BKS.media_set_id = BMF.media_set_id
		and	BKS.database_name = @p_dbname
		and	BKS.backup_start_date >= @p_datetime
		and	BKS.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
					 and	BKS2.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
			and	BKS.backup_set_id > @v_lastfullbackup
			and	BKS.media_set_id = BMF.media_set_id
			and	BKS.database_name = @p_dbname
			and	BKS.backup_start_date >= @p_datetime
			and	BKS.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
				and	BKS.backup_set_id > @v_lastdiffbackup
				and 	BKS.backup_set_id < @v_nextdayfullbackup
				and	BKS.media_set_id = BMF.media_set_id
				and	BKS.database_name = @p_dbname
				and	BKS.backup_start_date >= @p_datetime
				and	BKS.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
				and	BKS.backup_set_id > @v_lastfullbackup
				and 	BKS.backup_set_id < @v_nextdayfullbackup
				and	BKS.media_set_id = BMF.media_set_id
				and	BKS.database_name = @p_dbname
				and	BKS.backup_start_date >= @p_datetime
				and	BKS.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
			and	BKS.media_set_id = BMF.media_set_id
			and	BKS.database_name = @p_dbname
			and	BKS.backup_start_date >= @p_datetime
			and	BKS.type = 'L' -- log
GO