restore

Question

restore

Tara-1044200

SSCoach

Points: 15951
More actions
July 3, 2010 at 7:11 am

#157053

I am trying to restore all my production databases onto dev server which i will be doing very often, is there a way to get this done through a stored proc and all my databases have atleast 50 file groups.

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Why so many filegroups?

There's no procedure that let's you restore without specifying things like files. I suggest that you write a script that generates the restore statement for each database before you take the backups. Use sys.master_files, sys.databases and sys.filegroups. Combined they should have the info you need.

Then you can save the restore scripts to file, and load them up and run any time it's necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55553 More actions · Answer 2

If you are not familiar enough with coding it in T-SQL, you can use the GUI to setup the restore and select all the files & locations. Before hitting the final "OK", select "Script to new Query Window". That will generate the SQL script you need and you can then save and/or modify it as you need.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

If you can make sure the directory structures in dev and prod are identical and you don't change the logical file names you can restore with only the replace clause.

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

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 4

I would second homebrew01's method. That is a good way to get this saved as a job/script and learn something.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

In my company, we just developed a structure to do some admin tasks like setting up mirroring, log shipping, Backup and restore DB etc. we maintain all data path , log path, endpoint values, server name, IP address in a meta data table and fetch/use it in generic SP/script which does all admin tasks.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)