Restore script for Ola Hallengren backup

• Joe O'Connor

  SSC Eights!

  Points: 904

  More actions

  April 20, 2016 at 2:21 pm

  #306657

  Comments posted to this topic are about the item Restore script for Ola Hallengren backup

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 3, 2016 at 6:47 am

  #1876195

  Thanks for the script.

• Alex Gay

  SSCrazy

  Points: 2217

  More actions

  May 4, 2016 at 3:54 am

  #1876515

  Hi Joe,

  Thanks for the script, it will save a lot of effort when I need to restore a database in the future.

  I original got the original from Jason's site. Oops! :Whistling:

• DBA Pete

  SSC Veteran

  Points: 232

  More actions

  May 4, 2016 at 7:09 pm

  #1876748

  Hi Joe,

  Thanks for the script.

  Note that it does not cover Availability Group database backups.

  Ola Hallengren's maintenance solution places these backups in a separate folder for each Availability Group.

  Regards,

  DBA Pete.

• Joe O'Connor

  SSC Eights!

  Points: 904

  More actions

  May 5, 2016 at 5:41 am

  #1876798

  DBA Pete (5/4/2016)

  ---

  Hi Joe,

  Thanks for the script.

  Note that it does not cover Availability Group database backups.

  Ola Hallengren's maintenance solution places these backups in a separate folder for each Availability Group.

  Regards,

  DBA Pete.

  Thanks for the info - I'll have to address that!

• ildjarn.is.dead

  Ten Centuries

  Points: 1164

  More actions

  December 8, 2017 at 12:59 am

  #1971308

  I've made some modifications to allow Point-In-Time restores. Code to convert the date_time variable to a format that STOPAT understands is rather ugly however, perhaps someone with a better understanding of CAST/CONVERT can prettify it?

  
/*
Description:
T-SQL Script to generate a restore script for a database backed up to disk using Ola Hallengren's
maintenance solution. The script is based solely on the contents of a directory, taking into
account the order of full backups, differential backups and transaction log backups.

  Maintenance Script Credit: Ola Hallengren
https://ola.hallengren.com/

  Original Restore Script Credit: Greg Robidoux
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

  Modified Restore Script Credit: Jason Carter
http://jason-carter.net/professional/restore-script-from-backup-directory-modified.html

  Reason for change:
Ola's script uses a .BAK extension for differentials, and stores FULL, DIFF and LOG backups in a sub-folder
heirarchy that matches @backupPath\@@SERVERNAME\@dbName\[FULL|DIFF|LOG]\ and the filename also contains what
type of backup file it is within the filename, making filename comparison for order of restore impossible.

  ChangeLog:
    2/24/2016 - Joe O'Connor (thirtybird@gmail.com)
Allow backup paths to have spaces in them by encapsulating path in the command in quotes
Fixed DIR command - /O D to /O:D to guarantee order by date to ensure transaction logs are restored in proper order
Added wrapper to enable and disable xp_cmdshell and re-set the "show advanced options" setting to whatever it was
    If you have xp_cmdshell enabled in your environment, take this part out or it will get disabled!
Took out extra @backupPath in each RESTORE command - the full path is output into the file list and was duplicated in the output
Added backupTime to the local table variable to be used for comparison as comparing filenames doesn't work with Ola's filenames
    This involves substring parsing (and is ugly).

      2/25/2016 - Joe O'Connor (thirtybird@gmail.com)
Added logic to check to see if xp_cmdshell needs to be enabled or not. Only enables and disables if it needs to.
Verified it does not disable xp_cmdshell if it was enabled to start with.

      3/16/2016 - Joe O'Connor (thirtybird@gmail.com)
Added a RESTORE FILELISTONLY as the first output command.

      4/20/2016 - Joe O'Connor (thirtybird@gmail.com)
Converted code to utilize xp_dirtree instead of xp_cmdshell
Re-ordered some code to get the variables that need to be set closer to the top of the script
Fixed problem with compatibility with named instances (replacing '\' with '$' in @@SERVERNAME)

      12/7/2017 - Joost de Heer
Added support for point-in-time restores

  Tested against: SQL 2005 - 2014
*/

  USE Master;
