SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating Database Restores


Automating Database Restores

Author
Message
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 2816
Comments posted to this topic are about the item Automating Database Restores


Sujeet Singh
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 2816
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
Perry Whittle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27437 Visits: 17351
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
Divine Flame
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 2816
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
Kurt W. Zimmerman
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 1398
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
kartar67 77850
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 160
This has already been covered under the below link:-
http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30962/
mikeg 31960
mikeg 31960
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 79
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
JCAirey1
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 77
Cool script. What happens if @AutoExecute AND @IncludeSystemDatabases are both equal to 1? Smile
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 2816
JCAirey1 (4/21/2014)
Cool script. What happens if @AutoExecute AND @IncludeSystemDatabases are both equal to 1? Smile


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
Divine Flame
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2781 Visits: 2816
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search