December 3, 2008 at 3:16 pm
How does SQL Server know whether a full backup has been taken?
My end goal is to add that logic and incorporate it into a log backup script which will first check to see whether a full backup has been taken yet.
December 3, 2008 at 3:26 pm
if exists (select 1 from msdb..backupset where database_name = 'blah' and type = 'D')
i.e. history of backups is kept in backupset table in msdb.
pls check column names, no access to SQL currently.
---------------------------------------------------------------------
December 3, 2008 at 3:45 pm
That will only verify whether a backup has ever been taken.
Suppose a backup has been taken on a db in Full recovery mode. Then I change it to simple. Then I change it back to Full. At this point, a log backup will fail because a full backup has not been taken since the db's recovery model has changed.
December 4, 2008 at 7:22 am
David (12/3/2008)
That will only verify whether a backup has ever been taken.Suppose a backup has been taken on a db in Full recovery mode. Then I change it to simple. Then I change it back to Full. At this point, a log backup will fail because a full backup has not been taken since the db's recovery model has changed.
Hmmm, thats a slightly different question. I do not believe information is kept as to when database options are changed. you would certainly have to include a check that the database is not in simple mode. This scenario would return a particular error so after the tran log backup include logic to trap the error.
---------------------------------------------------------------------
December 4, 2008 at 4:15 pm
I figured it out. So scenario is I don't want a T-Log backup to be performed if a backup has not yet been performed. Example using database named Test.
SET NOCOUNT ON
DECLARE @Table TABLE (
ParentObject nvarchar(100),
[Object] nvarchar(100),
Field nvarchar(100),
[VALUE] nvarchar(100)
)
DECLARE @cmd varchar(100)
SET@cmd = 'DBCC DBINFO (''test'') WITH TABLERESULTS, NO_INFOMSGS'
INSERT @Table
EXEC (@cmd)
IF (SELECT [Value] FROM @Table
WHERE [Object] = 'dbi_dbbackupLSN'
AND Field = 'm_fSeqNo') = 0
BEGIN
RETURN
END
ELSE BEGIN
--T-Log backup script
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply