July 5, 2016 at 2:52 pm
Hi all, I have been looking into ways to restore multiple user databases from the .bak file. I know you can do this one at a time in SSMS but I have over 1000 dbs that I do not want to restore this way.
I have come across a few scripts that creates a stored procedure and when executed it will script out all the attached user databases to be restored. This is ok but I want to know how I can restore all theses databases incase of a complete disaster and I don't have access to run the stored procedure.
I am looking into creating my own script but to be honest I am not really sure were to begin as I am fairly new to T-SQL.
Basically if I can make a script to script out the following for each database that would be ideal.
RESTORE DATABASE [database] FROM DISK = N'D:\Path\to\backup\database.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
I am looking for any advice or suggestions on where to look to start scripting this out.
July 5, 2016 at 3:26 pm
You really do need a stored proc, given how complex the restore process could be, with a full backup, maybe differential, maybe log file(s).
I have a special user/utility db that I restore first by hand, if necessary, that has all the recovery scripts in it, including the scripts that gen the recovery for other dbs. My scripts assume that master and msdb have already been restored, so that the user db names and backup history are available to use to gen the recovery scripts.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 5, 2016 at 3:39 pm
See if the attached will get you started.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 5, 2016 at 4:31 pm
Thanks ScottPletcher, I will definitely set up a db that I can use to store all this.
Also thanks TheSQLGuru, I have taken a stab at the script you provided and it seems to do exactly what I was looking for 😀
July 6, 2016 at 12:57 am
I steped away from tsql scripting for this purpose.
I use a powershell script/module to assemble and restore databases from whatever sqlserver source to a given sql server target.
You can find my SQLSaturday presentation over here.
In any case, it is always a good thing if you have worked out some kind of standards up front.
e.g. backup location / safe zone location / naming conventions / ...
And of course [highlight="#f6ff33"]TEST YOUR BACKUP SYSTEM[/highlight] by performing a FULL RECOVERY to a given point in time onto an alternate system
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 6, 2016 at 9:07 am
Thanks ALZDBA, I have never really used powershell but I have browsed over the demo and it maybe something I will look into.
As for the T-SQL script it worked perfectly for my 2012 SQL Server but I noticed the HEAEDRONLY table was missing a few columns for SQL server 2014 so if any one cares here are the 3 missing columns that need to be created in the bdev table.
KeyAlgorithm nvarchar(32),
EncryptorThumbprint varbinary(20),
EncryptorType nvarchar(32)
July 7, 2016 at 11:47 pm
Thank you for the feedback.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply