Updated: create point-in-time restore script automatically


The script I created for generating a point-in-time restore t-sql code has been updated and the last version should support sql server 2005, 2008 and 2012 versions.

There is one prerequisite:
– Only backups made to file on disk is supported

Here is the final script:

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
 
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
 
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''
 
SET @DBName  = 'MyDatabase'
SET @PointInTime = '2013-05-21 15:40:00'
 
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName)
    RAISERROR(N'The database has never been backed up', 16, 1)
  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', 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 first', 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
 
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @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 = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC   

  PRINT @SQL
  SET @SQL = ''
   
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', 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
  
  PRINT @SQL
  SET @SQL = ''
   
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
     
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', 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
 
  PRINT @SQL
  SET @SQL = ''

  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', 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

16 thoughts on “Updated: create point-in-time restore script automatically

  1. Nice script. I ran it and unfortenately it broke down on one of our databases.
    The reason for this is that one database has a bracket in it’s name.

    To remedy this, I had to replace all instances of [‘ + @DBName + ‘] with ‘ + QUOTENAME(@DBName) + ‘

    • Great feedback! I udated the script acordingly. And I’ll just not ask why anyone would put a bracket in the database name ;)

      • I receive the following error:
        Msg 137, Level 15, State 1, Line 13
        Must declare the scalar variable “@MyDatabase”.

        Then I declare @MyDatabase but I receive other errors:

        Msg 207, Level 16, State 1, Line 46
        Invalid column name ‘Position’.
        Msg 207, Level 16, State 1, Line 59
        Invalid column name ‘Position’.
        Msg 207, Level 16, State 1, Line 76
        Invalid column name ‘Position’.
        Msg 207, Level 16, State 1, Line 90
        Invalid column name ‘Position’.

        Could you please give me a feedback?
        Thank you very much.
        Best Regards.

        Frank

  2. Hi Frank, don’t know how that got in there but I’ve updated the code now, SET @MyDatabase should have been SET @DBName. Unfortunately I don’t have a chance to test the script now but I’m pretty sure the position-column is correct. Which sql server version are you on?

  3. Hi Henning, thank you very much for your quick reply.
    I am testing your code on SQL Server 2008 R2 Enterprise Edition 64bit.
    I also have a SQL Server 2008 R2 Standard Edition to test soon, today.
    I’ll let you know asap.
    Cheers.

    • Hi Henning,
      I can confirm that the previous errors are gone, thank you!

      Sometimes the script get truncated.
      It seems that this is related to the number of tlog backups.

      This is successfull:
      SET @DBName = ‘test’
      SET @PointInTime = ‘2013-01-08 05:00:00’

      WITH FILE=1, RECOVERY, STOPAT = ‘2013-01-08 05:00:00’
      GO

      This get truncated:
      SET @DBName = ‘test’
      SET @PointInTime = ‘2013-01-08 06:00:00’
      ….
      WITH FILE=1, NORECOVERY
      GO
      RESTO

      Until 05.35 it works but if I put 05.36 the script is truncated:

      RESTORE LOG [test] FROM DISK = ‘X:\backup\server\test\LOG\server_test_LOG_20130108_053901.trn’
      WITH FILE=1, RECOVERY, STOPAT = ‘2

      We take t-log backup every 4 minutes from 05.03 AM until 23.59 PM.

      Cheers.
      Frank

      • Excellent! The truncation is probably because of SSMS settings…increase the maximum character output for text results and it should be just fine :)

      • Thank you again for your support and time.
        I think I already have the maximum set which is 8192 characters….
        This is done under Tools/Options in SSMS right?

  4. Hmmm, something strange.
    I count the output with Microsoft Word and it says 3847 characters with spaces.
    Could it be something else?

  5. Hi Henning,

    Your script is simply superb. However I am having an issue with the output.. Actually, we have a 15 mins t-log backup schedule and the script generates large output.. So only a portion of restore is generated in output. Is there anyway to solve this?

    Thanks,
    KiranK

    • Hi Kiran! Seems like the print-statement truncates a variable to 4000 characters for nvarchar and 8000 for varchar, so I did some modifications to the script to handle this. Can you check and see if it’s working for you now?

      • Thanks Henning for the updated script. it looks like the output issue is resolved but still one thing at the last restore log command which is coming twice. Can you please look into that..I have copied the last rows output for your reference. For now, I have created a temp table and able to use the script without any issues.

        RESTORE LOG [XYZ] FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MYSERVER\XYZ\LOG\MYSERVER
        RESTORE LOG [XYZ] FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MYSERVER\XYZ\LOG\MYSERVER_XYZ_LOG_20130521_184501.trn’
        WITH FILE=1, RECOVERY, STOPAT = ‘2013-05-21 18:40:00’
        GO

  6. Check the log shipping aerlts and find out on what alert condition you have set them to fire. By default, the log shipping aerlts (if setup) fire only after a configured latency is observed. Also, if the log shipping jobs fail, an alert response has to be configured for the jobs for you to be notified. If this is already done, then you need to investigate by collecting profiler traces to check what errors are being observed when the alert is being fired.

  7. I continually get this, see below, an additional line with no file name
    GO
    RESTORE LOG [dbname] FROM DISK = ‘G:\Daily\
    RESTORE LOG [dbname] FROM DISK = ‘G:\Daily\Prod\dbname\LOG_dbname_20160610230800.sqb’
    WITH FILE=1, RECOVERY, STOPAT = ‘2016-06-10 23:00:00’

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