Create a point-in-time restore automatically – SQL 2005 version


A new version has been created! Check it out here: Updated: create point-in-time restore script automatically

This is a 2005-version of the script originally posted here: Create a full Point-In-Time restore script automatically! – 2008 version

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
Works only on SQL2005 databases!!

Script for generating restore script for full backup originally created by Michael 
Valentine Jones, SQLTeam.com

The rest is created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO

DECLARE 
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1),
  @cr varchar(2),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL nvarchar(max)
  
SET @DBName = 'MyDatabaseName'
SET @PointInTime = '2011-11-22 09:45:00'
SET @tab = char(9)
SET @cr = char(13)+char(10)
SET @SQL = ''


BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime, restore is not possible at the given PointInTime.', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail-log backup before running this script again', 16, 1)

  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
    @Filename = b.physical_device_name,
    @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC

  --> Create temp-tables for file header information
  DECLARE @header table (
	  BackupName varchar(128) NULL,
	  BackupDescription varchar(255) NULL,
	  BackupType smallint NULL,
	  ExpirationDate datetime NULL,
	  Compressed tinyint NULL,
	  Position smallint NULL,
	  DeviceType tinyint NULL,
	  UserName varchar(128) NULL,
	  ServerName varchar(128) NULL,
	  DatabaseName varchar(128) NULL,
	  DatabaseVersion int NULL,
	  DatabaseCreationDate datetime NULL,
	  BackupSize numeric(20,0) NULL,
	  FirstLSN numeric(25,0) NULL,
	  LastLSN numeric(25,0) NULL,
	  CheckpointLSN numeric(25,0) NULL,
	  DatabaseBackupLSN numeric(25,0) NULL,
	  BackupStartDate datetime NULL,
	  BackupFinishDate datetime NULL,
	  SortOrder smallint NULL,
	  CodePage smallint NULL,
	  UnicodeLocaleId int NULL,
	  UnicodeComparisonStyle int NULL,
	  CompatibilityLevel tinyint NULL,
	  SoftwareVendorId int NULL,
	  SoftwareVersionMajor int NULL,
	  SoftwareVersionMinor int NULL,
	  SoftwareVersionBuild int NULL,
	  MachineName varchar(128) NULL,
	  Flags int NULL,
	  BindingID uniqueidentifier NULL,
	  RecoveryForkID uniqueidentifier NULL,
	  Collation varchar(128) NULL,
	  FamilyGUID uniqueidentifier NULL,
	  HasBulkLoggedData bit NULL,
	  IsSnapshot bit NULL,
	  IsReadOnly bit NULL,
	  IsSingleUser bit NULL,
	  HasBackupChecksums bit NULL,
	  IsDamaged bit NULL,
	  BeginsLogChain bit NULL,
	  HasIncompleteMetaData bit NULL,
	  IsForceOffline bit NULL,
	  IsCopyOnly bit NULL,
	  FirstRecoveryForkID uniqueidentifier NULL,
	  ForkPointLSN numeric(25,0) NULL,
	  RecoveryModel varchar(60) NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  BackupTypeDescription varchar(60) NULL,
	  BackupSetGUID uniqueidentifier NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Create temp-table for db file information
  DECLARE @filelist TABLE  (
	  LogicalName varchar(128) NULL,
	  PhysicalName varchar(260) NULL,
	  Type char(1) NULL,
	  FileGroupName varchar(128) NULL,
	  Size numeric(20,0) NULL,
	  MaxSize numeric(20,0) NULL,
	  FileID bigint NULL,
	  CreateLSN numeric(25,0) NULL,
	  DropLSN numeric(25,0) NULL,
	  UniqueID uniqueidentifier NULL,
	  ReadOnlyLSN numeric(25,0) NULL,
	  ReadWriteLSN numeric(25,0) NULL,
	  BackupSizeInBytes bigint NULL,
	  SourceBlockSize int NULL,
	  FileGroupID int NULL,
	  LogGroupGUID uniqueidentifier NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  IsReadOnly bit NULL,
	  IsPresent bit NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Get header and filelist information from the backup file
  INSERT INTO @header
  EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @Filename + '''')

  INSERT INTO @filelist
  EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @Filename + '''')

  --> Generate the full backup restore script
  SELECT
    @SQL = @SQL + 
      CASE
        WHEN a.Seq = 1 THEN 
          @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
          @cr + 'FROM DISK =' + @cr + @tab + '''' +
          @Filename + '''' + @cr + 'WITH' 
        ELSE ''
      END
      + @cr + @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''',' 
      +
      CASE 
        WHEN a.Seq = b.Seq THEN
          @cr + @tab + 'REPLACE, STATS = 5, NORECOVERY'
        ELSE ''
      END
  FROM
    @filelist a
    CROSS JOIN
      (SELECT Seq = MAX(b1.Seq) FROM @filelist b1 ) b
    CROSS JOIN
      (SELECT DatabaseName = MAX(c1.DatabaseName) FROM @header c1) c
  ORDER BY
    a.Seq
    
  SELECT @SQL = @SQL + @cr + 'GO' + @cr + @cr 
    
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
    
  --> Generate all log restores except the last one
  SELECT 
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
  
  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' 
      WITH RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC

  PRINT @SQL

END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH

Advertisements

One thought on “Create a point-in-time restore automatically – SQL 2005 version

  1. Pingback: Create a full Point-In-Time restore script automatically! « TheFirstSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s