GO
SET NOCOUNT ON

  /**
    Variable declaration
**/
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @pointintime CHAR(1)
DECLARE @pit_date_time CHAR(15)

  /**
    Initialize variables
    dbName        Name of the database to restore
    backupPath    The backup path as defined in Ola Hallengren's scripts
    pointintime    If set to 'Y', pit_date_time must be filled too
    pit_date_time    yyyymmdd_hhMMSS. No check is done, so if you don't use this pattern the
            script will produce nonsense!
**/
SET @dbName = 'AdventureWorks2014'
SET @backupPath = 'C:\Backup'
SET @pointintime = 'Y'
SET @pit_date_time = '20171208_193000'

  /**
    Convert the variables to match that of Olas maintenance script
**/
IF RIGHT (@backupPath,1) = '\' SET @backupPath = SUBSTRING (@backupPath, 1, LEN (@backupPath)-1)
SET @backupPath = @backupPath + '\' + REPLACE(@@SERVERNAME,'\','$') + '\' + @dbName + '\'

  /**
    Get List of Files
**/
IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
     DROP TABLE #DirectoryTree;

  CREATE TABLE #DirectoryTree (
      backupFile nvarchar(255) NOT NULL
     , depth int
     , isfile bit
     , backupTime NVARCHAR(20));

  /**
    Create a clustered index to keep everything in order by filename.
**/
ALTER TABLE #DirectoryTree
ADD CONSTRAINT PK_DirectoryTree PRIMARY KEY CLUSTERED (backupFile);

  INSERT #DirectoryTree (backupFile,depth,isfile)
EXEC xp_dirtree @backupPath,2,1;

  /**
    Get rid of the directories
**/
DELETE FROM #DirectoryTree WHERE isfile=0

  /**
  Figure out the backup time for comparison since file names cannot be compare as all DIFF and LOG backups will be > the full
  Also append the @backupPath to the backup File name in the table as well
**/

  -- Update the FULL's
UPDATE #DirectoryTree
SET backupTime =
SUBSTRING(backupfile, CHARINDEX (@dbName+'_FULL_',backupFile) +LEN(@dbName+'_FULL_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_FULL_',backupFile) +LEN(@dbName+'_FULL_')))
, backupfile = @backupPath + 'FULL\' + backupfile
FROM #DirectoryTree
WHERE CHARINDEX (@dbName+'_FULL_',backupFile) > 0

  -- Update the DIFF's
UPDATE #DirectoryTree
SET backupTime =
SUBSTRING(backupfile, CHARINDEX (@dbName+'_DIFF_',backupFile) +LEN(@dbName+'_DIFF_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_DIFF_',backupFile) +LEN(@dbName+'_DIFF_')))
, backupfile = @backupPath + 'DIFF\' + backupfile
FROM #DirectoryTree
WHERE CHARINDEX (@dbName+'_DIFF_',backupFile) > 0

  -- Update the LOGs
UPDATE #DirectoryTree
SET backupTime =
SUBSTRING(backupfile, CHARINDEX (@dbName+'_LOG_',backupFile) +LEN(@dbName+'_LOG_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_LOG_',backupFile) +LEN(@dbName+'_LOG_')))
, backupfile = @backupPath + 'LOG\' + backupfile
FROM #DirectoryTree
WHERE CHARINDEX (@dbName+'_LOG_',backupFile) > 0

  /**
    Find latest full backup
**/
DECLARE    @cmd NVARCHAR(500)
        , @lastFullBackup NVARCHAR(500)
        , @lastDiffBackup NVARCHAR(500)
        , @lastTransBackup NVARCHAR(500)
        , @backupFile NVARCHAR(500)
        , @lastFullBackupTime NVARCHAR(20)
        , @lastDiffBackupTime NVARCHAR(20)
        , @pitstring NVARCHAR(25)

  IF @pointintime = 'Y'
BEGIN
    SELECT TOP 1 @lastFullBackup = backupFile
        , @lastFullBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
        backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_FULL_%.bak'
        AND backuptime < @pit_date_time
    ORDER BY backupTime DESC
END
ELSE
BEGIN
    SELECT TOP 1 @lastFullBackup = backupFile
        , @lastFullBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
        backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_FULL_%.bak'
    ORDER BY backupTime DESC
END

  IF @lastFullBackup IS NULL
BEGIN
    PRINT 'No full backup available for this restore, restore can''t continue'
    GOTO CLEANUP;
END

  SET @cmd = 'RESTORE FILELISTONLY FROM DISK = '''
   + @lastFullBackup + ''' WITH FILE = 1'
PRINT @cmd

  SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
   + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

  /**
    Find latest diff backup
**/

  IF @pointintime = 'Y'
BEGIN
    SELECT TOP 1 @lastDiffBackup = backupFile
        , @lastDiffBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
        backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_DIFF_%.bak'
        AND backupTime > @lastFullBackupTime
        AND backupTime < @pit_date_time
    ORDER BY backupTime DESC;
END
ELSE
BEGIN
    SELECT TOP 1 @lastDiffBackup = backupFile
        , @lastDiffBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
        backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_DIFF_%.bak'
        AND backupTime > @lastFullBackupTime
    ORDER BY backupTime DESC;
END

  /**
    check to make sure there is a diff backup
**/
IF @lastDiffBackup IS NOT NULL
    BEGIN
     SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
         + @lastDiffBackup + ''' WITH NORECOVERY'
     PRINT @cmd
     SET @lastFullBackupTime = @lastDiffBackupTime
    END

  /**
    check for log backups
**/

  IF @pointintime = 'Y'
BEGIN
    DECLARE backupFiles CURSOR FOR
        SELECT backupFile
        FROM #DirectoryTree
        WHERE
            backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_LOG_%.trn'
            AND backupTime > @lastFullBackupTime
            AND backupTime < @pit_date_time;
END
ELSE
BEGIN
    DECLARE backupFiles CURSOR FOR
        SELECT backupFile
        FROM #DirectoryTree
        WHERE
            backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_LOG_%.trn'
            AND backupTime > @lastFullBackupTime;
END

  OPEN backupFiles

  /**
    Loop through all the files for the database
**/
FETCH NEXT FROM backupFiles INTO @backupFile

  WHILE @@FETCH_STATUS = 0
    BEGIN
     SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
         + @backupFile + ''' WITH NORECOVERY'
     PRINT @cmd
     FETCH NEXT FROM backupFiles INTO @backupFile
    END

  CLOSE backupFiles
DEALLOCATE backupFiles

  /**
    Find the first log after the point in time restore
**/

  IF @pointintime = 'Y'
BEGIN
    SELECT TOP 1 @lastTransBackup = backupFile
    FROM #DirectoryTree
    WHERE
        backupFile LIKE '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName + '_LOG_%.trn'
        AND backupTime > @pit_date_time
    ORDER BY backuptime asc

      IF @lastTransBackup IS NULL
    BEGIN
        PRINT '-- No transaction log available after the point-in-time given, so restore may be incomplete'
        GOTO START_DB
    END
    
    SELECT @pitstring = SUBSTRING(@pit_date_time,1,8) + ' ' + SUBSTRING(@pit_date_time,10,2) + ':' + SUBSTRING(@pit_date_time, 12, 2) + ':' + SUBSTRING(@pit_date_time,14,2)

      SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
        + @lasttransbackup + ''' WITH NORECOVERY, STOPAT = ''' + CAST(CAST(@pitstring as datetime) AS varchar(40)) + ''''
    PRINT @cmd
END

  /**
    put database in a useable state
**/
START_DB:
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
PRINT @cmd

  CLEANUP:
/**
  Cleanup our temp table
**/

  IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
  DROP TABLE #DirectoryTree;
GO


