auto-generate-sql-server-restore-script-from-backup-files (bak + trn) -in-a-directory for multi databases

Question

auto-generate-sql-server-restore-script-from-backup-files (bak + trn) -in-a-directory for multi databases

abdalah.mehdoini

Ten Centuries

Points: 1015
More actions
February 3, 2019 at 2:15 pm

#381340

Hello everyone
I am looking to generate a restoration script for hundreds of databases with their backup Full and log file Trn
I found it on the internet but it is applied qe for a single database
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
thank you for your help

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

You mean this script in the same set of articles?
https://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/

Sue_H SSC Guru Points: 90860 More actions · Answer 2

abdalah.mehdoini - Sunday, February 3, 2019 2:15 PM
Hello everyone
I am looking to generate a restoration script for hundreds of databases with their backup Full and log file Trn
I found it on the internet but it is applied qe for a single database
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
thank you for your help

All those scripts and the related ones there are for a single database. Take a look at this article and stored procedure that can do all user databases - it might meet your needs:
T-SQL Restore Script Generator

Sue

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Heh... and all of those scripts appear to be using MSDB, which does you no good at all if the original server(s) where the backups were taken from isn't available.

--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)

abdalah.mehdoini Ten Centuries Points: 1015 More actions · Answer 4

Who has an idea or feedback on the sp_RestoreGene function

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

abdalah.mehdoini - Monday, February 4, 2019 6:27 AM
Who has an idea or feedback on the sp_RestoreGene function

What's the link for it? (I'm not going to click on every link in this thread to try to find it). And, considering the sp_ prefix, it's probably not a function.

--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)

abdalah.mehdoini Ten Centuries Points: 1015 More actions · Answer 6

Jeff Moden - Monday, February 4, 2019 6:31 AM
abdalah.mehdoini - Monday, February 4, 2019 6:27 AM
Who has an idea or feedback on the sp_RestoreGene function
What's the link for it? (I'm not going to click on every link in this thread to try to find it). And, considering the sp_ prefix, it's probably not a function.

http://www.sqlservercentral.com/articles/Restore/111917/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

abdalah.mehdoini - Monday, February 4, 2019 9:31 AM
Jeff Moden - Monday, February 4, 2019 6:31 AM
abdalah.mehdoini - Monday, February 4, 2019 6:27 AM
Who has an idea or feedback on the sp_RestoreGene function
What's the link for it? (I'm not going to click on every link in this thread to try to find it). And, considering the sp_ prefix, it's probably not a function.
http://www.sqlservercentral.com/articles/Restore/111917/

Like I said earlier in this thread, if you're restoring to the same server and the MSDB database is intact as it was before the restore became a necessity, that might work fine because the msdb.dbo.backupset table is used (as is the backupmediafamily table) in that. He also says as much in the Flower Box Comments in the code. If all you have is a pile of backup files in a folder, it's not going to do a thing for you.

Other than that, it looks pretty good from a 60,000 foot level. I've not done a deep dive on it.

Of course, if you have MSDB available for the server you're doing the backups for, you can just as easily generate the code for a point in time recovery using the GUID. The only time you'd need the code is if you needed to automate the restore of a database every night.

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

The real key is... why do you need to "generate a restoration script for hundreds of databases with their backup Full and log file"??? What's the end game here??? And, how often do you need to do it? Using a script like this to do the job may be absolutely the wrong thing to do depending on what the ultimate goal is. For example, if you're trying to copy all the databases from a prod system to a lesser environment, a "SAN Snapshot" would do the whole shebang in just seconds or possibly less.

--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)