February 7, 2018 at 7:44 am
Hi,
I'm just curious if others use sp_DatabaseRestore from Brent Ozar's First Responder's Kit. If so, are you having any issues restoring differentials?
Here's some context. I use Ola's scripts for our full, diff, and t-log backups. It has served us well. I wanted an easy way to restore just the full, the full + diff, or full + t-log (point in time) if I needed to. I've found that I can successfully do the first and third (just the full and full + t-log). However, if I simply try to restore a full and a differential, I only ever end up with the full. Now I haven't dug into the code a ton. I actually plan to, but before I spend hours trying to figure out what's happening, I thought I'd at least check with all of you to see if you notice anything dumb that I'm just missing. Here's an example of a command I'd use to attempt to restore the full and diff.
EXEC DBA_Utils.dbo.sp_DatabaseRestore
@Database = 'production',
@RestoreDatabaseName = 'test',
@BackupPathFull = 'E:\temporary\FULL\',
@BackupPathDiff = 'E:\temporary\DIFF\',
@RestoreDiff = 1,
@ContinueLogs = 0,
@RunRecovery = 1,
@TestRestore = 0,
@Debug = 1;
I should also note that the debug does not print commands that indicate that a diff restore is happening, so it seems to not even get to the correct block of code. Before I go digging, does anyone see anything inherently wrong with my stored procedure call?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 7, 2018 at 2:57 pm
Can you post your full and diff backup script?
February 7, 2018 at 4:25 pm
AlexSQLForums - Wednesday, February 7, 2018 2:57 PMCan you post your full and diff backup script?
Hi Alex,
I'm not exactly sure what you mean. I use Ola Hallengren's backup scripts. What exactly are you looking for?
I should also note that I was able to restore the full and the diff using the RESTORE DATABASE command, so I know there's not an issue with the backup files themselves (at least, I think that proves that there isn't...)
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 7, 2018 at 5:48 pm
Hi Mike
I think you have to specify log directory as well
EXEC dbo.sp_DatabaseRestore
@Database = 'TEST1',
@BackupPathFull = '\\PATHTOFILE\FULL\',
@BackupPathDiff = '\\PATHTOFILE\DIFF\',
@BackupPathLog = '\\PATHTOFILE\LOG\',
@RestoreDiff = 1,
@ContinueLogs = 0,
@RunRecovery = 1;
February 8, 2018 at 11:48 am
AlexSQLForums - Wednesday, February 7, 2018 5:48 PMHi Mike
I think you have to specify log directory as well
EXEC dbo.sp_DatabaseRestore
@Database = 'TEST1',
@BackupPathFull = '\\PATHTOFILE\FULL\',
@BackupPathDiff = '\\PATHTOFILE\DIFF\',
@BackupPathLog = '\\PATHTOFILE\LOG\',
@RestoreDiff = 1,
@ContinueLogs = 0,
@RunRecovery = 1;
Alex,
Thank you for the suggestion. Even though I don't think specifying a log path should be a requirement for diffs, I gave it a try. Unfortunately, it still didn't restore the diff. Now, to be fair, I didn't actually put any log files in the directory--I only specified a path in the call. Also, as you can see, for simplicity, all of my paths are the same. I'm not sure if either of these two things (not putting actual log files in the directory and using the same directory for everything) had anything to do with the diff not getting picked up, but I'll keep digging...
EXEC DBA_Utils.dbo.sp_DatabaseRestore
@TestRestore = 0,
@Database = 'production',
@RestoreDatabaseName = 'production_restored',
@MoveFiles = 1,
@MoveDataDrive = 'E:\temporary\',
@MoveLogDrive = 'E:\temporary\',
@BackupPathFull = 'E:\temporary\',
@BackupPathDiff = 'E:\temporary\',
@BackupPathLog = 'E:\temporary\',
@RestoreDiff = 1,
@ContinueLogs = 0,
@RunRecovery = 1,
@Debug = 1;
Again, thanks for your help with this.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 12, 2018 at 2:11 pm
Mike
Can you post Ola's backup script which you are using?
February 12, 2018 at 2:23 pm
AlexSQLForums - Monday, February 12, 2018 2:11 PMMike
Can you post Ola's backup script which you are using?
Sure. This is the command I'm using. Do you need more detail than this?
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA_Utils -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'F:\SQLBackups', @BackupType = 'DIFF', @verify = 'Y', @CleanupTime = 360, @checksum = 'Y', @LogToTable = 'Y'" -b
Mike Scalise, PMP
https://www.michaelscalise.com
September 20, 2018 at 1:23 pm
I know this post is a few months old, but I also had this same issue. I ended up splitting up the Full and Differential Restore which worked for me.
EXEC DBA_Utils.dbo.sp_DatabaseRestore
@Database = 'production',
@RestoreDatabaseName = 'test',
@BackupPathFull = 'E:\temporary\FULL\',
@RunRecovery = 0;
EXEC DBA_Utils.dbo.sp_DatabaseRestore
@Database = 'production',
@RestoreDatabaseName = 'test',
@BackupPathDiff = 'E:\temporary\DIFF\',
@RestoreDiff = 1,
@RunRecovery = 1;
September 23, 2018 at 5:34 pm
meganjanosky - Thursday, September 20, 2018 1:23 PMI know this post is a few months old, but I also had this same issue. I ended up splitting up the Full and Differential Restore which worked for me.
EXEC DBA_Utils.dbo.sp_DatabaseRestore
@Database = 'production',
@RestoreDatabaseName = 'test',
@BackupPathFull = 'E:\temporary\FULL\',
@RunRecovery = 0;EXEC DBA_Utils.dbo.sp_DatabaseRestore
@Database = 'production',
@RestoreDatabaseName = 'test',
@BackupPathDiff = 'E:\temporary\DIFF\',
@RestoreDiff = 1,
@RunRecovery = 1;
Hi Megan,
Thank you for posting this. It makes perfect sense to just split it into two steps--full with norecovery then the diff. I had shifted to dbatools to build my restore process, but if I need to go back to sp_DatabaseRestore, I'll know how to do it. Again, thank you.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply