December 29, 2008 at 11:00 pm
Jeffrey Williams (12/29/2008)
When you change the recovery model from SIMPLE to FULL or FULL to SIMPLE, the database is marked as *requiring* a full backup before you can perform a transaction log backup.
Agreed
This also applies to a differential backup...
I'm gonna have to test that one. Will do so once I get SQL reinstalled.
If a backup log ... with truncate invalidated diffs, then it would not be possible to do a diff backup in simple, because the log is constantly been truncated.
In order for you to be able to perform a differential backup, you must have the base differential backup, which is a conventional backup, partial backup or file backup for that database.
Agreed. I wasn't aware that changing recovery models marked the DB as not having a backup, rather than having a broken log chain.
Differentials also rely upon the LSN - which is stored in the column 'differential_base_lsn'.
Yup, though afaik, that's just to identify which full backup the diff belongs with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 12:07 am
Bini, I seem to recall there's a bug in one version of SQL regarding the diff backups. Perhaps a google search of support.microsoft.com would turn something up
Jeffrey : I setup the test that I mentioned above. Recovery model change and log truncation broke the log backups, the diffs still worked fine throughout.
SQL 2005 SP2 developer edition.
CREATE DATABASE TestingBackups
GO
use TestingBackups
GO
ALTER DATABASE TestingBackups SET RECOVERY FULL -- just to be sure
GO
CREATE TABLE Testing (id int)
INSERT INTO Testing VALUES (1)
BACKUP DATABASE TestingBackups TO DISK = 'D:\Develop\TestingBackups.bak' WITH INIT-- Base full backup for the subsequent diffs.
-- succeeded
INSERT INTO Testing VALUES (2)
GO
BACKUP LOG TestingBackups TO DISK = 'D:\Develop\TestingBackups_log1.trn' WITH INIT -- first of the log backups
-- succeeded
BACKUP DATABASE TestingBackups TO DISK = 'D:\Develop\TestingBackups_diff1.bak' WITH DIFFERENTIAL, INIT -- first diff still in full recovery
-- succeeded
GO
-- Now, truncate the log (note this section won't work on SQL 2008, since the log cannot be truncated)
INSERT INTO Testing VALUES (3)
GO
BACKUP LOG TestingBackups WITH TRUNCATE_ONLY -- bye bye log chain
GO
BACKUP LOG TestingBackups TO DISK = 'D:\Develop\TestingBackups_log2.trn' WITH INIT-- 2nd of the log backups, in full recovery, log truncated
-- failed
/*
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
*/
BACKUP DATABASE TestingBackups TO DISK = 'D:\Develop\TestingBackups_diff2.bak' WITH DIFFERENTIAL, INIT -- 2nd diff still in full recovery, log truncated
-- succeeded
GO
-- Now to simple recovery
INSERT INTO Testing VALUES (4)
GO
ALTER DATABASE TestingBackups SET RECOVERY SIMPLE
GO
BACKUP LOG TestingBackups TO DISK = 'D:\Develop\TestingBackups_log3.trn' WITH INIT -- 3rd of the log backups, in simple recovery
-- failed
/*
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
*/
BACKUP DATABASE TestingBackups TO DISK = 'D:\Develop\TestingBackups_diff3.bak' WITH DIFFERENTIAL, INIT -- 3rd diff in simple recovery
-- succeeded
GO
-- Now, back to full recovery
INSERT INTO Testing VALUES (5)
GO
ALTER DATABASE TestingBackups SET RECOVERY FULL
GO
BACKUP LOG TestingBackups TO DISK = 'D:\Develop\TestingBackups_log4.trn' WITH INIT -- 4th of the log backups, back in full recovery, no DB backup taken yet
-- failed
/*
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
*/
BACKUP DATABASE TestingBackups TO DISK = 'D:\Develop\TestingBackups_diff4.bak' WITH DIFFERENTIAL, INIT -- 4th diff back in full recovery
-- succeeded
GO
USE master
go
DROP DATABASE TestingBackups
GO
-- Now, the question, of course, is can we restore using those diff backups. A backup that's not restorable is not a backup.
RESTORE DATABASE TestingBackups FROM DISK = 'D:\Develop\TestingBackups.bak' WITH NORECOVERY -- the base full
GO
RESTORE DATABASE TestingBackups FROM DISK = 'D:\Develop\TestingBackups_diff4.bak' WITH NORECOVERY -- the last of the diffs
GO
RESTORE DATABASE TestingBackups WITH RECOVERY
GO
-- all succeeded
USE TestingBackups
GO
SELECT * FROM Testing
-- values 1.. 5 returned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 12:16 am
Gayle,
It seems that you tested an error message pertaining to the log backup...i'm having an issue with the DIFF backup. Unless i completely misunderstood your explanation. I'm currently running weekly fulls and daily t-log backups until this DIFF bug can be fixed.
December 30, 2008 at 12:23 am
biniyb (12/30/2008)
It seems that you tested an error message pertaining to the log backup...i'm having an issue with the DIFF backup. Unless i completely misunderstood your explanation.
The test was for Jeffrey, since we were disagreeing over whether a log truncation would break the diff backups. It wasn't aimed at you. Sorry if the post was confusing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 12:38 am
I found the bug that I was thinking about, but it's 2005 RTM, fixed by SP2
http://support.microsoft.com/kb/921106
If the diff succeeds on the sat, but fails on the sunday, I think you're going to have to trace everything that happens between the diff that succeeds and the diff that fails.
Perhaps, if it's possible, set up an adhoc schedule for the diff backups so that they run every hour between the sat diff (that succeeds) and the sun diff (that fails). That way you'll be able to narrow down the time frame where something happens and have less trace data to wade through.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 12:44 am
Gail, thanks for the test. It is interesting that the OP is getting the same error that you would get if you tried to perform a diff before ever performing a backup on that database.
The question I have is what else could cause a database to not have a current backup. Is this a bug that existed in an earlier version?
The error the OP is getting is definitely raised because the system does not think that database has a current backup. I really thought it would have been caused by the same thing that causes a transaction log to fail with the same type of message.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 12:54 am
Jeffrey Williams (12/30/2008)
The question I have is what else could cause a database to not have a current backup. Is this a bug that existed in an earlier version?
That's a bloody good question. Wish I had an answer. There are very few things that cause a full backup to be invalidated for the purposes of a diff.
The only ones I know of are:
-A bug in SQL 2005 RTM
-NTBackup
-The desktop version of the Store-It removable drives one-click backup software
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 1:07 am
Yeah - I can see that. Thanks for the pointer to the bug article - I knew I had seen something like that also, but since I no longer build any systems on anything less than SQL Server 2005 SP2 I won't see this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 5:55 am
try one thing take full backup first and then take differential backup manually if it works or not ...
now if it works then you make problem to schedule jobs or make maintenance plan whenever you make maintenance plan job related to that made ...now make one fresh maintenance plane for both full and differential than run that maintenance plan manually and check is that runing ok or not
sometimes user access may cause problem or space not available on the hard disk and also another thing is that sql server agent can't access path specified ... but do things that i told you and reply me ...we will solve your problem
Raj Acharya
December 30, 2008 at 9:34 am
Hello Everyone,
I installed SQL Server SP3 and the DIFF backup seemed to work. Maybe something in SP3 that was fixed from SP2. Thanks for all the advise in the past, greatly appreciated.
-Bini
October 30, 2009 at 1:27 pm
Hey All,
I have a solution, at least in my case.
I have Sql 2008 doing Full-Weely, Diff-Daily, Trn-MidDay
My Diff were failing the same way. I traced the problem to a piece of software called Jungle Disk. This software moves our Sql Backups offsite and stores them on Amazon S3 storage.
Inside the Jungle Disk app there was a setting "Backup In Use Files (NTFS Only)". I am fairly sure this setting was causing a VSS Snapshot or something to be done. This VSS snapshot was ERASING our entire backup history inside Sql Management Studio.
At any rate, I am not sure if it is Jungle Disk bug or way in which it interacts with VSS, but I disabled and solved my problem.
July 11, 2010 at 10:16 pm
I know this is an old thread, but the previous post from Jon Vickers is identical to our experience with MOZY. We're on 2005 SP2. Diffs ran fine (even with other fulls running in between in different directories!) until our offisite backup software ran with the "backup open files" option enabled.
Cheers.
*** Scratch that. Disabling Mozy open file support (ie, vss) didn't fix it. I had to disable Mozy completely and uninstall. All is well now ***
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply