Restore Gene Version 3 Comments

A few of the comments received for Restore Gene :-

  • Just wanted to drop you a note and let you know that my migration from SQL 2005 to SQL 2014 was a success, and this script performed spectacularly. It made the process so much easier to script, and allowed me to focus my attention on the other important details. Thanks again to you Paul and to all contributors!
  • This script saved me big time., I am reaching out to you if you have updates or anything more current.
  • Hi Paul, I just wanted to say thank you for sharing your restore proc on SQLServerCentral.  It was a big time saver for me and exactly what we needed!
  • This is an awesome solution and I thank you for sharing it with the community!

There have been many more, also suggestions and bug fixes which have all been implemented.

The latest versions are here:

User Guide – https://paulbrewer.wordpress.com/2016/08/05/restoregeneversion6/

Stored Procedure – https://paulbrewer.wordpress.com/sp_restoregene/

PowerShell Script – https://paulbrewer.wordpress.com/ps_restoregene/



Categories: Restore Gene

26 replies

  1. I’m currently evaulating your script for use in a multi-tenant production system; one thing I’m seeing is that your PROC doesn’t take into account there may not be registered backup devices (a la if you use Ola Hallengren’s maintanance script), and it doesn’t take into account that anyone running a network drive on AWS EC2 is going to see their device show up as type ‘7’ (virtual device). Specifically, the line “AND Device_Type in (102,2)” wont’ capture anything I mentioned above. I had to change it to (7,102,2) in my case in order to catch backups taken.

    The relevant Section of code is below.

    INNER JOIN
    (
    SELECT
    database_name
    ,MAX(backup_finish_date) backup_finish_date
    FROM msdb.dbo.backupset a
    JOIN msdb.dbo.backupmediafamily b
    ON a.media_set_id = b.media_set_id
    WHERE a.[type] = ‘D’
    AND Device_Type IN (102,2)
    AND a.is_copy_only = 0
    AND a.backup_finish_date <= @LogShippingStartTime
    GROUP BY database_name
    ) x

    Like

  2. Could you send me a snippet of code generated where the backup device is an issue to clarify it? The AWS backup type is something I am not familiar with or thought about but will add it and do a full regression test.

    Thanks very much for the feedback, really appreciated.
    Regards
    Paul

    Like

  3. Thanks; I’ll fork and throw my changes in there and submit a pull request. This week, sometime.

    Like

  4. Awesome script. Had a bit of a hard time getting it to work as my default backup location is on a shared drive. It finds it if I explicitly specify the UNC path, but if left blank, the restore powerscript passes a UNC path of ”. The StoreProcedure tests to see if this value is null, which is not and does not grab the full physical_path as specified in the column. I added an additional WHEN to the check for ” in addition to NULL value. Works beautifully.

    Thank you Paul.

    Like

  5. Version 4 released which includes the following fixes:
    – Lots of unnecessary _msg variables removed
    – Support for a single backup fork added
    – Support for default backup paths, for UNC backups there is now no need to declare them in ps_RestoreGene
    – Support for databases which a – in their name

    All feedback and suggestions most welcome, thanks
    Paul

    Like

  6. This maybe near perfect for me and a life saver potentially…
    Im just in the process of evaluating what I need to do for a task that has landed in my lap..
    And Im new TSQL so looking all over the internet…

    The brief is that backup and checkdb should be run and that we can parameter to an extent what DBs need to be backed up and then restored all in one process.
    SQL 2k databases that need to be backed up and then restored from a NW share to SQL 2008 Server.

    I have some questions :
    1. Different versions between SQL servers for the source and target? Compatibility mode ?
    2. Also the SQL 2k dbs are all on one old domain and will be restored to a different domain – will the process be able to handle this?

    Thanks

    Like

    • Hi Mo,
      The procedure doesn’t work with SQL Server 2000. It uses common table expressions and other operations that are only available in 2005 onwards. SQL Server 2005 is the earliest supported version.
      Good luck with your project and best wishes
      Paul

      Like

  7. This is fantastic. I’m sorry, i’m missing something. I wanted to figure out how to restore some ola hallegren backups that I copied to another server. This would mean they aren’t part of the backup history on this server. Is there any functionality I’m missing on getting this to work? I need the flexibility in case I needed to restore the backups on a mirror server or another instance, and currently I’m not finding anything being generated. The alternative solution I was approaching was https://github.com/dbassassin/simpletalk/edit/master/ManagingTestData/sp_automate_restore.sql but i’m having difficulty with the logic as it’s parsing the files.

    Any tips?

    Like

    • Hi Sheldon, I used to call restore gene immediately after taking a full, diff or log back on the primary. It generates a restore script you can store with the backup files, then when you need it you are ready to go. You could use the restore gene parameters to tailor the restore script for your mirror, use the with move and backup path overrides.

      Call restore gene as a SQL agent job step after the back step, use the output file advanced job step property to save the script it generates.

      Hope this helps, best wishes

      Like

  8. Hi Paul,

    This is an excellent initiative , currently i’m trying to automate restores from backups using your PS mechanism , some how the subsequent restore call is advancing and updating the last lsn number in the xml log file but the actual restore is not happening on the db , when i run the command in MGMT studio the error Catching happens ‘Transaction Log File Restore Exclusion – Check Recovery Sequence.’ , but im sure the trn log file im attempting to restore is the correct one, even tried updating the LSN value in log.xml but it cannot proceed, when i attempted the restore command in MGMT (with out using your solution it works (this is what i mean :’RESTORE LOG [AdventureWorks2014_Test] FROM DISK = N’\\SERVER1\e$\Backup\AdventureWorks2014_Test\AdventureWorks2014_Test_backup_2016_06_04_184001_5583979.trn” )) , can you please advise how to trouble shoot this situation. Thanks,
    Kiran

    Like

    • Hi Kiran, are the with recovery or standby parameters set in the call to restore gene? The PoSh script is very particular about parameters, below is a test I’ve just run to restore all backups of a database to new database on the same server:

      Set-Location -Path “C:\RestoreGene”

      .\ps_RestoreGene.ps1 `
      -RestoreLog “C:\RestoreGene\log.xml” `
      -PrimaryServer “PB20” `
      -StandbyServer “PB20” `
      -DBName “workspace” `
      -TargetDBName “workspace_restore” `
      -WithReplace “1” `
      -WithMoveDataFiles $null `
      -WithMoveLogFile $null `
      -FromFileFullUNC $null `
      -FromFileDiffUNC $null `
      -FromFileLogUNC $null `
      -StopAt $null `
      -StandBy “1” `
      -WithRecovery “0” `
      -WithCHECKDB “0” `
      -LogShippingInitialize “1” `
      -Log_Reference “Posh Driver Test” `
      -KillConnections “1” `
      -ConsoleFeedback “1” `
      -BlobCredential “Paul” `
      -RestoreScriptReplaceThis “C:\SQL\Data\” `
      -RestoreScriptWithThis “C:\SQL2\Data\” `
      -DebugMode “0”

      Take a new log backup of the primary database then run again with init = 0

      Set-Location -Path “C:\RestoreGene”

      .\ps_RestoreGene.ps1 `
      -RestoreLog “C:\RestoreGene\log.xml” `
      -PrimaryServer “PB20” `
      -StandbyServer “PB20” `
      -DBName “workspace” `
      -TargetDBName “workspace_restore” `
      -WithReplace “1” `
      -WithMoveDataFiles $null `
      -WithMoveLogFile $null `
      -FromFileFullUNC $null `
      -FromFileDiffUNC $null `
      -FromFileLogUNC $null `
      -StopAt $null `
      -StandBy “1” `
      -WithRecovery “0” `
      -WithCHECKDB “0” `
      -LogShippingInitialize “0” `
      -Log_Reference “Posh Driver Test” `
      -KillConnections “1” `
      -ConsoleFeedback “1” `
      -BlobCredential “Paul” `
      -RestoreScriptReplaceThis “C:\SQL\Data\” `
      -RestoreScriptWithThis “C:\SQL2\Data\” `
      -DebugMode “0”

      Best wishes
      Paul

      Like

      • Excellent, thanks Paul, Worked like a charm ! below was what i had, do you thing the order matters or I was not having WithMoveData/LogFiles parameter or -Standby in the subsequent restore call ?Appreciate your support.

        C:\PS\ps_RestoreGene.ps1 -LogShippingInitialize 1 `
        -DBName AdventureWorks2014_Test `
        -TargetDB AdventureWorks2014_Test `
        -WithMoveDataFiles D:\Data\ `
        -WithMoveLogFile D:\Log `
        -WithCheckDB 0 `
        -PrimaryServer SERVER1`
        -StandbyServer SERVER2`
        -FromFileFullUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `
        -RestoreLog C:\PS\Log_ADVENTUREWORKS_SERVER2.xml `
        -WithReplace “1” `
        -WithRecovery 0 `
        -StandBy “1” `
        -Log_Reference “PoSh Example 1” `
        -KillConnections 1

        C:\PS\ps_RestoreGene.ps1 -LogShippingInitialize 0 `
        -DBName AdventureWorks2014_Test `
        -TargetDB AdventureWorks2014_Test `
        -WithCheckDB 0 `
        -PrimaryServer PER3SVR80 `
        -StandbyServer PER3SVR31 `
        -FromFileFullUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `
        -RestoreLog C:\PS\Log_ADVENTUREWORKS_SERVER2.xml `
        -WithReplace “1” `
        -WithRecovery 0 `
        -Log_Reference “PoSh Example 2” `
        -KillConnections 1 `
        -ConsoleFeedback 1

        Like

        • Hi Kiran,
          I think this was the probably the most significant issue, -LogShippingInitialize “0” . It defaults to 1 so the second call was attempting to start again from a full backup. With it set to 0 it checks the xml log file for last lsn restored the msdb backup history for the next files to restore.
          Thanks for the feedback and best wishes
          Paul

          Like

          • Thanks for a quick response, Paul. I just posted the issue i’m having with consecutive restores, not sure how to resolve this , please can you advise.I’m not sure if you are comfortable in seeing so many error posts on the blog, may I just email you these queries from now on ? It is very important for me to resolve this issue on this weekend , as I’m currently designing a automated solution to create a QA server using our current backup strategy with out any impact on Production servers , also im going to extend this solution to obfuscate the sensitive data for compliance purpose.It seems like Im getting there but just this error is pulling me back, hope you will be able to assist. Thanks for your support.Here is the post copy. Thanks. Hi Paul, I have scheduled a Job to Sync Secondary with Primary once every 3 min using the ps call, as there is a log backup scheduled on Primary every 2 min, Sync was successful for first few consecutive log restores but from later onward the LSN issue is repeating , the actual LSN differs with last LSN in log.xml, noticed that the last successful olg restore is before the _UNDO.Bak file i.e., AdventureWorks2014_Test_backup_2016_06_05_174401_7542278.trn 5/06/2016 5:44 PM. can you please advise why this happens, and how can i get over this issue, also is there a way to report any error and terminate the command on any such conflict , i’m guessing the parameter _Debug, -ErrorAction etc can help. Appreciate your support. Below is the command Used for Subsequent log restores : PS C:\restoregene> .\ps_RestoreGene.ps1 `-RestoreLog “C:\RestoreGene\log.xml” `-PrimaryServer “SERVER1” `-StandbyServer “SERVER2” `-DBName “AdventureWorks2014_Test” `-TargetDBName “AdventureWorks2014_Test_Restore” `-WithReplace “1” `-WithMoveDataFiles D:\Data\ `-WithMoveLogFile D:\Log `-FromFileFullUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `-FromFileDiffUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `-FromFileLogUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `-StopAt $null `-StandBy “1” `-WithRecovery “0” `-WithCHECKDB “0” `-LogShippingInitialize “0” `-Log_Reference “Posh Driver Test” `-KillConnections “1” `-ConsoleFeedback “1”SERVER1 Below is the XML log file : –This last LSN is a way ahead of the last restored LSN Below is the restore sequence happened on the database Date modified AdventureWorks2014_Test_backup_2016_06_05_181401_9293017.trn 5/06/2016 6:14 PMAdventureWorks2014_Test_backup_2016_06_05_181201_9439599.trn 5/06/2016 6:12 PMAdventureWorks2014_Test_backup_2016_06_05_181001_9596279.trn 5/06/2016 6:10 PMAdventureWorks2014_Test_backup_2016_06_05_180801_8852748.trn 5/06/2016 6:08 PMAdventureWorks2014_Test_backup_2016_06_05_180601_9099432.trn 5/06/2016 6:06 PMAdventureWorks2014_Test_backup_2016_06_05_180401_8996005.trn 5/06/2016 6:04 PMAdventureWorks2014_Test_backup_2016_06_05_180201_9732912.trn 5/06/2016 6:02 PMAdventureWorks2014_Test_backup_2016_06_05_180001_8690150.trn 5/06/2016 6:00 PMAdventureWorks2014_Test_backup_2016_06_05_175801_8905826.trn 5/06/2016 5:58 PMAdventureWorks2014_Test_backup_2016_06_05_175601_9252474.trn 5/06/2016 5:56 PMAdventureWorks2014_Test_backup_2016_06_05_175401_8169214.trn 5/06/2016 5:54 PMAdventureWorks2014_Test_backup_2016_06_05_175201_8205659.trn 5/06/2016 5:52 PMAdventureWorks2014_Test_backup_2016_06_05_175001_8162376.trn 5/06/2016 5:50 PMAdventureWorks2014_Test_backup_2016_06_05_174801_7479031.trn 5/06/2016 5:48 PMAdventureWorks2014_Test_backup_2016_06_05_174601_7875529.trn 5/06/2016 5:46 PMAdventureWorks2014 Tt_ROLLBACK_UNDO.bak 5/06/2016 5:45 PMAdventureWorks2014_Test_backup_2016_06_05_174401_7542278.trn 5/06/2016 5:44 PMAdventureWorks2014_Test_backup_2016_06_05_174201_7578826.trn 5/06/2016 5:42 PMAdventureWorks2014_Test_backup_2016_06_05_174001_7385355.trn 5/06/2016 5:40 PMAdventureWorks2014_Test_backup_2016_06_05_173801_7461982.trn 5/06/2016 5:38 PMAdventureWorks2014_Test_backup_2016_06_05_173601_7128572.trn 5/06/2016 5:36 PMAdventureWorks2014_Test_backup_2016_06_05_173401_7455114.trn 5/06/2016 5:34 PMAdventureWorks2014_Test_backup_2016_06_05_173201_6512184.trn 5/06/2016 5:32 PMAdventureWorks2014_Test_backup_2016_06_05_173001_7458608.trn 5/06/2016 5:30 PMAdventureWorks2014_Test_backup_2016_06_05_171923_3814591.bak 5/06/2016 5:19 PM

            Regards,Kiran

            Date: Sun, 5 Jun 2016 10:23:35 +0000 To: jgkiran@live.com

            Like

  9. Hi Paul,

    I have scheduled a Job to Sync Secondary with Primary once every 3 min using the ps call, as there is a log backup scheduled on Primary every 2 min, Sync was successful for first few consecutive log restores but from later onward the LSN issue is repeating , the actual LSN differs with last LSN in log.xml, noticed that the last successful olg restore is before the _UNDO.Bak file i.e., AdventureWorks2014_Test_backup_2016_06_05_174401_7542278.trn 5/06/2016 5:44 PM.

    can you please advise why this happens, and how can i get over this issue, also is there a way to report any error and terminate the command on any such conflict , i’m guessing the parameter _Debug, -ErrorAction etc can help. Appreciate your support.

    Below is the command Used for Subsequent log restores :

    PS C:\restoregene> .\ps_RestoreGene.ps1 `
    -RestoreLog “C:\RestoreGene\log.xml” `
    -PrimaryServer “SERVER1” `
    -StandbyServer “SERVER2” `
    -DBName “AdventureWorks2014_Test” `
    -TargetDBName “AdventureWorks2014_Test_Restore” `
    -WithReplace “1” `
    -WithMoveDataFiles D:\Data\ `
    -WithMoveLogFile D:\Log `
    -FromFileFullUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `
    -FromFileDiffUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `
    -FromFileLogUNC ‘\\SERVER1\e$\Backup\AdventureWorks2014_Test\’ `
    -StopAt $null `
    -StandBy “1” `
    -WithRecovery “0” `
    -WithCHECKDB “0” `
    -LogShippingInitialize “0” `
    -Log_Reference “Posh Driver Test” `
    -KillConnections “1” `
    -ConsoleFeedback “1”
    SERVER1

    Below is the XML log file :

    –This last LSN is way ahead of the last restored LSN

    Below is the restore sequence happened on the database :

    destination_database_name restore_date type first_lsn last_lsn checkpoint_lsn
    AdventureWorks2014_Test_Restore 2016-06-05 17:45:08.610 L 50000000008100001 50000000008600001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:42:20.810 L 50000000007600001 50000000008100001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:42:08.327 L 50000000007100001 50000000007600001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:39:15.780 L 50000000006600001 50000000007100001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:39:13.540 L 50000000006100001 50000000006600001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:39:11.290 L 50000000005600001 50000000006100001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:39:08.890 L 50000000004800001 50000000005600001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:31:23.960 L 49000000137500001 50000000004800001 50000000001600022
    AdventureWorks2014_Test_Restore 2016-06-05 17:29:03.097 D 50000000001600022 50000000002700001 50000000001600022

    Below are the log backups from Primary server , please notice the last one succesfully restored was one before Tt_ROLLBACK_UNDO.bak :

    Name Date modified

    AdventureWorks2014_Test_backup_2016_06_05_181401_9293017.trn 5/06/2016 6:14 PM
    AdventureWorks2014_Test_backup_2016_06_05_181201_9439599.trn 5/06/2016 6:12 PM
    AdventureWorks2014_Test_backup_2016_06_05_181001_9596279.trn 5/06/2016 6:10 PM
    AdventureWorks2014_Test_backup_2016_06_05_180801_8852748.trn 5/06/2016 6:08 PM
    AdventureWorks2014_Test_backup_2016_06_05_180601_9099432.trn 5/06/2016 6:06 PM
    AdventureWorks2014_Test_backup_2016_06_05_180401_8996005.trn 5/06/2016 6:04 PM
    AdventureWorks2014_Test_backup_2016_06_05_180201_9732912.trn 5/06/2016 6:02 PM
    AdventureWorks2014_Test_backup_2016_06_05_180001_8690150.trn 5/06/2016 6:00 PM
    AdventureWorks2014_Test_backup_2016_06_05_175801_8905826.trn 5/06/2016 5:58 PM
    AdventureWorks2014_Test_backup_2016_06_05_175601_9252474.trn 5/06/2016 5:56 PM
    AdventureWorks2014_Test_backup_2016_06_05_175401_8169214.trn 5/06/2016 5:54 PM
    AdventureWorks2014_Test_backup_2016_06_05_175201_8205659.trn 5/06/2016 5:52 PM
    AdventureWorks2014_Test_backup_2016_06_05_175001_8162376.trn 5/06/2016 5:50 PM
    AdventureWorks2014_Test_backup_2016_06_05_174801_7479031.trn 5/06/2016 5:48 PM
    AdventureWorks2014_Test_backup_2016_06_05_174601_7875529.trn 5/06/2016 5:46 PM
    AdventureWorks2014 Tt_ROLLBACK_UNDO.bak 5/06/2016 5:45 PM
    AdventureWorks2014_Test_backup_2016_06_05_174401_7542278.trn 5/06/2016 5:44 PM
    AdventureWorks2014_Test_backup_2016_06_05_174201_7578826.trn 5/06/2016 5:42 PM
    AdventureWorks2014_Test_backup_2016_06_05_174001_7385355.trn 5/06/2016 5:40 PM
    AdventureWorks2014_Test_backup_2016_06_05_173801_7461982.trn 5/06/2016 5:38 PM
    AdventureWorks2014_Test_backup_2016_06_05_173601_7128572.trn 5/06/2016 5:36 PM
    AdventureWorks2014_Test_backup_2016_06_05_173401_7455114.trn 5/06/2016 5:34 PM
    AdventureWorks2014_Test_backup_2016_06_05_173201_6512184.trn 5/06/2016 5:32 PM
    AdventureWorks2014_Test_backup_2016_06_05_173001_7458608.trn 5/06/2016 5:30 PM
    AdventureWorks2014_Test_backup_2016_06_05_171923_3814591.bak 5/06/2016 5:19 PM

    Like

  10. Hi Kiran,
    Are the backup jobs and restore jobs overlapping? Are you running multiple jobs concurrently that share the same xml restore log file? I’m try to think what might cause this behaviour, have you captured the T-SQL failure message?
    Thanks
    Paul

    Like

  11. HI Paul, The log is not being used by any thing else other than the restore script, the backup job is independent and it is on another server.There are no errors in sql , the PS script is assuming that it successfully restored the previous consecutive log file hence the LSN value keep getting incremented in the last LogShippingLastLSN Description , but this is not true from msdb, please see the actual last LSN that msdb is expecting in my previous post, I need to make the script break and report a error to see the actual cause, please advise. Thanks.

    Below is the XML Log file as of now :

    Like

    • Hi Kiran, is something connecting to and reading the restored database? If a reader reconnects quickly with a standby DB, maybe there is a failure to restore there but it is logged as complete, a bug. Try running it with standby = 0 and with no recovery.
      Best wishes
      Paul

      Like

  12. HI Paul,

    Probably timing was the issue, thanks for the hint, i have changed the Sync schedule to every 7 min where as the log backup happens every 2 min. So far the sync is happening with out any issues , i will monitor this for 24 hrs..hopefully all goes well. Thanks for your support, much appreciate it.

    Regards,
    Kiran

    Like

  13. Version 5.1 released today includes the following changes:

    1. If Target database name is supplied (when restoring to a database name different to the back up), a string is appended to the end of the filenames in the format : c:\sql\data\name.mdf. @TargetDatabase_ + _RestoreGene_TargetDBRenamed. This makes the filenames unique so the restore can be on the same instance using the same folders.

    2. If a backup was taken to Azure url storage, the restore script constructed includes ‘FROM URL’ rather than ‘FROM FILE’. The credentials used to take the backup can be supplied as a new parameter.

    3. Two new parameters – one for a string scan and one for replace that can optionally be applied to the restore script.

    4. The PowerShell driver script now defaults ‘With Standby’ to false.

    Thanks for the feedback, all of these changes were suggested recently.
    Paul

    Like

Trackbacks

  1. Generating a restore script | SQL Studies

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

%d bloggers like this: