July 11, 2012 at 7:33 am
I've got a problem and would like to know what I am missing. I know you need a full backup before you can take a differential backup. I have a differential job that is failing because SQL says there is no existing full backup. My code checks for the existence of a full backup before trying the differential by the following:
SELECT *
FROM msdb.dbo.backupset a
WHERE database_name = @DatabaseName
AND [type] = 'D'
AND server_name = @@servername
If it returns any records, I assume a full backup has been made and then proceed to take the differential. This serves me well for most cases. However, if I restore a database with the same name as an existing database and overwrite the existing one, then my differential fails. The above code returns records for the old database backups, but SQL somehow knows the newly restored database doesn't have a full backup. Any idea how I can check for this?
July 11, 2012 at 7:42 am
Try something like this:
DECLARE @DatabaseName VARCHAR(128) = 'TrainingDB'
SELECT *
FROM msdb.dbo.backupset a
WHERE database_name = @DatabaseName
AND [type] = 'D'
AND server_name = @@servername
AND NOT EXISTS (SELECT *
FROM msdb.dbo.backupset a
WHERE database_name = @DatabaseName
AND fork_point_lsn IS NOT NULL)
When you restore a database, it will create a fork (multiple restore paths).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2012 at 7:49 am
Hmm.. That doesn't appear to be the case for me.. My fork_point_lsn values are all null for this database.
July 11, 2012 at 8:03 am
I think the way to do it (or at least one way to do it) is to check the restorehistory table and look for a restore with a date after the date of the last full backup from the backupset table...
July 11, 2012 at 1:49 pm
Here is a rewrite of the script to support that.
DECLARE @DatabaseName VARCHAR(128) = 'TrainingDB'
SELECT *
FROM msdb.dbo.backupset a
LEFT OUTER JOIN msdb.dbo.restorehistory rh
ON a.backup_set_id = rh.backup_set_id
WHERE database_name = @DatabaseName
AND [type] = 'D'
AND server_name = @@servername
AND NOT EXISTS (SELECT *
FROM msdb.dbo.backupset a
WHERE database_name = @DatabaseName
AND fork_point_lsn IS NOT NULL)
AND rh.backup_set_id IS NULL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply