July 3, 2008 at 6:18 am
If I have a backup schedule which takes a full backup nightly and tran log backups hourly, and then take an adhoc full db backup during the day (say to create a test database) and then delete this adhoc backup, does this break the chain from the original backup and tran log backups?
Thanks
Steve
July 3, 2008 at 6:25 am
No.
A full backup is similar to a snapshot, it's point in time. You could take a full backup 4 or 5 times a day and still use Transaction Logs without issue. However, only Transaction Logs backups that have been taken since the full backup can be applied.
I say that you are in the clear. 😎
Regards, Irish
July 3, 2008 at 6:35 am
Thought so.
The doubt cam from reading about SQL2005 backups:
Copy-Only Backups
Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, SQL Server 2005 introduces copy-only backups. These backups are independent of the regular sequence of SQL Server backups.
on the Microsfot website.
But if a normal database backup does not break the chain, is it not equivalent to a Copy-Only backup?!
July 3, 2008 at 6:39 am
If you take an ad-hoc backup during the day, all the transaction log backups after that backup are in relation to the ad-hoc backup. So, if you delete the backup, you will only be able to restore to the end of the most recent t-log backup previous to the ad-hoc backup. All the t-log backups after the ad-hoc are now worthless and your ability to recover to a point in time beyond the ad-hoc is gone until an additional full backup is taken.
So, to answer your question, all the backups previous to the ad-hoc are fine. However, if you have a failure 6 hours after you take the ad-hoc and that file is gone, you're going to lose 6 hours worth of data.
I don't advise deleting any full backups taken on production databases. Always put them into the same folder as the scheduled backups. This way, they will be managed / archived with the regulars and your retention period will be maintained.
You can refer to http://msdn.microsoft.com/en-us/library/ms190440.aspx for clarification.
Kyle
July 3, 2008 at 6:47 am
Well, I stand corrected.
Clearly I was incorrect given the information in the Microsoft Article (http://msdn.microsoft.com/en-us/library/ms190440.aspx).
I thought that the chain continued regardless of whether you took a Full backup in the middle of the day or not.
My apologies for misleading anyone.
Regards, Irish
July 3, 2008 at 6:53 am
Steve Hindle (7/3/2008)
does this break the chain from the original backup and tran log backups?
No. Tran log backups chain from another. Full backups do not truncate the transaction log, only a transaction log backup does that.
You can restore an earier full backup and apply transaction logs from then right up to your latest, regardless of what other full/diff backups you have taken
What normal backups reset is the differential base LSN which sets what full database backup the differentials are based off. It's only an issue if you have differential backups.
As an example, say you take a full backup on Sunday and a differential backup each weekday evening, and have log backups every hour.
If you have a failure on the thursday, you would restore the sunday full backup, then the wednesday night differential (as it is the latest one you have) and then the transaction logs one by one until you get up to date.
Now say that someone took an ad-hoc full backup wednesday morning, then deleted it. Now the differential taken wednesday night is based off the wednesday morning full backup, not the sunday full backup.
To restore now to thursday morning, you would restore the sunday full, the tuesday night differential (because the wednesday one is based off a full that has since been deleted), then all the transaction log backups since the tuesday night diff.
Make any sense?
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
July 3, 2008 at 6:56 am
Jeffrey Irish (7/3/2008)
I thought that the chain continued regardless of whether you took a Full backup in the middle of the day or not.
You are correct, it does. Only a log truncation or a switch to simple recovery mode breaks the log chain. (or a deleted log backup)
The relevant quote from the MSDN article:
Usually, a new log chain is only started when the database is backed up for the first time, or after the recovery model is switched from simple recovery to full or bulk-logged recovery.
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
July 3, 2008 at 6:57 am
I hope you aren't wrong Mr Irish!!
Take the following backup sequence:-
Full Backup 1
Txn Log Backup 1
Txn Log Backup 2
Txn Log Backup 3
Full Backup 2
Txn Log Backup 4
Txn Log Backup 5
Txn Log Backup 6
May be all I've learnt over the years is wrong, but in my book, you can restore either:-
Full Backup 1 followed by txn log backups 1-6
OR
Full Backup 2 followed by txn log backups 4-6
Are we getting crossed wires with things like filegroup and differential backups?
July 3, 2008 at 6:59 am
Gail,
That's how I thought it worked, but after reading the Microsoft MSDN for 2005 I was not sure
A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time, or after the recovery model is switched from simple recovery to full or bulk-logged recovery.
To restore a database up to the point of failure, the log chain must be intact. That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups.
Can you see how I would be a little confused. What you said makes sense, and is how I thought that it worked. In theory you could recover to a point in time by restoring Sunday nights full backup and then running all the Transaction Logs through Thursday morning then, regardless of any ad-hoc backups in between.
That's how I want it to work!! :w00t:
Regards, Irish
July 3, 2008 at 7:04 am
Jeffrey Irish (7/3/2008)
In theory you could recover to a point in time by restoring Sunday nights full backup and then running all the Transaction Logs through Thursday morning then, regardless of any ad-hoc backups in between.
Yes. Providing you have all of those transaction log backups, the recovery model has not been changed in the interum and no one has truncated the log.
What in the article confused you? I can try and clarify if you want.
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
July 3, 2008 at 7:12 am
I had this all sorted out in my head before reading the whole chain thing in the article. I understand the concept of chaining, but it was the portion that I quoted in a previous post that threw me.
I have recovered SQL 2000 and prior databases with Full Backups and Transaction Log Backups in the past, regardless of ad hoc backups in between. I guess it was the little seed of doubt I had from someone else refuting my post with the power of an article.
I just tested my theory on a development system, and it worked flawlessly. Full backup from Sunday and recovered to Tuesday 09:00 using just Transaction Logs.
Regards, Irish
July 3, 2008 at 7:19 am
Wow, had to prove it to myself. My understanding has been wrong for years...
I tested this using the following code:
CREATE DATABASE TestRecovery
GO
ALTER DATABASE TestRecovery SET recovery FULL
GO
USE TestRecovery
GO
CREATE TABLE Test (id INT)
GO
INSERT INTO Test (id) VALUES (1)
GO
BACKUP DATABASE testrecovery TO DISK='E:\TestRecovery_FirstFull.bak'
GO
INSERT INTO Test (id) VALUES (2)
GO
BACKUP LOG testrecovery TO DISK='E:\TestRecovery_FirstLog.trn'
GO
INSERT INTO Test (id) VALUES (3)
GO
BACKUP DATABASE testrecovery TO DISK='E:\TestRecovery_SecondFull.bak'
GO
INSERT INTO Test (id) VALUES (4)
GO
BACKUP LOG testrecovery TO DISK='E:\TestRecovery_SecondLog.trn'
GO
USE [master]
GO
DROP DATABASE TestRecovery
GO
RESTORE DATABASE TestRecovery FROM DISK='E:\TestRecovery_FirstFull.bak' WITH NORECOVERY
GO
RESTORE LOG TestRecovery FROM DISK='E:\TestRecovery_FirstLog.trn' WITH NORECOVERY
GO
RESTORE LOG TestRecovery FROM DISK='E:\TestRecovery_SecondLog.trn' WITH RECOVERY
GO
USE TestRecovery
GO
SELECT * FROM test
Clearly, Gail and Jeffrey are correct and I'm the one that should be apologizing. I've never not had the most recent full during a recovery based mostly on my misunderstanding. So, it was a self-perpetuating myth in my world.
Kyle
July 3, 2008 at 7:21 am
Jeffrey Irish (7/3/2008)
I had this all sorted out in my head before reading the whole chain thing in the article. I understand the concept of chaining, but it was the portion that I quoted in a previous post that threw me.
The file and filegroup backups could stand to be explained a bit better.
Basically, if you are using full/diff backups, you must hav an intact log chain (no truncation, no missing backups, no recovery model change) from the end of the full/diff backup that you are using.
If you are using file/filegroup backups, you must have a log chain intact from the first of the file/ilegroup backups that you are using to restore your database. (take this under possible correction, as I don't work with these types of backups)
Is that any better?
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
July 3, 2008 at 7:35 am
Kyle Neier (7/3/2008)
Clearly, Gail and Jeffrey are correct and I'm the one that should be apologizing.
No worries. We've all done similar or worse.
I appropriated your test code to show what doesn't work. Hope you don't mind
CREATE DATABASE TestRecovery
GO
ALTER DATABASE TestRecovery SET recovery FULL
GO
USE TestRecovery
GO
CREATE TABLE Test (id INT)
GO
INSERT INTO Test (id) VALUES (1)
GO
BACKUP DATABASE testrecovery TO DISK='c:\Temp\TestRecovery_FirstFull.bak'
GO
INSERT INTO Test (id) VALUES (2)
GO
BACKUP DATABASE testrecovery TO DISK='c:\Temp\TestRecovery_FirstDiff.bak' WITH DIFFERENTIAL
GO
INSERT INTO Test (id) VALUES (3)
GO
BACKUP DATABASE testrecovery TO DISK='c:\Temp\TestRecovery_SecondFull.bak'
GO
INSERT INTO Test (id) VALUES (4)
GO
BACKUP DATABASE testrecovery TO DISK='c:\Temp\TestRecovery_SecondDiff.bak' WITH DIFFERENTIAL
GO
USE [master]
GO
DROP DATABASE TestRecovery
GO
RESTORE DATABASE TestRecovery FROM DISK='c:\Temp\TestRecovery_FirstFull.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestRecovery FROM DISK='c:\Temp\TestRecovery_FirstDiff.bak' WITH RECOVERY -- this works
GO
USE TestRecovery
GO
SELECT * FROM test
USE [master]
GO
DROP DATABASE TestRecovery
GO
RESTORE DATABASE TestRecovery FROM DISK='c:\Temp\TestRecovery_FirstFull.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestRecovery FROM DISK='c:\Temp\TestRecovery_SecondDiff.bak' WITH RECOVERY -- this doesn't
GO
USE TestRecovery
GO
SELECT * FROM test
The errors that the second throws are:
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
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
July 3, 2008 at 7:53 am
Thanks you for all your replies.
I have also been experimenting...
I created:
Full1.Bak
Tran1.TRN
Full2.BAK
Tran2.TRN
using SQL2005. I deleted Full2.BAK and restored Full1.Bak, Tran1.TRN and then Tran2.TRN and this worked.
I'm still a tad confused?? I thought this would fail as the chain has been broken as a result of deleting Full2.BAK.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply