February 12, 2014 at 3:12 am
Hi Everyone,
I am testing a DR scenario at work but having problems. Our backup strategy consists of
1 nightly full backup
Differential backups every few hours
Transaction log backups every 10 minutes
I have restored the full database backup with "No recovery" and it is currently in a state of "recovering". My understanding is that I should now apply the last Differential backup and then the subsequent transaction log backups.
When I try to restore the Diff backup, I receive the following message...
"This differential backup cannot be restored because the database has not been restored to the correct earlier state"
I had a look at the headers and noticed the full backup checkpoint LSN is 13782000008345800277 but the DatabasebackupLSN for the differential backups does not match (13784000012174600142)
Do you think this is the problem and any can anyone suggest how this has happened?
Many thanks for any help and guidance
David
February 12, 2014 at 5:59 am
TerrenceTheCat (2/12/2014)
Hi Everyone,I am testing a DR scenario at work but having problems. Our backup strategy consists of
1 nightly full backup
Differential backups every few hours
Transaction log backups every 10 minutes
I have restored the full database backup with "No recovery" and it is currently in a state of "recovering". My understanding is that I should now apply the last Differential backup and then the subsequent transaction log backups.
When I try to restore the Diff backup, I receive the following message...
"This differential backup cannot be restored because the database has not been restored to the correct earlier state"
I had a look at the headers and noticed the full backup checkpoint LSN is 13782000008345800277 but the DatabasebackupLSN for the differential backups does not match (13784000012174600142)
Do you think this is the problem and any can anyone suggest how this has happened?
Many thanks for any help and guidance
David
Can you post the output from this query when run for each of the 2 databases
SELECT DB_NAME(database_id)
, name
, differential_base_lsn
, differential_base_time
FROM sys.master_files
WHERE database_id = DB_ID('yourdb')
AND type_desc = 'ROWS'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2014 at 3:18 am
Hi Perry,
Thank you so much for your help. Below is the output from the query you asked me to execute
(No column name) name differential_base_lsn differential_base_time
Point in Time Test CompanyName_Data 13782000008345800277 2014-02-10 23:20:02.923
Point in Time Test CompanyName_1_Data 13782000008345800277 2014-02-10 23:20:02.923
Best regards
David
February 13, 2014 at 7:07 am
TerrenceTheCat (2/13/2014)
Hi Perry,Thank you so much for your help. Below is the output from the query you asked me to execute
(No column name) name differential_base_lsn differential_base_time
Point in Time Test CompanyName_Data 13782000008345800277 2014-02-10 23:20:02.923
Point in Time Test CompanyName_1_Data 13782000008345800277 2014-02-10 23:20:02.923
Best regards
David
I need the results when run against the primary database and the secondary database, should be 2 lots of results
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2014 at 7:48 am
Hi Perry,
I made a copy of the production back up files to a test server on Monday as I did not want to have any impact on Prod while I was testing the recovery.
Below are the results from statement you provided for existing Production DB and my point in time test DB on a different server.
Production
ProductionDB Company_Data 13855000004026800266 2014-02-13 13:00:32.097
ProductionDB Company_1_Data 13855000004026800266 2014-02-13 13:00:32.097
Test
Point in Time Test Company_Data 13782000008345800277 2014-02-10 23:20:02.923
Point in Time Test Company_1_Data 13782000008345800277 2014-02-10 23:20:02.923
February 13, 2014 at 12:04 pm
The differential base lsn results indicate that since you restored the full backup from prod another backup had been taken against prod, @ 1pm on the 13/02 to be exact. It's this new backup that the differential is based upon.
What is the date and time of the differential you are attempting to restore?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2014 at 3:46 am
Hi Perry,
Just a quick follow up....
From your guidance I was able to track down a hourly full backup that was being undertaken outside of the SQL Server by a third party tool. This meant the differential back ups were every 3 hours and full backups every hour so therefore the differential LSNs were always out of sync and could never be restored.
Thank you for taking time out to respond to my posts.
Best regards
David
February 20, 2014 at 4:38 am
TerrenceTheCat (2/20/2014)
Hi Perry,Just a quick follow up....
From your guidance I was able to track down a hourly full backup that was being undertaken outside of the SQL Server by a third party tool. This meant the differential back ups were every 3 hours and full backups every hour so therefore the differential LSNs were always out of sync and could never be restored.
Thank you for taking time out to respond to my posts.
Best regards
David
Thank you for responding, glad you got it sorted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply