Automating Database Restores

  • Divine Flame

    SSCoach

    Points: 15941

    Comments posted to this topic are about the item Automating Database Restores


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    Due to some formatting issues in the article, RESTORE HEADERONLY & RESTORE FILELISTONLY commands are not visible in full.

    Complete commands are given below:

    Restore HeaderOnly:

    RESTORE HEADERONLY FROM DISK = 'D:\SQLTestLab\Backups\AdventureWorks_FULL_2014_04_03_18_11_23.BAK'

    Restore FileListOnly:

    RESTORE FILELISTONLY FROM DISK = 'D:\SQLTestLab\Backups\AdventureWorks_FULL_2014_04_03_18_11_23.BAK'


    Sujeet Singh

  • Perry Whittle

    SSC Guru

    Points: 233794

    Why not just use log shipping, seems it would be perfect for this scenario.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Divine Flame

    SSCoach

    Points: 15941

    Perry Whittle (4/21/2014)


    Why not just use log shipping, seems it would be perfect for this scenario.

    Thanks for your comment Perry.

    You are absolutely right. Log shipping is also a very good option.

    However, in our case we went ahead with this script because,

    1. Using this method, we were able to refresh the other server without doing any major change\configuration on Production.

    2. There might be few databases which were in Simple Recovery Model & hence log-shipping was not a viable option for them.

    3. Using this script required less maintenance.


    Sujeet Singh

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8674

    I have found that an automated means of restoring a database vs. using various forms of database replication such as log shipping or database mirroring really comes down to what the resultant database is going to be used for.

    For log shipping or database mirroring, I've deployed this type of solution for a High Availability solution. I've developed a similar script to copy & restore a production database to a test/UAT environment for just that. In my situation after the database has been restored there are some system related information that needs to be updated in order for the newly refreshed database can be utilized in the test/UAT platform.

    I like the Author's depth of his restore script and may find some use for it. None the less this type of processing will become handy sooner than later.

    Thanks for sharing.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • kartar67 77850

    SSC Rookie

    Points: 38

    This has already been covered under the below link:-

    http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30962/

  • mikeg 31960

    Old Hand

    Points: 324

    Thanks for this. Our databases are, by design, in simple recovery mode. So we've implemented not log shipping, but rather "backup shipping" (Clever ...I know). I learned a lot merely reviewing this and am inspired to make a couple improvements to what we already have in place.

  • JCAirey1

    SSC Journeyman

    Points: 81

    Cool script. What happens if @AutoExecute AND @IncludeSystemDatabases are both equal to 1? 🙂

  • Divine Flame

    SSCoach

    Points: 15941

    JCAirey1 (4/21/2014)


    Cool script. What happens if @AutoExecute AND @IncludeSystemDatabases are both equal to 1? 🙂

    Hi JC,

    In case @AutoExecute & @IncludeSystemDatabases both are set to 1, procedure will throw following error:

    "AutoExecute cannot be used with system databases. Set @AutoExecute = 0 to print the restore commands for system databases & then restore them manually OR set @IncludeSystemDatabases = 0 to exclude system databases. "

    I have done this on purpose, so that nobody accidently overwrites any of his\her system databases.

    Thanks for asking.


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    Kurt W. Zimmerman (4/21/2014)


    I have found that an automated means of restoring a database vs. using various forms of database replication such as log shipping or database mirroring really comes down to what the resultant database is going to be used for.

    For log shipping or database mirroring, I've deployed this type of solution for a High Availability solution. I've developed a similar script to copy & restore a production database to a test/UAT environment for just that. In my situation after the database has been restored there are some system related information that needs to be updated in order for the newly refreshed database can be utilized in the test/UAT platform.

    I like the Author's depth of his restore script and may find some use for it. None the less this type of processing will become handy sooner than later.

    Thanks for sharing.

    Kurt

    mikeg 31960 (4/21/2014)


    Thanks for this. Our databases are, by design, in simple recovery mode. So we've implemented not log shipping, but rather "backup shipping" (Clever ...I know). I learned a lot merely reviewing this and am inspired to make a couple improvements to what we already have in place.

    Thanks a lot, Kurt & Mike for your kind words. I am glad you found it useful.


    Sujeet Singh

  • JoseM

    SSC Enthusiast

    Points: 122

    Good script !!! :w00t::w00t:

    The only thing I miss is a parameter to change the destination database name.

    In our case many customer has a mirrored database in the same instance we are restoring data needing another database with the same data for test purposes. We restore production database on demand with another name, change some permissions and truncate some tables.

  • Divine Flame

    SSCoach

    Points: 15941

    JoseM (4/22/2014)


    Good script !!! :w00t::w00t:

    The only thing I miss is a parameter to change the destination database name.

    In our case many customer has a mirrored database in the same instance we are restoring data needing another database with the same data for test purposes. We restore production database on demand with another name, change some permissions and truncate some tables.

    Thanks Josh.

    If you go through the script, it should be very easy for you to change the code to accept the parameter for destination database name as well. Let me know in case you need any help on that.


    Sujeet Singh

  • Anders Pedersen

    SSChampion

    Points: 11410

    JoseM (4/22/2014)


    Good script !!! :w00t::w00t:

    The only thing I miss is a parameter to change the destination database name.

    In our case many customer has a mirrored database in the same instance we are restoring data needing another database with the same data for test purposes. We restore production database on demand with another name, change some permissions and truncate some tables.

    I used similar procedure at my previous job, but with that added capability. Used a table to drive the parameters sent into the procedure. That way I could also alter by day which databases got restored, say for checking backups, if I did not have enough time to check all backups every day.

  • ggoble

    Hall of Fame

    Points: 3337

    How does this script handle multiple files and filegroups?

    Edit: Does this script handle multiple file and file groups?

  • jswong05

    Hall of Fame

    Points: 3503

    http://dbace.us

    Actually on SQLSaturday#308, I will show you how to generate a restore script from MSDB using T-SQL.

    I wrote this and use it for many years on many jobs.

    Two advantages:

    1) the MSDB catalog has LSN info as well.

    2) in case someone did an ad-hoc backup that is stored somewhere else, the restore script generated from backup directory files won't work for broken chain. :-D:-P:w00t:

    Jason

    http://dbace.us

    Jason
    http://dbace.us
    😛

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply