restore multi database

Question

restore multi database

abdalah.mehdoini

Ten Centuries

Points: 1012
More actions
December 20, 2018 at 5:43 am

#382496

Hello everyone
I need to have a script that allows me to restore multi backup SQL server
the problem is that it must also contain the RESTORE FILELISTONLY command to retrieve the name of the file mdf and ldf
I searched a lot on the internet but I had some examples
thank you for your help

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Heh... you do have to ask the right question when using yabingooglehoo. 😀 Try the following search ...
https://www.google.com/search?q=script+to+find+and+restore+latest+backups+sql+server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

abdalah.mehdoini - Thursday, December 20, 2018 5:43 AM
Hello everyone
I need to have a script that allows me to restore multi backup SQL server
the problem is that it must also contain the RESTORE FILELISTONLY command to retrieve the name of the file mdf and ldf
I searched a lot on the internet but I had some examples
thank you for your help

Are you moving the files to a different location when restoring these databases? If so - how are you determining where to move those files and is that consistent across all databases to be restored?

For example - what would you do if the database being restored has 4 data files across 4 different mount points on the source system - with the transaction log on a separate drive - and full text catalogs on yet another location? What if some database files are located in different folders - DB1 is located on {drive}:\MSSQL\Data\ and DB2 is located on {drive}:\Other\Data\?

The simplest way to do this is to insure that you have the exact same drive layout on the destination - in that case you would not have any issues with the restore commands because you don't need to include the MOVE as RESTORE would use the original locations.

Now - if this is being setup as a repeatable process...I would recommend building some tables to contain this information and only restoring those databases that are defined in those tables. One table would identify the database and source system, another would identify the MDF files and destination location/name with a third table identifying the LDF file(s) location and name, etc... and other parameters if needed.

You then generate the restore script using the data in the tables...if something changes on the source system you have to update your tables but that is much easier than trying to account for every possible condition in your code, especially when the source system adds a new file to a different drive/mount point that you don't have available on the destination server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs