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
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?
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?
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?
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
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?
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.
15 People are following this question.
