February 3, 2019 at 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
February 3, 2019 at 2:50 pm
You mean this script in the same set of articles?
https://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/
February 3, 2019 at 3:05 pm
abdalah.mehdoini - Sunday, February 3, 2019 2:15 PMHello 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
February 3, 2019 at 4:39 pm
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
February 4, 2019 at 6:27 am
Who has an idea or feedback on the sp_RestoreGene function
February 4, 2019 at 6:31 am
abdalah.mehdoini - Monday, February 4, 2019 6:27 AMWho has an idea or feedback on thesp_RestoreGenefunction
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
February 4, 2019 at 9:31 am
Jeff Moden - Monday, February 4, 2019 6:31 AMabdalah.mehdoini - Monday, February 4, 2019 6:27 AMWho has an idea or feedback on thesp_RestoreGenefunctionWhat'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.
February 4, 2019 at 6:43 pm
abdalah.mehdoini - Monday, February 4, 2019 9:31 AMJeff Moden - Monday, February 4, 2019 6:31 AMabdalah.mehdoini - Monday, February 4, 2019 6:27 AMWho has an idea or feedback on thesp_RestoreGenefunctionWhat'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.
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
February 4, 2019 at 6:54 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply