September 4, 2018 at 4:41 am
Hi all,
We have an automated job that kills all connections to a DB on UAT, restores a full backup of prod to UAT, then any diff files, plus a few other processes.
The job failed at the diff stage with “This differential backup cannot be restoredbecause the database has not been restored to the correct earlier state”.
I manually brought the database out of recovery, then
USE
master
ALTER DATABASE Mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE Mydb
FROM DISK = N'\\BHs-Storage02\DPMSQLMigrations\OneLove\Backup\Mydb
WITH FILE = 1, NOUNLOAD, STATS = 1, Replace, Recovery
GO
But I get:
Msg 3117, Level 16, State 1, Line 7
The log or differential backup cannot be restored because no files are ready to rollforward
TIA
September 4, 2018 at 5:13 am
Try it with NORECOVERY instead of RECOVER in the restore. leave it in the recovering state until the differential is done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2018 at 5:24 am
And once you do what Grant instructed, if you still get the error, you need to look at the backup tables in MSDB to find out what the correct "earlier state" is.
I have found, at my work place, sometimes we accidentally take FULL backups of databases instead of COPY ONLY when getting a backup to restore down to non-prod. Which means any logs or diffs after that moment are pointing to a different FULL backup than the one we might expect by looking at the usual backup NAS. Definitely a whoopsie moment that happens to a lot of people. :crazy:
September 4, 2018 at 7:24 am
Brandie Tarvin - Tuesday, September 4, 2018 5:24 AMAnd once you do what Grant instructed, if you still get the error, you need to look at the backup tables in MSDB to find out what the correct "earlier state" is.
I have found, at my work place, sometimes we accidentally take FULL backups of databases instead of COPY ONLY when getting a backup to restore down to non-prod. Which means any logs or diffs after that moment are pointing to a different FULL backup than the one we might expect by looking at the usual backup NAS. Definitely a whoopsie moment that happens to a lot of people. :crazy:
Great point! Absolutely do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2018 at 6:34 am
Brandie Tarvin - Tuesday, September 4, 2018 5:24 AMAnd once you do what Grant instructed, if you still get the error, you need to look at the backup tables in MSDB to find out what the correct "earlier state" is.
I have found, at my work place, sometimes we accidentally take FULL backups of databases instead of COPY ONLY when getting a backup to restore down to non-prod. Which means any logs or diffs after that moment are pointing to a different FULL backup than the one we might expect by looking at the usual backup NAS. Definitely a whoopsie moment that happens to a lot of people. :crazy:
Can I presume you mean backupset/backupsetmedia etc on Prod? If so, in which column/table do I find this earlier state" ?
September 6, 2018 at 6:49 am
JackCarrington - Thursday, September 6, 2018 6:34 AMBrandie Tarvin - Tuesday, September 4, 2018 5:24 AMAnd once you do what Grant instructed, if you still get the error, you need to look at the backup tables in MSDB to find out what the correct "earlier state" is.
I have found, at my work place, sometimes we accidentally take FULL backups of databases instead of COPY ONLY when getting a backup to restore down to non-prod. Which means any logs or diffs after that moment are pointing to a different FULL backup than the one we might expect by looking at the usual backup NAS. Definitely a whoopsie moment that happens to a lot of people. :crazy:
Can I presume you mean backupset/backupsetmedia etc on Prod? If so, in which column/table do I find this earlier state" ?
See if this helps you.
USE msdb;
GO
SELECT bs.database_name, bf.backup_set_id, bs.database_backup_lsn, bs.first_lsn, bs.last_lsn, *
FROM backupfile bf
INNER JOIN backupset bs
ON bf.Backup_set_id = bs.Backup_set_ID
ORDER BY bf.logical_name, bf.file_number, bs.first_lsn, bs.last_lsn
September 6, 2018 at 7:35 am
Hi Brandie, I think I've gotten to the bottom of this....
Apparently 'Copy-Only' full backups, once restored on UAT, do not then allow subsequent diff backups to be restored. Not only that, but when I checked the backup folder, the full backup is more recent than the associated diff backup !!! The automated restore job at UAT has a step after the full refresh that attempts to apply the diff, then goes off and does some other stuff, when I checked
/****** Script for SelectTopNRows command from SSMS ******/
SELECT BACKUP_finish_date, is_copy_only, backup_size, type, *
FROM [msdb].[dbo].[backupset]
WHERE database_name = 'Ahem'
Order by [msdb].[dbo].[backupset].backup_finish_date desc
I found the original backups going back a whole month WERE indeed 'Copy Only' !! Thank you f**ing Redmond for your really, really informatively meaningful error messages!!!
Thanks Brandie 🙂
September 6, 2018 at 7:44 am
Glad I could help point you in the right direction.
September 6, 2018 at 9:34 am
JackCarrington - Thursday, September 6, 2018 7:35 AMHi Brandie, I think I've gotten to the bottom of this....Apparently 'Copy-Only' full backups, once restored on UAT, do not then allow subsequent diff backups to be restored. Not only that, but when I checked the backup folder, the full backup is more recent than the associated diff backup !!! The automated restore job at UAT has a step after the full refresh that attempts to apply the diff, then goes off and does some other stuff, when I checked
/****** Script for SelectTopNRows command from SSMS ******/
SELECT BACKUP_finish_date, is_copy_only, backup_size, type, *
FROM [msdb].[dbo].[backupset]
WHERE database_name = 'Ahem'
Order by [msdb].[dbo].[backupset].backup_finish_date desc
I found the original backups going back a whole month WERE indeed 'Copy Only' !! Thank you f**ing Redmond for your really, really informatively meaningful error messages!!!
Thanks Brandie 🙂
Seems to me that the error message was telling you exactly what the problem was.
September 6, 2018 at 10:37 am
Lynn Pettis - Thursday, September 6, 2018 9:34 AMJackCarrington - Thursday, September 6, 2018 7:35 AMHi Brandie, I think I've gotten to the bottom of this....Apparently 'Copy-Only' full backups, once restored on UAT, do not then allow subsequent diff backups to be restored. Not only that, but when I checked the backup folder, the full backup is more recent than the associated diff backup !!! The automated restore job at UAT has a step after the full refresh that attempts to apply the diff, then goes off and does some other stuff, when I checked
/****** Script for SelectTopNRows command from SSMS ******/
SELECT BACKUP_finish_date, is_copy_only, backup_size, type, *
FROM [msdb].[dbo].[backupset]
WHERE database_name = 'Ahem'
Order by [msdb].[dbo].[backupset].backup_finish_date desc
I found the original backups going back a whole month WERE indeed 'Copy Only' !! Thank you f**ing Redmond for your really, really informatively meaningful error messages!!!
Thanks Brandie 🙂
Seems to me that the error message was telling you exactly what the problem was.
ISTR that I've actually been able to restore Diffs to a copy only FULL, but when the copy only is later than the Diff, it definitely doesn't work.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply