Automating Database Restores

Question

Automating Database Restores

Divine Flame

SSCoach

Points: 15943
More actions
April 20, 2014 at 3:18 pm

#288145

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

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

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

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: 234013 More actions · Answer 2

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

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Divine Flame SSCoach Points: 15943 More actions · Answer 3

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: 8676 More actions · Answer 4

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 More actions · Answer 5

This has already been covered under the below link:-

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

mikeg 31960 Old Hand Points: 334 More actions · Answer 6

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 More actions · Answer 7

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

Divine Flame SSCoach Points: 15943 More actions · Answer 8

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: 15943 More actions · Answer 9

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 More actions · Answer 10

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: 15943 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

How does this script handle multiple files and filegroups?

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

jswong05 Hall of Fame Points: 3553 More actions · Answer 14

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
😛