Full backup Checkpoint LSN does not match Differential DatabasebackupLSN
-
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
-
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
SELECTDB_NAME(database_id), name, differential_base_lsn, differential_base_timeFROM sys.master_filesWHERE database_id = DB_ID('yourdb')AND type_desc = 'ROWS'-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
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_lsndifferential_base_time
Point in Time TestCompanyName_Data137820000083458002772014-02-10 23:20:02.923
Point in Time TestCompanyName_1_Data137820000083458002772014-02-10 23:20:02.923
Best regards
David
-
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_lsndifferential_base_time
Point in Time TestCompanyName_Data137820000083458002772014-02-10 23:20:02.923
Point in Time TestCompanyName_1_Data137820000083458002772014-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"
-
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
ProductionDBCompany_Data138550000040268002662014-02-13 13:00:32.097
ProductionDBCompany_1_Data138550000040268002662014-02-13 13:00:32.097
Test
Point in Time TestCompany_Data137820000083458002772014-02-10 23:20:02.923
Point in Time TestCompany_1_Data137820000083458002772014-02-10 23:20:02.923
-
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"
-
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
-
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"
-
Find below/attached script to get LSN of full, diff and log backup which will help to check the LSN and find the location of adhoc backup while doing restoration.
--LSN Check
--Full backup-CheckpointLsn -> 1stlogbackup-FirstLSN,1stLogbackup-LastLSN->2ndlogbackup-FirstLSN
--Full backup-CheckpointLsn -> Differential backup->DatabasebackupLSN
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'Adventurework' -- Database name
SELECT
BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
,BS.checkpoint_lsn,BS.database_backup_lsn
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
and BS.[type] in ('D','I','l')--Type like D-FULL, I Differentail,F- File,L- Log backup,FG- File group.
ORDER BY backup_start_date DESC
,backup_finish_date
--Other Script
select backup_start_date,backup_finish_date,is_copy_only,database_name,type ='D',BF.physical_device_name,
first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,user_name,backup_size,compressed_backup_size
from msdb.dbo.backupset BS join msdb.dbo.backupmediafamily BF
on BS.media_set_id = BF.media_set_id
where type ='D'
and database_name = 'Adventurework' -- Database name
and is_copy_only = 0
order by 1 desc
--Reference :-
--https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply