Generate restore script automatically by backup history table

I wrote a store procedure which could generate restore script automatically by backup history table. it can help you select a best restore solution base on the backup history table.

For instance,  if you backup the database as the sequence below

ID	backup_start_date	backup_type
1	2012-04-30 18:28:43.000	Full
2	2012-04-30 18:29:22.000	Log
3	2012-04-30 18:29:52.000	Full
4	2012-04-30 18:30:28.000	Full
5	2012-04-30 18:33:11.000	Diff
6	2012-04-30 18:37:18.000	Diff
7	2012-04-30 18:39:26.000	Log
8	2012-04-30 18:40:58.000	Full
9	2012-04-30 18:43:21.000	Full
10	2012-04-30 18:43:56.000	Full
11	2012-04-30 18:44:10.000	Full
12	2012-04-30 18:48:50.000	Log
13	2012-04-30 18:50:53.000	Log

let's say you want to generate restore script which can restore the database to  '2012-04-30 18:34:00',
it will automatically generate restore script by using the backup file below

ID	backup_start_date	backup_type
4	2012-04-30 18:30:28.000	Full
5	2012-04-30 18:33:11.000	Diff
7	2012-04-30 18:39:26.000	Log

if you want to restore to '2012-04-30 18:44:00', it will generate script by backup file

10	2012-04-30 18:43:56.000	Full
12	2012-04-30 18:48:50.000	Log

The store procedure can also check if the backup file is existed.
you can also generate restore script for any point, let me know your feedback . thanks

---------------------------------------------------------------------------------------------------------------
USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RestoreDBbyHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RestoreDBbyHistory]
GO
USE [msdb]
GO
CREATE PROCEDURE [dbo].[sp_RestoreDBbyHistory] (
 @SourceDB  varchar(200),
 @DestinationDB varchar(200)   =NULL,
 @RestoreToTime datetime    =NULL,   --'yyyy-mm-dd hh:mi:ss', ie. '2012-04-27 22:19:20'
 @RecoveryMode varchar(10)    ='Recovery',  --'Recovery' or 'Norecovery'
 @ListMode   varchar(10)   ='OnlyValid'   --'All' or 'OnlyValid'
 )
AS
/*********************************************************************************************
Generate database restore script by backup history, it can
1. give out the restore script and sequence
 @ListMode='All' :   lists all possible restore scripts before @RestoreToTime
 @ListMode='OnlyValid' :  gives out a best valid restore way with mininum script.
2. check if the backup file existed

blog: http://jamessql.blogspot.com/
Example:
1. Generate restore script for database RS
EXEC sp_RestoreDBbyHistory @SourceDB='RS'
2. Generate all possible restore script for database RS, restore it as name 'RS_restored' to '2012-04-30 14:13:30.000' and with norecovery
[sp_RestoreDBbyHistory]
@SourceDB='RS'
,@DestinationDB='RS_restored'
,@RestoreToTime ='2012-04-30 14:13:30.000'
,@RecoveryMode ='Norecovery'
,@ListMode='All'
Pleaes test it by yourself first.
*********************************************************************************************/
BEGIN

 DECLARE @Exists int
 DECLARE @File_Exists varchar(30)
 DECLARE @backup_start_date datetime
 DECLARE @backup_finish_date datetime
 DECLARE @first_lsn numeric(25,0)
 DECLARE @last_lsn numeric(25,0)
 DECLARE @position int
 DECLARE @backup_type varchar(20)
 DECLARE @backup_size numeric(20,0)
 DECLARE @physical_device_name varchar(500)
 DECLARE @backupset_name varchar(500)
 DECLARE @differential_base_lsn numeric(25,0)
 DECLARE @database_backup_lsn numeric(25,0)


 DECLARE @restore_command varchar(max)
 DECLARE @lastfile bit
 DECLARE @stopat varchar(50)
 DECLARE @MOVETO VARCHAR(MAX)
 DECLARE @MOVETO_temp VARCHAR(MAX)
 DECLARE @first_backup_set_id int

 IF (@SourceDB is NULL)
 BEGIN
  PRINT 'Please input the @SourceDB name!'
  RETURN
 END

 IF (@DestinationDB is NULL)
  SET @DestinationDB=@SourceDB

 if (@RestoreToTime is NULL)
 BEGIN
  SET @stopat=''
  SET @RestoreToTime=GETDATE()
 END
 else
  SET @stopat=', STOPAT = '''+CONVERT(varchar(50), @RestoreToTime)+''''

 IF (@RecoveryMode NOT IN ('Recovery', 'Norecovery'))
 BEGIN
  PRINT 'Please set parameter @RecoveryMode with value ''Recovery'' or ''Norecovery'''
  RETURN
 END

 IF (@ListMode NOT IN ('All', 'OnlyValid'))
 BEGIN
  PRINT 'Please set parameter @@Mode with value ''All'' or ''OnlyValid'''
  RETURN
 END

 SET @lastfile=0
 SET @MOVETO=''

 --FIND the last valid full backup
 SELECT TOP 1 @first_backup_set_id=backup_set_id
 FROM MSDB..backupset bs
 WHERE bs.type='D'
 AND bs.backup_start_date<=@RestoreToTime
 AND bs.database_name=@SourceDB
 ORDER BY bs.backup_start_date DESC

 IF (@first_backup_set_id IS NULL)
 BEGIN
  SELECT @first_backup_set_id=MIN(bs.backup_set_id)
  FROM MSDB..backupset bs
  WHERE bs.database_name=@SourceDB AND bs.backup_start_date<=@RestoreToTime
 
  IF (@first_backup_set_id IS NULL)
  BEGIN
   PRINT 'There is no any valid backup!!!'
   return
  END
  ELSE
   PRINT 'There is no valid full backup!!!'
  
 END
 ELSE
 BEGIN
  SELECT @MOVETO=@MOVETO+'MOVE N''' +bf.logical_name+''' TO N'''+
  REVERSE(RIGHT(REVERSE(bf.physical_name),(LEN(bf.physical_name)-
  CHARINDEX('\', REVERSE(bf.physical_name),1))+1))+
  CASE
   WHEN bf.file_number = 1 THEN @DestinationDB+'_data.mdf'
   WHEN (bf.file_number <> 1) and (bf.file_type = 'L') THEN @DestinationDB+'_log'+CONVERT(varchar(3),bf.file_number)+'.ldf'
   WHEN (bf.file_number <> 1) and (bf.file_type = 'D') THEN @DestinationDB+'_data'+CONVERT(varchar(3),bf.file_number)+'.ndf'
   ELSE @DestinationDB+'_'+CONVERT(varchar(3),bf.file_number)+'.ndf'
  END +
  ''','
  FROM msdb..backupfile bf LEFT JOIN msdb..backupset bs on bf.backup_set_id=bs.backup_set_id
  where bf.backup_set_id=@first_backup_set_id
  SET @MOVETO_temp=@MOVETO
 END

 CREATE TABLE #RestoreCommand(
  ID int NOT NULL IDENTITY (1, 1),
  backup_start_date datetime,
  backup_finish_date datetime,
  database_backup_lsn numeric(25,0),
  differential_base_lsn numeric(25,0),
  first_lsn numeric(25,0),
  last_lsn numeric(25,0),
  postion int,
  backup_type varchar(20),
  backup_size numeric(20,0),
  physical_device_name varchar(500) ,
  backupset_name varchar(500),
  restore_command varchar(max),
  fileExist varchar(20)
 )
 IF (@ListMode='All')
  SET @first_backup_set_id=1
 DECLARE backup_cursor CURSOR FOR
 SELECT
    bs.backup_start_date, 
    bs.backup_finish_date,
    bs.database_backup_lsn,
    bs.differential_base_lsn,
    bs.first_lsn,
    bs.last_lsn,
    position,
    CASE bs.type 
     WHEN 'D' THEN 'Full' 
     WHEN 'L' THEN 'Log' 
     WHEN 'I' THEN 'Diff'
    END AS backup_type, 
    bs.backup_size,
    bmf.physical_device_name,  
    bs.name AS backupset_name
 FROM   msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
 WHERE bs.database_name=@SourceDB
 AND bs.backup_set_id>=@first_backup_set_id
 ORDER BY 
    bs.backup_start_date

 OPEN backup_cursor;
 FETCH NEXT FROM backup_cursor
 INTO @backup_start_date,@backup_finish_date,@database_backup_lsn,@differential_base_lsn,@first_lsn, @last_lsn,@position, @backup_type,@backup_size,@physical_device_name, @backupset_name

 WHILE ((@@FETCH_STATUS = 0) and (@lastfile <>1))
 BEGIN
  --check if file exist
  EXEC Master.dbo.xp_fileexist @physical_device_name, @Exists OUT
  IF  (@Exists= 1)
   SET @File_Exists= 'File Found'
  ELSE
   SET @File_Exists= 'File Not Found'
  
  IF (@backup_start_date<=@RestoreToTime)
  BEGIN
   --if this diff backup, then remove all log backup before it.
   IF ((@backup_type='Diff') and (@ListMode='OnlyValid'))
    DELETE FROM #RestoreCommand
    WHERE backup_type IN ('Log', 'Diff')
  
   IF @backup_type='Full'
   BEGIN
    SET @MOVETO_temp=@MOVETO
    IF (@ListMode='OnlyValid')
     DELETE FROM #RestoreCommand
   END
   ELSE
    SET @MOVETO_temp=''
   SET @restore_command=
    CASE  
     WHEN @backup_type in ('Full','Diff') THEN 'RESTORE DATABASE [' + @DestinationDB + '] FROM  DISK = N'''+ @physical_device_name +''' WITH  FILE = '+convert(varchar(3),@position)+','+@MOVETO_temp+'NORECOVERY, NOUNLOAD,  STATS = 10'
     WHEN @backup_type = 'Log' THEN 'RESTORE LOG [' + @DestinationDB + '] FROM  DISK = N'''+ @physical_device_name +''' WITH  FILE = '+convert(varchar(3),@position)+','+@MOVETO_temp+'NORECOVERY, NOUNLOAD, STATS = 10'
    END
  
   INSERT INTO #RestoreCommand (
    backup_start_date,
    backup_finish_date,
    database_backup_lsn ,
    differential_base_lsn,
    first_lsn,
    last_lsn,
    postion,
    backup_type,
    backup_size,
    physical_device_name ,
    backupset_name,
    restore_command,
    fileExist)
   VALUES
   (
    @backup_start_date,
    @backup_finish_date,
    @database_backup_lsn ,
    @differential_base_lsn,
    @first_lsn,
    @last_lsn,
    @position,
    @backup_type,
    @backup_size,
    @physical_device_name,
    @backupset_name,
    @restore_command,
    @File_Exists
   )
  END
  ELSE
   IF (@backup_type='Log')
   BEGIN
    SET @lastfile=1
    SET @restore_command='RESTORE LOG [' + @DestinationDB + '] FROM  DISK = N'''+ @physical_device_name +''' WITH  FILE = '+convert(varchar(3),@position)+',NORECOVERY, NOUNLOAD, STATS = 10'
    SET @restore_command=REPLACE(@restore_command, 'NORECOVERY','RECOVERY')+@stopat
    INSERT INTO #RestoreCommand (
     backup_start_date,
     backup_finish_date,
     database_backup_lsn ,
     differential_base_lsn,
     first_lsn,
     last_lsn,
     postion,
     backup_type,
     backup_size,
     physical_device_name ,
     backupset_name,
     restore_command,
     fileExist)
    VALUES
    (
     @backup_start_date,
     @backup_finish_date,
     @database_backup_lsn ,
     @differential_base_lsn,
     @first_lsn,
     @last_lsn,
     @position,
     @backup_type,
     @backup_size,
     @physical_device_name,
     @backupset_name,
     @restore_command,
     @File_Exists
    )
   END
  FETCH NEXT FROM backup_cursor
  INTO @backup_start_date,@backup_finish_date,@database_backup_lsn,@differential_base_lsn,@first_lsn, @last_lsn,@position, @backup_type,@backup_size,@physical_device_name, @backupset_name
 END
 CLOSE backup_cursor;
 DEALLOCATE backup_cursor;

 IF (@lastfile<>1)
  INSERT INTO #RestoreCommand (
   restore_command)
  VALUES
   (
   'You need to back up the Tail of the Log on database [' +@SourceDB+'] before restoring, then restore the tail-log backup with recovery as last step!'
   )
 select * from #RestoreCommand order by [ID]
 DROP TABLE #RestoreCommand

END