Monday, April 30, 2012

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

4 comments:

  1. Thanks for sharing information about restore backup of database, i got something knowledge from your posting, keep it up... political news paper

    ReplyDelete
    Replies
    1. thank you, I am glad you like it

      Delete
  2. Very helpful blog... Thanks for sharing valuable information on "Generate restore script automatically by backup history table". Automatically backup SQL database is very helpful.

    ReplyDelete