• christian.lorber

  Grasshopper

  Points: 19

  More actions

  March 15, 2019 at 9:37 am

  #2024600

  I did some small modifications to support availability groups

  
  /*
  Description:
  T-SQL Script to generate a restore script for a database backed up to disk using Ola Hallengren's
  maintenance solution. The script is based solely on the contents of a directory, taking into
  account the order of full backups, differential backups and transaction log backups.

  Maintenance Script Credit: Ola Hallengren
  https://ola.hallengren.com/

  Original Restore Script Credit: Greg Robidoux
  https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

  Modified Restore Script Credit: Jason Carter
  http://jason-carter.net/professional/restore-script-from-backup-directory-modified.html

  Reason for change:
  Ola's script uses a .BAK extension for differentials, and stores FULL, DIFF and LOG backups in a sub-folder
  heirarchy that matches @backupPath\@@SERVERNAME\@dbName\[FULL|DIFF|LOG]\ and the filename also contains what
  type of backup file it is within the filename, making filename comparison for order of restore impossible.

  ChangeLog:
    2/24/2016 - Joe O'Connor (thirtybird@gmail.com)
  Allow backup paths to have spaces in them by encapsulating path in the command in quotes
  Fixed DIR command - /O D to /O to guarantee order by date to ensure transaction logs are restored in proper order
  Added wrapper to enable and disable xp_cmdshell and re-set the "show advanced options" setting to whatever it was
    If you have xp_cmdshell enabled in your environment, take this part out or it will get disabled!
  Took out extra @backupPath in each RESTORE command - the full path is output into the file list and was duplicated in the output
  Added backupTime to the local table variable to be used for comparison as comparing filenames doesn't work with Ola's filenames
    This involves substring parsing (and is ugly).

    2/25/2016 - Joe O'Connor (thirtybird@gmail.com)
  Added logic to check to see if xp_cmdshell needs to be enabled or not. Only enables and disables if it needs to.
  Verified it does not disable xp_cmdshell if it was enabled to start with.

    3/16/2016 - Joe O'Connor (thirtybird@gmail.com)
  Added a RESTORE FILELISTONLY as the first output command.

    4/20/2016 - Joe O'Connor (thirtybird@gmail.com)
  Converted code to utilize xp_dirtree instead of xp_cmdshell
  Re-ordered some code to get the variables that need to be set closer to the top of the script
  Fixed problem with compatibility with named instances (replacing '\' with '$' in @@SERVERNAME)

    12/7/2017 - Joost de Heer
  Added support for point-in-time restores

    15/3/2019 - Christian Lorber
  Added support for availability groups

  Tested against: SQL 2005 - 2017
  */

  SET NOCOUNT ON

  /**
    Variable declaration
  **/

  DECLARE @dbName sysname
  DECLARE @backupPathInit NVARCHAR(500)
  DECLARE @pointintime CHAR(1)
  DECLARE @pit_date_time CHAR(15)

  DECLARE @backupPath NVARCHAR(500)
  DECLARE @backupfilecompare VARCHAR(255)
  DECLARE @AGName NVARCHAR(100)
  DECLARE @Clustername VARCHAR(100)

  /**
    Initialize variables
  **/
  SET @dbName = 'AdventureWorks2014'
  SET @backupPathInit = 'C:\Backup'
  SET @pointintime = 'Y'
  SET @pit_date_time = '20190315_113000'
  SET @backupPath = @backupPathInit

  /**
  get availability group Information - if there is no availabiliy group @@servername will be used instead
  **/

  SELECT @AGName =
  Groups.[Name]
  FROM sys.dm_hadr_availability_group_states States
  INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
  INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
  WHERE primary_replica = @@Servername AND AGDatabases.database_name = @dbName

  IF @AGName IS NULL
  BEGIN    
      /**
          Convert the variables to match that of Olas maintenance script for no availability groups
      **/
      IF RIGHT (@backupPath,1) = '\' SET @backupPath = SUBSTRING (@backupPath, 1, LEN (@backupPath)-1)
      SET @backupPath = @backupPath + '\' + REPLACE(@@SERVERNAME,'\','$') + '\' + @dbName + '\'
      SET @backupfilecompare = '%' + REPLACE(@@SERVERNAME,'\','$') + '_' + @dbName

  END    
  ELSE
  BEGIN
      /**
          Convert the variables to match that of Olas maintenance script for availability groups
      **/
      SELECT @Clustername = cluster_name FROM master.sys.dm_hadr_cluster;
      IF RIGHT (@backupPath,1) = '\' SET @backupPath = SUBSTRING (@backupPath, 1, LEN (@backupPath)-1)
      SET @backupPath = @backupPath + '\' + @Clustername + '$' + @AGName + '\' + @dbName + '\'
      SET @backupfilecompare = '%' + REPLACE(REPLACE(@backupPath, @backupPathInit, '%'),'\' + @dbName +'\','') + '_' + @dbName
  END    

  /**
    Get List of Files
  **/
  IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
    DROP TABLE #DirectoryTree;

  CREATE TABLE #DirectoryTree (
    backupFile nvarchar(255) NOT NULL
    , depth int
    , isfile bit
    , backupTime NVARCHAR(20));

  /**
    Create a clustered index to keep everything in order by filename.
  **/
  ALTER TABLE #DirectoryTree
  ADD CONSTRAINT PK_DirectoryTree PRIMARY KEY CLUSTERED (backupFile);

  INSERT #DirectoryTree (backupFile,depth,isfile)
  EXEC xp_dirtree @backupPath,2,1;

  /**
    Get rid of the directories
  **/
  DELETE FROM #DirectoryTree WHERE isfile=0

  /**
  Figure out the backup time for comparison since file names cannot be compare as all DIFF and LOG backups will be > the full
  Also append the @backupPath to the backup File name in the table as well
  **/

  -- Update the FULL's
  UPDATE #DirectoryTree
  SET backupTime =
  SUBSTRING(backupfile, CHARINDEX (@dbName+'_FULL_',backupFile) +LEN(@dbName+'_FULL_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_FULL_',backupFile) +LEN(@dbName+'_FULL_')))
  , backupfile = @backupPath + 'FULL\' + backupfile
  FROM #DirectoryTree
  WHERE CHARINDEX (@dbName+'_FULL_',backupFile) > 0

  -- Update the DIFF's
  UPDATE #DirectoryTree
  SET backupTime =
  SUBSTRING(backupfile, CHARINDEX (@dbName+'_DIFF_',backupFile) +LEN(@dbName+'_DIFF_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_DIFF_',backupFile) +LEN(@dbName+'_DIFF_')))
  , backupfile = @backupPath + 'DIFF\' + backupfile
  FROM #DirectoryTree
  WHERE CHARINDEX (@dbName+'_DIFF_',backupFile) > 0

  -- Update the LOGs
  UPDATE #DirectoryTree
  SET backupTime =
  SUBSTRING(backupfile, CHARINDEX (@dbName+'_LOG_',backupFile) +LEN(@dbName+'_LOG_'), (LEN(backupfile) - CHARINDEX ('.',REVERSE(backupFile))) + 1 - (CHARINDEX (@dbName+'_LOG_',backupFile) +LEN(@dbName+'_LOG_')))
  , backupfile = @backupPath + 'LOG\' + backupfile
  FROM #DirectoryTree
  WHERE CHARINDEX (@dbName+'_LOG_',backupFile) > 0

  /**
    Find latest full backup
  **/
  DECLARE  @cmd NVARCHAR(500)
     , @lastFullBackup NVARCHAR(500)
     , @lastDiffBackup NVARCHAR(500)
     , @lastTransBackup NVARCHAR(500)
     , @backupFile NVARCHAR(500)
     , @lastFullBackupTime NVARCHAR(20)
     , @lastDiffBackupTime NVARCHAR(20)
     , @pitstring NVARCHAR(25)

  IF @pointintime = 'Y'
  BEGIN
    SELECT TOP 1 @lastFullBackup = backupFile
     , @lastFullBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
     backupFile LIKE @backupfilecompare + '_FULL_%.bak'
     AND backuptime < @pit_date_time
    ORDER BY backupTime DESC

  END
  ELSE
  BEGIN
    SELECT TOP 1 @lastFullBackup = backupFile
     , @lastFullBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
     backupFile LIKE @backupfilecompare + '_FULL_%.bak'
    ORDER BY backupTime DESC

  END

  IF @lastFullBackup IS NULL
  BEGIN
    PRINT 'No full backup available for this restore, restore can''t continue'
    GOTO CLEANUP;
  END

  SET @cmd = 'RESTORE FILELISTONLY FROM DISK = '''
   + @lastFullBackup + ''' WITH FILE = 1'
  PRINT @cmd

  SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
   + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
  PRINT @cmd

  /**
    Find latest diff backup
  **/

  IF @pointintime = 'Y'
  BEGIN
    SELECT TOP 1 @lastDiffBackup = backupFile
     , @lastDiffBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
     backupFile LIKE @backupfilecompare + '_DIFF_%.bak'
     AND backupTime > @lastFullBackupTime
     AND backupTime < @pit_date_time
    ORDER BY backupTime DESC;
  END
  ELSE
  BEGIN
    SELECT TOP 1 @lastDiffBackup = backupFile
     , @lastDiffBackupTime = backupTime
    FROM #DirectoryTree
    WHERE
     backupFile LIKE @backupfilecompare + '_DIFF_%.bak'
     AND backupTime > @lastFullBackupTime
    ORDER BY backupTime DESC;
  END

  /**
    check to make sure there is a diff backup
  **/
  IF @lastDiffBackup IS NOT NULL
    BEGIN
    SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
     + @lastDiffBackup + ''' WITH NORECOVERY'
    PRINT @cmd
    SET @lastFullBackupTime = @lastDiffBackupTime
    END

  /**
    check for log backups
  **/

  IF @pointintime = 'Y'
  BEGIN
    DECLARE backupFiles CURSOR FOR
     SELECT backupFile
     FROM #DirectoryTree
     WHERE
      backupFile LIKE @backupfilecompare + '_LOG_%.trn'
      AND backupTime > @lastFullBackupTime
      AND backupTime < @pit_date_time;
  END
  ELSE
  BEGIN
    DECLARE backupFiles CURSOR FOR
     SELECT backupFile
     FROM #DirectoryTree
     WHERE
      backupFile LIKE @backupfilecompare + '_LOG_%.trn'
      AND backupTime > @lastFullBackupTime;
  END

  OPEN backupFiles

  /**
    Loop through all the files for the database
  **/
  FETCH NEXT FROM backupFiles INTO @backupFile

  WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
     + @backupFile + ''' WITH NORECOVERY'
    PRINT @cmd
    FETCH NEXT FROM backupFiles INTO @backupFile
    END

  CLOSE backupFiles
  DEALLOCATE backupFiles

  /**
    Find the first log after the point in time restore
  **/

  IF @pointintime = 'Y'
  BEGIN
    SELECT TOP 1 @lastTransBackup = backupFile
    FROM #DirectoryTree
    WHERE
     backupFile LIKE REPLACE(@backupfilecompare,'%%','%') + '_LOG_%.trn'
     AND backupTime > @pit_date_time
    ORDER BY backuptime asc

    IF @lastTransBackup IS NULL
    BEGIN
     PRINT '-- No transaction log available after the point-in-time given, so restore may be incomplete'
     GOTO START_DB
    END
   
    SELECT @pitstring = SUBSTRING(@pit_date_time,1,8) + ' ' + SUBSTRING(@pit_date_time,10,2) + ':' + SUBSTRING(@pit_date_time, 12, 2) + ':' + SUBSTRING(@pit_date_time,14,2)

    SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
     + @lasttransbackup + ''' WITH NORECOVERY, STOPAT = ''' + CAST(CAST(@pitstring as datetime) AS varchar(40)) + ''''
    PRINT @cmd
  END

  /**
    put database in a useable state
  **/
  START_DB:
  SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
  PRINT @cmd

  CLEANUP:
  /**
  Cleanup our temp table
  **/

  IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
  DROP TABLE #DirectoryTree;
  GO

• March 10, 2020 at 3:25 pm

  This was removed by the editor as SPAM

• mcarter1027

  SSC Enthusiast

  Points: 115

  More actions

  April 5, 2021 at 3:29 pm

  #3865640

  Does this generate a AG restore script for anyone else? I cannot get this to generate a restore script. All I get is 'No full backup available for this restore, restore can't continue' and I do have @LogToTable set to 'Y'.

• April 6, 2021 at 3:00 pm

  This was removed by the editor as SPAM

• April 6, 2021 at 3:02 pm

  This was removed by the editor as SPAM

• April 6, 2021 at 3:06 pm

  This was removed by the editor as SPAM

• April 6, 2021 at 3:13 pm

  This was removed by the editor as SPAM

• April 6, 2021 at 3:32 pm

  This was removed by the editor as SPAM