Avatar of dbaSQL
dbaSQL
Flag for United States of America asked on

Automate this RESTORE statement to be executed in a procedure

I use the script below generate the RESTORE statement for a FULL backup and all subsequent transaction log backups.  It is something that I found here:  https://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/


It is very helpful, but right now I run it manually.  I connect to SSMS query and execute the statement below.  Then I cut/paste the results into another window and run them in order to perform the restore.


I need to automate it.  At the time that it is run, there is an average of 30-40 trn log files. but that doesn't matter.  I cannot hard code it to expect any number of files.  I just want to call the below statement within a procedure, output the results into a runnable format, and then be able to execute the RESTORE.  I am not sure of the best way to do this because of the multiple lines in the resultset, and was hoping to find Expert assistance.  This is very important and I appreciate any input.


DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = 'XXX' 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
          AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

Open in new window

* Microsoft SQL Server 2019Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
David Todd

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dbaSQL

ASKER
Wow.  I am very surprised I did not know about that procedure already.  I am a very big Ola and Ozar user.   Regardless, it may work.  To test, I have created a full backup with four transaction log backups.  My goal is to simply restore the full followed by all 4 trns and leave the database recovered/usable.  

This is my statement:

EXEC dbo.sp_DatabaseRestore 
  @Database = 'xxx, 
  @BackupPathFull = 'C:\MSSQL\2017\Backup\xxx\', 
  @BackupPathLog = 'C:\MSSQL\2017\Backup\xxx\', 
  @ContinueLogs = 0, 
  @RunRecovery = 1;

Open in new window


It failed and I have pasted the output below.  I do use Ola, and I have confirmed the full bak and 4 trns all exist at the backup path.

Interesting that the output suggests it is starting with the 4th log backup, rather than the full bak.

Do you know what I am missing?



Getting default data drive for @MoveDataDrive
Getting default log drive for @MoveLogDrive
Setting default data drive for @MoveFilestreamDrive
Setting default data drive for @MoveFullTextCatalogDrive
@MoveFiles = 1, adjusting paths
@ExistingDBAction 0 so do nothing
@ContinueLogs set to 0
Date and time: 2021-11-23 14:15:51
Database context: [master]
Command: RESTORE DATABASE [xxx] FROM DISK = 'C:\MSSQL\2017\Backup\xxx\xxx_log4.bak' WITH NORECOVERY, REPLACE
Msg 3117, Level 16, State 1, Line 79
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 79
RESTORE DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:00:00
Date and time: 2021-11-23 14:15:51
      
Date and time: 2021-11-23 14:15:51
Database context: [master]
Command: RESTORE DATABASE [xxx] WITH RECOVERY

Msg 3153, Level 16, State 2, Line 79
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 79
RESTORE DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:00:00
Date and time: 2021-11-23 14:15:51
      

Completion time: 2021-11-23T14:15:51.9926920-06:00
dbaSQL

ASKER
Yes.  I think I am receiving this message because the full was not restored first, nor were the 3 preceding tran log backups:

The log or differential backup cannot be restored because no files are ready to rollforward.
David Todd

Hi,

There is a missing quote on the end of the database name that maybe you edited out by mistake.

If you have the space, why don't you restore alongside your existing database and use the @restoreDatabaseName parameter?

https://www.brentozar.com/archive/2017/03/databaserestore-open-source-database-restore-stored-procedure/

All I can think of at this stage.

Kind regards
  David

PS Looks like you got things installed correctly - sp_DatabaseRestore needs to be in the same database as Ola's procedures. I typically put Ola's in _dba, and Brent's First Responders kit in master.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dbaSQL

ASKER
I got it!  This is perfect!  Again, I am really surprised I did not know about this, but it is so perfect.  Thank you very much!
dbaSQL

ASKER
Retract.  I closed hastily and I really do hope that David or another Expert can still help.  I am attempting to run this against a UNC, and it fails with the error below.   Any ideas?


Msg 3201, Level 16, State 2, Line 43
Cannot open backup device '\\backupshare\backups\cluster1$clst1\dbname\FULL_COPY_ONLY\temp'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 43
RESTORE FILELIST is terminating abnormally.
Msg 3201, Level 16, State 2, Line 43
Cannot open backup device '\\backupshare\backups\cluster1$clst1\dbname\FULL_COPY_ONLY\temp'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 43
RESTORE HEADERONLY is terminating abnormally.
Msg 50000, Level 16, State 1, Procedure dbo.sp_DatabaseRestore, Line 715 [Batch Start Line 42]
Backupfile "temp" does not match @Database parameter "xxx"




This is my statement:
EXEC dbo.sp_DatabaseRestore
  @Database = 'xxx',
  @BackupPathFull = '\\backupshare\backups\cluster1$clst1\dbname\FULL_COPY_ONLY\',
  @BackupPathLog = '\\backupshare\backups\cluster1$clst1\dbname\LOG\',
  @ContinueLogs = 0,
  @RunRecovery = 1,
  @Execute = 'N';
David Todd

Hi

The SQL Service account requires access to those shares and paths.

Kind regards
  David
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question