question

navjyotn avatar image
navjyotn asked

What exactly SQL server check for current backup while taking t-log backup

Hi All, I have one interesting question related to an issue we have faced recently. We had a logshipping setup between database A (Primary) and B (Secondary) both are on different server so obviously we must have taken full backup first followed by consecutive T-log backups to configure log shipping. One day we have decided to bring the database B online and for that we did break the log shipping, the purpose of doing this was we wanted to use B as Primary now so we have setup all backup & Maintenance job on this server. Now its time to take backup, **we have just hit the T-log backup (please note We have never ever taken FULL backup on B) which got successful.** But as we all are aware for T-Log backup there should be at least 1 current (FULL) backup but this is not being applicable in this case. Does any one has idea what Check exactly SQL perform for CURRENT backup existence while taking T-Log backup?? Appreciate your help/Idea's
backupbackup-restorebackupslogshipping
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The secondary database is a restore of the primary database - so there is a full backup - it's the one you started the log shipping with. Are you seeing some issues or errors?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

At a guess I'd say something in the database boot page `dbcc dbinfo() with tableresults` - after all you can't rely on MSDB surely?
1 Like 1 ·
Hi Kev, There was a backup from which the secondary has been restored but the information of backup is not present on secondary in backupset table. We only have information of restore in restorehistory table. My question is what exactly SQL server use to check for the existence of FULL backup while taking log backup? Which information and where that information is located?
0 Likes 0 ·
Here you go Kev This is it!!!!! "dbi_differentialBaseLSN" is the field SQL Server check for the last full backup while mounting the database. Thanks alot.
0 Likes 0 ·
emil87b avatar image
emil87b answered
You can check when was the last full backup performed by running below query, however if you never took a full backup before of the database you should get an error message when taking the log backup. SELECT sdb.Name AS DatabaseName , COALESCE(CONVERT(VARCHAR(12) , MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime FROM sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name GROUP BY sdb.Name until you take a full database backup the database will be operating in auto-truncate mode, so SQL Server will truncate the log when database checkpoints occur. Here's one more query that will help you read the backup history for all dtabases: SELECT TOP 100 s.database_name, m.physical_device_name, Cast(Cast(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, Cast(Datediff(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, Cast(s.first_lsn AS VARCHAR(50)) AS first_lsn, Cast(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id ORDER BY backup_start_date DESC, backup_finish_date
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Emil, Thanks for your response. The query which you are providing will get executed on Primary but it will fail on secondary as we are not copying the data of msdb.dbo.backupset table so the backup information of primary will be available only on Primary not on secondary. Any thoughts?
0 Likes 0 ·
Is the secondary database B a restore from a full backup of database A or any other? if so this backup will be treated as the first full backup for the log chain. if you run RESTORE HEADERONLY for the backup log of database B you should get the DatabaseBackupLSN
0 Likes 0 ·
Yes B is a restore from a full backup of A. Lets assume you are right that the full backup of A will be treated as the full backup of B as well but still the question is same what exactly SQL server check when i go ahead and take the log backup of B? Where the full backup information is saved in database B? Restore headeronly is useful to check LSN while restoring log backups. Here my case is i am taking log backup.
0 Likes 0 ·
because one thing it definetly checks, if the chain is not broken, if you break the log chain (lets say by changing recovery model to simple and back to full) you have to take full backup before another log backup.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.