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: 1019
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 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63551 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: 1004556 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: 1019 More actions · Answer 4

Who has an idea or feedback on the sp_RestoreGene function

Jeff Moden SSC Guru Points: 1004556 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: 1019 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: 1004556 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: 1004556 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)

Srinivas Merugu SSC Enthusiast Points: 133 More actions · Answer 9

Find Below/attached script to restore multiple backup files in single shot as per existing Database data and log file location.

Use master

go

DECLARE @date CHAR(8)

SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @path VARCHAR(125)

SET @path = '\\TestServer\Backup' -- Provide the backup path here

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )

AS ( SELECT DISTINCT

DB_NAME(database_id) ,

STUFF((SELECT ' ' + CHAR(13)+', MOVE ''' + name + ''''

+ CASE Type

WHEN 0 THEN ' TO ''M:\SQLData\' -- Provide SQL Data File Path here

ELSE ' TO ''N:\SQLLogs\' ---- Provide SQL Log File Path here

END

+ REVERSE(LEFT(REVERSE(physical_name),

CHARINDEX('\',

REVERSE(physical_name),

1) - 1)) + ''''

FROM sys.master_files sm1

WHERE sm1.database_id = sm2.database_ID

FOR XML PATH('') ,

TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd

FROM sys.master_files sm2

)

SELECT 'Alter database ['+ name+'] set Single_User with rollback immediate;'+

' RESTORE DATABASE ['+ name + '] FROM DISK = ''' + @path + '' + name + '.bak'' WITH FILE = 1 '+movecmdCTE.MoveCmd

+', RECOVERY, REPLACE, STATS=5,buffercount=600;'+

'Alter database ['+ name+'] set Multi_User ;'

FROM sys.databases d

INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename

WHERE d.name not in ('master','model','msdb','tempdb','distribution') --LIKE '%DatabaseName%'

GO

This reply was modified 1 month ago by Srinivas Merugu.

Attachments:

You must be logged in to view attached files.

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

Attachments:

Cookies on SQLServerCentral