Restoring 100s of user databases..

Question

Restoring 100s of user databases..

matthewful3

SSC Veteran

Points: 265
More actions
July 5, 2016 at 2:52 pm

#327923

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

See if the attached will get you started.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

matthewful3 SSC Veteran Points: 265 More actions · Answer 3

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 😀

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

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

matthewful3 SSC Veteran Points: 265 More actions · Answer 5

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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Thank you for the feedback.