May 21, 2015 at 6:04 am
Hello Masters,
We have a production environment, where below is the backup strategy:
Full Backup : Everyday 7 PM
Differential Backup: Everyday 12 AM
T-Log Backup: Every 6 hours (starts from 12 AM)
Now, our client feels database is corrupt from yesterday (20th May 2015) after 4 PM. and they want us to restore database backup (Point in time recovery) up to 4 PM (20th May 2015).
I restored Full Backup of 19th May 7 PM
Than I can see a Transaction log backup generated at 12 AM and at same time can see Differential backup generated. I tried to restore T-Log backup but its giving an error that previous LSN number is missing !! How could it be possible ? Even I tried to restore Differential backup but its not allowing me (Erro: Unable to create restore plan due to break in LSN chain).
What should I do now ?
Below are list of available backup from 19th May 20015 Full backup
Full Backup - 19th May 7 PM
TLog Backup - 20th May 12 AM
Diff Backup - 20th May 12 AM
TLog Backup- 20th May 6 AM
Tlog Backup - 20th May 12 PM
Tlog Backup- 20th May 6 PM (Client wants us to restore it till this time)
Full Backup - 20th May 7 PM
I attached Avaialble backup file's pic as well.
It's very urgent. Please help me.
May 21, 2015 at 6:16 am
are you restoring the initial full backup with no recovery before you attempt to restore the differential/log backup? Are you certain that the full backup was not a copy only backup?
May 21, 2015 at 6:20 am
Why do they think it's corrupt? Is there any justification there?
Check the MSDB backup takes, see what backups were taken, compare that with the backups you have, see if there are any log backups missing from your folder.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 7:10 am
Hello Gila Monster,
I checked MSDB, there is nothing mssing with backup set.
Is it any bug with SQL 2012 Management studio ?
May 21, 2015 at 7:13 am
No, SSMS is not going to be the cause of SQL Server telling you that the log chain is broken.
What does MSDB show?
What are your restore statements?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 8:45 am
Hello Gila Monster,
It seems some issue with Backup set only. I ran below query on MSDB:
SELECT
'SQL' + '_' + msdb.dbo.backupset.machine_name+'_'+ convert(CHAR(30),SERVERPROPERTY('Servername')) + '_' + msdb.dbo.backupset.database_name AS CIName,
CONVERT(CHAR(15), SERVERPROPERTY('Servername') ) AS Instance_Name,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date AS 'Last Successful Backup',
CASE msdb..backupset.type
WHEN 'D' THEN 'Full Backup'
WHEN 'L' THEN 'Transactional Log Backup'
WHEN 'I' THEN 'Differential Backup'
END AS backup_type
FROM msdb.dbo.backupmediafamily (nolock)
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 101) >= GETDATE() - 3)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
And found that there was one more Full backup at 8.17 PM and one more Tlog backup at 10.12 PM, !! which backup sets are not present there in the folder !
May 21, 2015 at 9:02 am
jitendra.padhiyar (5/21/2015)
one more Tlog backup at 10.12 PM
There's your problem.
The full's fine, but a missing log backup will mean that you can't restore past that point. So unless you can find that log backup or have a full/diff after that time, you can't restore past 10:12 PM.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply