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.