How does SQL detect if a database backup was done?

Question

Post reply

How does SQL detect if a database backup was done?

generalrouting

SSC Enthusiast

Points: 177
More actions
April 16, 2016 at 12:16 am

#309919

Does anyone know how does SQL detects if a database backup was done? For example, if a database is created and I try to do a diff backup, SQL gives error "Cannot perform a differential backup for database "xx", because a current database backup does not exist". The same error if I have a database in full recovery model change to simple recovery model and change again to full recovery model and do a log backup. I know you can look in msdb.dbo.backupset to see if there is record if a full was done. I am looking for a way to check LSN or something. So that when I see there a broken chain in backup set that causes the full backup invalid, it automatically do a full backup.
My goal is:
Weekly full
Daily Diff - when diff runs and don't see there is a full, it automatically does a full before doing a diff.
TLog every 15 minutes - when tlog runs and don't see there is a full, it automatically does a full before doing a diff.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

This should help you get started...

SELECT LastFullBackupDT = MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE database_name = 'yourdatabasenamehere'

AND recovery_model <> 'SIMPLE'

AND is_copy_only = 0

AND [type] = 'D'

;

Reference:

https://msdn.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

EDIT: Don't use that. Although it correctly finds what the last full backup was, it could let a problem through if someone has recently changed to the SIMPLE recovery model and no backup has been taken since then. I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Jeff Moden (4/17/2016)
This should help you get started...
SELECT LastFullBackupDT = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = 'yourdatabasenamehere'
AND recovery_model <> 'SIMPLE'
AND is_copy_only = 0
AND [type] = 'D'
;
[/code]
Reference:
https://msdn.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

Actually, don't use that. It could allow a problem through. I'll be back with a fix.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Here's what I'm talking about... this checks to find out what the current recovery model is so that you can make better decisions as to what kind of backup is needed of if something is already in place. I've not yet been able to figure out how you might know that a database went from FULL to SIMPLE and back to FULL so that you know that a DIF needs to be taken to restart the log file change but this may do for now.

DECLARE @DBName SYSNAME;

SELECT @DBNAME = 'PutYourDatabaseNameHere'

;

--===== This finds out what the latest backup was.

SELECT TOP 1

DBRecoveryModel = db.recovery_model

,LastBackupType = bus.[type]

,LastBackupDate = bus.backup_finish_date

,DBState = db.state_desc

,IsReadOnly = db.is_read_only

,LastFullBkupOn = (SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @DBNAME AND is_copy_only = 0 AND [type] = 'D')

FROM msdb.dbo.backupset bus

JOIN sys.databases db ON bus.database_name = db.name

WHERE db.name = @DBNAME

AND bus.is_copy_only = 0

AND bus.[type] IN ('D','I','L')

ORDER BY bus.backup_finish_date DESC

;

Shifting gears a bit, there will be a lot of people that recommend Ola Hallengren's fine code or "Minion" or a half dozen other freebies that can make your life easier and both of the ones I've mentioned are great. I like what you're doing much better though... Learning as you go so you can fix it if it breaks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

You can check table [database_recovery_status] for the specific database. When value of column [last_log_backup_lsn] is NULL a FULL backup needs to be performed, before a LOG backup can be created.

SELECT db_name(db.database_id) AS 'database'

, db.recovery_model_desc

, db_rec.last_log_backup_lsn

, case when recovery_model_desc <> 'SIMPLE' and last_log_backup_lsn is null

then 'FULL backup needed'

else 'LOG backup possible'

end as status

FROM sys.database_recovery_status db_rec

INNER JOIN sys.databases db

on db_rec.database_id = db.database_id

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

generalrouting SSC Enthusiast Points: 177 More actions · Answer 5

Hi Jeff,

Thanks for the suggestion. I try that out to see if the information return is useful. Just to give a little bit more information. There are 2 cases:

1. I have database from DEV that goes from PROD weekly. If it goes in the middle of the week, the differential backup fails. it is in simple recovery

2. I have database in full recovery and it stays in PROD always. No one should change recovery model. However, I am not counting on that. so I want to make sure my backup stored procedure works when someone change recovery model.

This is what I have so far. It works but not 100%.

select TOP 1 differential_base_lsn,* from sys.master_files where db_name(database_id) = 'DBName' and type_desc = 'ROWS'-- and file_id = 1

select type, first_lsn,checkpoint_lsn,last_lsn,database_backup_lsn,backup_start_date,backup_finish_date,name, description

from msdb.dbo.backupset

where database_name = 'DBName'

and is_copy_only = 0 -- copy_only does not count

and type IN ('D') -- Look for full back

order by 2

If the first_lsn and the differential_base_lsn are not the same, I do a full. It worked for case #1 but it seems like every other day it has to do a full backup every other day even though the database does not change. I am thinking about changing to checkpoint_lsn.

I am still haven't figure out for case #2 yet.