Intro
This blog post is designed to be useful to two different audiences:
- Those implementing Ola Hallengren’s Maintenance Solution for the first time
- Those that have been using it for awhile, but might have a hole in their DR plan – what to restore when a resource goes offline
There’s numerous blogs and presentations out there about Ola’s Maintenance Solution. I don’t feel a strong need to try to replicate that information here. What I am presenting is a custom extension that works out of the box for those of you that have created jobs using the maintenance solution. The scripts will add an additional job step to all DatabaseBackup jobs which will output a continuously updated text file that contains the most current restore scripts for each database in the instance.
Solution
Let’s just get to it then, shall we?
You can download the latest version of my scripts here: https://github.com/jzagelbaum/OlaHallengrenRestoreScript
Please note that there are three requirements for the solution to function correctly:
- The DatabaseBackup jobs must use @LogToTable = ‘Y’
- In order for the generated restore script to be valid, at least one full database backup should be logged to the CommandLog Table
- SQL Server agent should have read / write access to the directory where the restore scripts will be written. The default is the @Directory value.
If you don’t know what @LogToTable, CommandLog, and @Directory are, then please read up on Maintenance Solution before continuing.
Implementation
RestoreCommand Stored Procedure
Use the same database that the Maintenance Solution objects are created in; the default is Master. First create the RestoreCommand s proc. This is the procedure that will be executed each time the DatabaseBackup jobs are run. There are no input parameters for this procedure. It reads the records from CommandLog and outputs the restore commands for each database being backed up on the instance. This includes log, diff, and full based on last completed backup and backup type. You can create the procedure and execute it to view the output as a sample:
Please, please, please do not just create this procedure without implementing the job script step. The whole point of this solution is to have a standby restore script available if your instance / database becomes unusable (and you’re not clustering, etc.). I’ve really tried to make it as easy as possible to add the job step (assuming you’re using the OOTB maintenance solution created jobs). That being said…
Create Restore Script Job Steps
This script accepts two parameter values:
- @DatabaseName, the database where the Maintenance Solution and RestoreCommand objects are located (default of Master)
- @RestoreScriptDir, accepts ‘Backup_Dir’, ‘Error_Log’, or custom defined dir, e.g., ‘C:\’. If the dir is a custom value, the directory must be created prior to running the job.
For the @RestoreScriptDir parameter, the default value ‘Backup_Dir’ places the script in the instance level folder in the directory of the backup files, which is determined by the value of the @Directory parameter. ‘Error_Log’ places the restore script in the same dir as the default location of Maintenance Solution output files.
Make sure SQL Agent has read / write access to any custom directory if used.
After the script is run, you now have an additional job step in all of your DatabaseBackup jobs:
Result
Now, each time a DatabaseBackup job is run, a continuously updated text file containing the latest restore script is written to the appropriate instance folder of the configured directory. Here is a screenshot using the default backup directory:
Parting Thoughts
So, you now have the power to restore your instance at the database level up to the last verified, completed backup. No GUI, no guesswork, and most importantly, no trying to figure out what needs to be restored when you have the least amount of time to think about it.
Hope this helps!
I only had to give SQL Agent access to my backup directory.
Your scripts work like a charm. Thanks!
LikeLiked by 1 person
Thanks for this solution! I am testing it out now on a server that uses differential backups. My current solution for generating a restore script doesn’t have the option for diff’s.
LikeLike
Hope it meets your needs!
LikeLike
So far so good! Thanks again!
LikeLike
Thanks for this, Jared. The script doesn’t seem to account for multiple backup files and only will give a restore command for the first of n files.
LikeLike
Thanks, John. I haven’t had to account for multiple files before. There’s other supported Ola configs that I’m sure I’m missing as well. Please feel free to extend the script to meet your needs.
LikeLike
Thank you! This script was exactly what I needed. The environment I’m working in has Availability Groups, but as long as all replicas back up to the same location, it works. Great script!
LikeLiked by 1 person
[…] https://jaredzagelbaum.wordpress.com/2015/04/16/automated-restore-script-output-for-ola-hallengrens-… […]
LikeLike
Friendly edit to the “Create Restore Scripts Job Step.sql” file, to handle both updating the @RestoreScriptDirValue and for adding these steps to already-modified versions of Ola Hallengren’s scripts (for example, if you already have a step that runs after Ola’s backup job step):
/*Scott Doubleday-Stern 2015-11-04
Replaces:
EXEC msdb.dbo.sp_update_jobstep
@job_id=@jobID
,@step_id = 1
,@on_success_action=3
,@on_fail_action=2
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobid
,@step_name=N’Generate Restore Script’
,@step_id=2
,@cmdexec_success_code=0
,@on_success_action=1
,@on_fail_action=2
,@retry_attempts=0
,@retry_interval=0
,@os_run_priority=0
,@subsystem=N’CmdExec’
,@command=@RestoreCommand
,@database_name=@DatabaseName
,@output_file_name=@RestoreScriptDirValue
,@flags=0
*/
DECLARE @StepID_GenerateRestoreScript INT = -9
DECLARE @StepID_Max INT = 1
DECLARE @StepID_Next INT = 2
select @StepID_GenerateRestoreScript =
MAX(CASE WHEN step_name like ‘Generate Restore Script’
THEN step_id
ELSE -9
END
)
, @StepID_Max = MAX(step_id)
, @StepID_Next = MAX(step_id) + 1
FROM msdb.dbo.sysjobsteps
where job_id = @jobID
/*If the step does not exist, change the control flow of the last
step and add the Generate Restore Script step after it.*/
IF (@StepID_GenerateRestoreScript = -9)
BEGIN
EXEC msdb.dbo.sp_update_jobstep
@job_id=@jobID
,@step_id = @StepID_Max
,@on_success_action=3
,@on_fail_action=2
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobid
,@step_name=N’Generate Restore Script’
,@step_id= @StepID_Next
,@cmdexec_success_code=0
,@on_success_action=1
,@on_fail_action=2
,@retry_attempts=0
,@retry_interval=0
,@os_run_priority=0
,@subsystem=N’CmdExec’
,@command=@RestoreCommand
,@database_name=@DatabaseName
,@output_file_name=@RestoreScriptDirValue
,@flags=0
END
/*If the Generate Restore Script step DOES exist, we are
updating it (possibly with new @RestoreScriptDirValue) without
touching the rest of the job.*/
ELSE
BEGIN
EXEC msdb.dbo.sp_update_jobstep
@job_id = @jobid
,@step_name=N’Generate Restore Script’
,@step_id= @StepID_GenerateRestoreScript
,@cmdexec_success_code=0
,@on_success_action=1
,@on_fail_action=2
,@retry_attempts=0
,@retry_interval=0
,@os_run_priority=0
,@subsystem=N’CmdExec’
,@command=@RestoreCommand
,@database_name=@DatabaseName
,@output_file_name=@RestoreScriptDirValue
,@flags=0
END
LikeLike
Hi Jared,
Quick question. How would you recommend I use your restore script to automatically execute the restore command generated via your restorecommand stored procedure? I would like to use this in conjunction with Ola’s scripts to do a nightly restore to a testing server. Would you simply execute the command that is scripted in your stored procedure and run it through Ola’s CommandExecute stored procedure?
Thanks,
Michael
LikeLike
Hi Michael, sorry for the delay in getting back to you; crazy busy! If you want to schedule the restore to a remote server, you could save the restore script off as .sql instead of .txt and execute the script from a sqlcmd job step. See this link: https://msdn.microsoft.com/en-us/library/ms170572.aspx
Place the backup files and script in a shared directory.
My question back to you would be, perhaps you’d rather consider log shipping for something like this, as the design of the solution is really meant to be a DR scenario.
LikeLike
I’m receiving the following error when running the Restore Script File Cleanup job: “Unable to start execution of step 1 (reason: Variable SQLLOGDIR not found). The step failed.”. Any help would be greatly appreciated.
LikeLike
Sounds like an instance issue and not necessarily a problem with the script.
Here’s some links: https://support.microsoft.com/en-us/kb/2199578
https://www.mssqltips.com/sqlservertip/2506/identify-location-of-the-sql-server-error-log-file/
LikeLiked by 1 person
The “Variable SQLLOGDIR not found” error is caused by scripting a SQL Agent job from a later version of SQL Server then running it against older version. Prevent the issue by either modifying the maintenancesolution.sql file to create the new custom jobs or use one of the sample jobs generated on the older server as a template for new custom jobs.
LikeLike
This happened to me when I migrated one of my Ola Hallengren jobs to a SQL Server database that was an older version. In the job step, under ‘Advanced’ the Output file was using the SQLLOGDIR variable which I think only exists from SQL Server 2014 onward. On older versions, Ola’s script creates jobs with steps that log to Output file paths that do not use the SQLLOGDIR variable. I copied the path that one of Ola’s default scripts was using on that older version of SQL Server, and used it in the job I migrated over from a newer SQL Server, and then it worked.
LikeLike
Hello, The script is no longer available at http://1drv.ms/1ynGEsV Can you please upload it back. Thank you.
LikeLike
Thanks for letting me know! Should be able to access now.
LikeLike
I am able to access it now. Thank you.
LikeLike
Hi Jarred,
thanks for sharing the script,
but how does it works when you run the logs on secondary replica and Full and diff on primary?
As there are two different commanlog table how we can get the restore script that contains everythink
LikeLike
Hi There. As Ola scripts are designed to log locally, there isn’t a supported way I know of to log to a dedicated admin instance. Perhaps use a synonym on each instance? This introduces a potential single point of failure and additional licensing if done with HA in mind (which I think would be the point). Otherwise, I would guide you to run all backups (including logs) on the primary if performant enough to avoid the issue of splitting out the command log records. Diverting read workloads to a secondary instance if not already in place might help offset putting log backups on the primary instance.
LikeLike
Looking forward to trying this out with Ola’s scripts. Thanks!
LikeLike
I believe the problem with SQLLOGDIR is that the SQL Agent token (SQLLOGDIR) does not exist in versions prior to SQL Server 2014.
LikeLiked by 1 person
Tim, you nailed it. At least that was my problem. I had scripted the job from a 2014 instance, and installed on 2012 instance. Once I fixed the Output File value on the Advanced tab of the Job Step, I was able to execute job without issue.
LikeLiked by 1 person
When I do FULL backups, I create 4 files. The current RestoreCommand scripts was only restoring the first file. My friendly modification to count for multiple files also.
with completed_ola_backups as
(
SELECT [ID]
,[DatabaseName]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[IndexName]
,[IndexType]
,[StatisticsName]
,[PartitionNumber]
,[ExtendedInfo]
,[Command]
,[CommandType]
,[StartTime]
,[EndTime]
,[ErrorNumber]
,[ErrorMessage]
,CASE WHEN [Command] LIKE ‘%\LOG\%’ THEN ‘Log’
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%\LOG_COPY_ONLY\%’ THEN ‘Log’
WHEN [Command] LIKE ‘%\DIFF\%’ THEN ‘Diff’
WHEN [Command] LIKE ‘%\FULL\%’ THEN ‘Full’
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%\FULL_COPY_ONLY\%’ THEN ‘Full’
End BackupType
,CASE WHEN [Command] LIKE ‘%\LOG\%’ THEN 3
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%\LOG_COPY_ONLY\%’ THEN 3
WHEN [Command] LIKE ‘%\DIFF\%’ THEN 2
WHEN [Command] LIKE ‘%\FULL\%’ THEN 1
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%\FULL_COPY_ONLY\%’ THEN 1
End BackupTypeOrder
,CASE CommandType
WHEN ‘BACKUP_LOG’
THEN CHARINDEX(‘.trn’, Command)
WHEN ‘BACKUP_DATABASE’
–First Modification
–THEN CHARINDEX(‘.bak’, Command)
THEN CHARINDEX(‘WITH’, Command) –have WITH COMPRESSION
END filechar
FROM [dbo].[CommandLog]
WHERE CommandType IN (‘BACKUP_LOG’, ‘BACKUP_DATABASE’)
AND EndTime IS NOT NULL — Completed Backups Only
AND ErrorNumber = 0
)
,lastfull as
(
SELECT MAX( [id]) FullId
,DatabaseName
FROM completed_ola_backups
WHERE BackupType = ‘Full’
GROUP BY DatabaseName
)
,lastdiff as
(
SELECT MAX( [id]) DiffId
,cob.DatabaseName
FROM completed_ola_backups cob
INNER JOIN lastfull lf
ON cob.DatabaseName = lf.DatabaseName
AND cob.[ID] > lf.FullId
WHERE BackupType = ‘Diff’
GROUP BY cob.DatabaseName
)
,lastnonlog as
(
SELECT Max([Id]) LogIdBoundary
,DatabaseName
FROM
(
SELECT Fullid Id, DatabaseName
FROM lastfull
UNION ALL
SELECT DiffId Id, ld.DatabaseName
FROM lastdiff ld
) Nonlog
GROUP BY DatabaseName
)
,lastlogs as
(
SELECT cob.[Id] logid
FROM completed_ola_backups cob
INNER JOIN lastnonlog lnl
ON cob.DatabaseName = lnl.DatabaseName
AND cob.[ID] > lnl.LogIdBoundary
)
,validbackups as
(
SELECT FullId backupid
FROM lastfull
UNION
SELECT DiffId backupid
FROM lastdiff
UNION
SELECT logid backupid
FROM lastlogs
)
SELECT cob.DatabaseName
,
Replace(
Replace(
Replace(
LEFT(Command, filechar + 3)
, ‘BACKUP LOG’, ‘RESTORE LOG’)
, ‘BACKUP DATABASE’, ‘RESTORE DATABASE’)
–Second Modification
— , ‘TO DISK’, ‘FROM DISK’) + ”’ WITH NORECOVERY’
, ‘TO DISK’, ‘FROM DISK’) + ‘ NORECOVERY’ –IF you have Multiple FULL backup Files
+ CASE BackupType
WHEN ‘Full’
THEN ‘, REPLACE;’
ELSE ‘;’
END RestoreCommand
FROM completed_ola_backups cob
WHERE EXISTS
(SELECT *
FROM validbackups vb
WHERE cob.[ID] = vb.backupid
)
ORDER BY cob.DatabaseName, Id, BackupTypeOrder
;
LikeLike
Thank you for the solution.
Chris, thanks to you too, I was able to use the modified script for multiple files and generate the restore script.
LikeLike
The script does it job when run through windows powershell window. However the same script does not work from SQL Agent job
Note: Added “-recurse”
$erroractionpreference = “Stop”
$computer = gc env:computername
Get-ChildItem -path Microsoft.PowerShell.Core\FileSystem::F:\SQLBackups\RestoreScripts\$computer_DatabaseRestore*.txt | where {$_.Lastwritetime -lt (date).addhours(-168)} | remove-item -recurse
Error:
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
What could be the reason to fail from SQL Agent?
LikeLike
Hi, got a problem with the output file. The next row after the FULL backup row starts with BAC’ and ends there. What’s the reason?
RESTORE DATABASE [DB] FROM DISK = N’\\SERVER\SQLBACKUP\SERVER\DB\FULL\SERVER_DB_FULL_20170826_143749.bak’ WITH NORECOVERY, REPLACE;
BAC’ WITH NORECOVERY;
RESTORE LOG [DB] FROM DISK = N’\\SERVER\SQLBACKUP\SERVER\DB\LOG\SERVER_DB_LOG_20170831_223500.trn’ WITH NORECOVERY;
RESTORE LOG [DB] FROM DISK = N’\\SERVER\SQLBACKUP\SERVER\DB\LOG\SERVER_DB_LOG_20170831_224000.trn’ WITH NORECOVERY;
RESTORE LOG [DB] FROM DISK = N’\\\SERVER\SQLBACKUP\SERVER\DB\LOG\SERVER_DB_LOG_20170831_224500.trn’ WITH NORECOVERY;
RESTORE LOG [DB] FROM DISK = N’\\SERVER\SQLBACKUP\SERVER\DB\LOG\SERVER_DB_LOG_20170831_225000.trn’ WITH NORECOVERY;
LikeLike
Is the CommandLog record correct for the log file?
LikeLike
Hmm..yes. But it could be the fact, that I changed the file ending for differential backups from .bak to .dif in OLAs SP
LikeLike
That would not be handled without altering the RestoreCommand procedure. You can change the completed_ola_backups CTE to search on dif instead of bak for differentials, but you’ll have to add some extra logic there.
LikeLike
Does your script work on SQL AG nodes? For example, an AG cluster contains two nodes, primary replica and secondary replica and their role can switch anytime after automatic fail over. Ola’s script is able to handle AG backup now. But can your script handle that too? Thanks.
LikeLike
Hi Mike, it works if backups run on primary replica. I would suggest you look at https://dbatools.io for newer solutions as well
LikeLike
Another question, is that txt file overwritten everytime or it just keeps growing? Thanks.
LikeLike
The latest version creates a new date stamped file for each backup. See the restore script file cleanup example for how to maintain file growth in the backup dir using PoSh example
LikeLike
[…] log backup files in the backup chain. You can find his blog post that talks about his solution here and a link the GitHub files here. Thanks to Jared for sharing and allowing me to sleep better at […]
LikeLike
[…] Purnell (b|l|t) is short and concise in his praise of Ola Hallengren maintenance scripts and Jared Zagelbaums extension of those in […]
LikeLike
Hello Jared,
Firstly thanks for the script. But I am having issues while the job runs and fails to generate the restore script.
Basically using your scripts I have created the RestoreCommand stored procedure and the additional job steps for all the Database backup jobs which we implemented using the Ola Maintenance scripts on a DBA utility database.
The job runs impersonating as a SQL agent account which is a service account and this account have been given permissions to read & write on a custom folder within the backup directory which was created before executing the jobs.
The first step of backing up the databases works perfectly fine as expected but the Generate Restore Script fails with the below message within the job history,
Executed as user: ‘DOMAIN SERVICE ACCOUNT’. Unable to open Step output file. The step failed.
Is there something I am missing as part the implementation?
Can you please Help?
Thanks in advance
LikeLike
Hi Sai— make sure the SQL agent service account has rights to your backup dir or wherever you configured the restore script output to be written to.
LikeLike
In our case the root cause for “Unable to open Step output file” was that using $(ESCAPE_SQUOTE(SRVR)) token in sql agent output file on a named instance probably contained dollar ($) which was ended up doing something funky with the output file. We ended up using $(ESCAPE_SQUOTE(INST)) instead.
Amazing script, Jared! Thanks a bunch.
LikeLike
Actually I meant to write “with $(ESCAPE_SQUOTE(SRVR)) on clustered instances you get netname\instancename” 😀 Anyway, as you can imagine introducing backslash like that into the path will freak out SQL Agent.
LikeLike
This is the full script for including the backups taken to URL with storage credentials
Create PROCEDURE [dbo].[RestoreCommand] AS
SET NOCOUNT ON
Declare @DatabaseName sysname
Declare @DatabaseNamePartition sysname = ‘N/A’
Declare @Command nvarchar(max)
Declare @IncludeCopyOnly nvarchar(max) = ‘Y’ — include copy only backups in restore script? Added for AlwaysOn support
Declare @message nvarchar(max)
Declare @credential nvarchar (50)
SELECT top 1 @credential= ‘CREDENTIAL =”’
+ CASE
WHEN CHARINDEX(‘CREDENTIAL = N”’, Command) > 0
THEN SUBSTRING(Command, CHARINDEX(‘CREDENTIAL = N”’, Command) + LEN(‘CREDENTIAL = N”’), CHARINDEX(””, Command, CHARINDEX(‘CREDENTIAL = N”’, Command) + LEN(‘CREDENTIAL = N”’)) – CHARINDEX(‘CREDENTIAL = N”’, Command) – LEN(‘CREDENTIAL = N”’)
)
ELSE NULL
END
+””
FROM CommandLog WHERE Command LIKE ‘%CREDENTIAL = N%’ Order by ID desc
–select @credential
Declare restorecursor CURSOR FAST_FORWARD FOR
with completed_ola_backups as
(
SELECT [ID]
,[DatabaseName]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[IndexName]
,[IndexType]
,[StatisticsName]
,[PartitionNumber]
,[ExtendedInfo]
,[Command]
,[CommandType]
,[StartTime]
,[EndTime]
,[ErrorNumber]
,[ErrorMessage]
,CASE WHEN [Command] LIKE ‘%_LOG%’ THEN ‘Log’
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%LOG_COPY_ONLY%’ THEN ‘Log’
WHEN [Command] LIKE ‘%_DIFF%’ THEN ‘Diff’
WHEN [Command] LIKE ‘%_FULL%’ THEN ‘Full’
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%FULL_COPY_ONLY%’ THEN ‘Full’
End BackupType
,CASE WHEN [Command] LIKE ‘%_LOG%’ THEN 3
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%LOG_COPY_ONLY%’ THEN 3
WHEN [Command] LIKE ‘%_DIFF%’ THEN 2
WHEN [Command] LIKE ‘%_FULL%’ THEN 1
WHEN @IncludeCopyOnly = ‘Y’ AND [Command] LIKE ‘%FULL_COPY_ONLY%’ THEN 1
End BackupTypeOrder
,CASE CommandType
WHEN ‘BACKUP_LOG’ THEN
CASE
WHEN CHARINDEX(‘with’, Command) > 0 THEN CHARINDEX(‘with’, Command)
WHEN CHARINDEX(‘.trn’, Command) > 0 THEN CHARINDEX(‘.trn’, Command)
ELSE 0
END
WHEN ‘BACKUP_DATABASE’ THEN
CHARINDEX(‘WITH’, Command) — have WITH COMPRESSION
END filechar
FROM [dbo].[CommandLog]
WHERE CommandType IN (‘BACKUP_LOG’, ‘BACKUP_DATABASE’)
AND EndTime IS NOT NULL — Completed Backups Only
AND ErrorNumber = 0
)
,lastfull as
(
SELECT MAX( [id]) FullId
,DatabaseName
FROM completed_ola_backups
WHERE BackupType = ‘Full’
GROUP BY DatabaseName
)
,lastdiff as
(
SELECT MAX( [id]) DiffId
,cob.DatabaseName
FROM completed_ola_backups cob
INNER JOIN lastfull lf
ON cob.DatabaseName = lf.DatabaseName
AND cob.[ID] > lf.FullId
WHERE BackupType = ‘Diff’
GROUP BY cob.DatabaseName
)
,lastnonlog as
(
SELECT Max([Id]) LogIdBoundary
,DatabaseName
FROM
(
SELECT Fullid Id, DatabaseName
FROM lastfull
UNION ALL
SELECT DiffId Id, ld.DatabaseName
FROM lastdiff ld
) Nonlog
GROUP BY DatabaseName
)
,lastlogs as
(
SELECT cob.[Id] logid
FROM completed_ola_backups cob
INNER JOIN lastnonlog lnl
ON cob.DatabaseName = lnl.DatabaseName
AND cob.[ID] > lnl.LogIdBoundary
)
,validbackups as
(
SELECT FullId backupid
FROM lastfull
UNION
SELECT DiffId backupid
FROM lastdiff
UNION
SELECT logid backupid
FROM lastlogs
)
SELECT cob.DatabaseName,
Replace(Replace( Replace( Replace(LEFT(Command, filechar + 3) , ‘BACKUP LOG’, ‘RESTORE LOG’), ‘BACKUP DATABASE’, ‘RESTORE DATABASE’), ‘TO DISK’, ‘FROM DISK’),’TO URL’,’FROM URL’)
+ CASE WHEN @credential IS NULL THEN ” ELSE ‘ ‘ + @credential + ‘,’ END + ‘ NORECOVERY, Stats=1’
+
CASE BackupType
WHEN ‘Full’
THEN ‘, REPLACE;’
ELSE ‘;’
END RestoreCommand
FROM completed_ola_backups cob
WHERE EXISTS
(SELECT *
FROM validbackups vb
WHERE cob.[ID] = vb.backupid
)
ORDER BY cob.DatabaseName, Id, BackupTypeOrder
;
RAISERROR( ‘/*****************************************************************’, 10, 1) WITH NOWAIT
set @message = ‘Emergency Script Restore for ‘ + @@Servername + CASE @@Servicename WHEN ‘MSSQLSERVER’ THEN ” ELSE ‘\’ + @@Servicename END
RAISERROR(@message,10,1) WITH NOWAIT
set @message = ‘Generated ‘ + convert(nvarchar, getdate(), 9)
RAISERROR(@message,10,1) WITH NOWAIT
set @message = ‘Script does not perform a tail log backup. Dataloss may occur, use only for emergency DR.’
RAISERROR(@message,10,1) WITH NOWAIT
RAISERROR( ‘******************************************************************/’, 10, 1) WITH NOWAIT
OPEN RestoreCursor
FETCH NEXT FROM restorecursor
INTO @databasename, @command
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DatabaseName @DatabaseNamePartition AND @DatabaseNamePartition ‘N/A’
BEGIN
set @message = ‘RESTORE DATABASE ‘ + ‘[‘ + @DatabaseNamePartition + ‘]’ + ‘ WITH RECOVERY;’
RAISERROR(@message,10,1) WITH NOWAIT
END
IF @DatabaseName @DatabaseNamePartition
BEGIN
set @message = char(13) + char(10) + char(13) + char(10) + ‘——–‘ + @DatabaseName + ‘————-‘
RAISERROR(@message,10,1) WITH NOWAIT
END
RAISERROR( @Command,10,1) WITH NOWAIT
SET @DatabaseNamePartition = @DatabaseName
FETCH NEXT FROM restorecursor
INTO @databasename, @command
END
set @message = ‘RESTORE DATABASE ‘ + ‘[‘ + @DatabaseNamePartition + ‘]’ + ‘ WITH RECOVERY;’
RAISERROR(@message,10,1) WITH NOWAIT
;
CLOSE restorecursor;
DEALLOCATE restorecursor;
LikeLike
Thank you for your wonderful script. This is exactly what I needed
LikeLike