August 8, 2013 at 9:33 am
Edit Out at user request 😎
August 8, 2013 at 9:35 am
Hi,
Full backup or diff backup has no effect on log chain and thus has no effect on log backups. You can use any full backup and consequent log backup post that full backup to do a point in time restore
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:39 am
Differential backup is dependent on previous full backup. Log backup is dependent on any full backup as you can restore the transaction log backup post that used full backup
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:39 am
If your backup solution/server version allows you to restore transaction logs created after a full backup to an earlier full backup file without having the differential backup or full backup then you are right.
Mine does not do this.
The differential backup is the key here. You can't use any differential backup created after a missing/deleted full backup. Not unless the missing/deleted full backup was created with the copy_only option. If you have a full backup from a year ago, and all of the transaction log backups taken since then, and the recovery model has never changed, you could still restore the database up to and including the last transaction log backup. It would be a lot of work and very time consuming, but possible.
August 8, 2013 at 9:41 am
PHYData DBA (8/8/2013)
stobe (8/8/2013)
PHYData DBA (8/8/2013)
stobe (8/8/2013)
Joie Andrew (8/8/2013)
Nice article. One thing though. You list this:Myth 6: Full and differential backup breaks the log chain
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I don't think this is true for full and transaction log backups. If I take a full backup, then some transaction backups, then another full backup, and a another set of transaction log backups, I can restore the first full backup and then all the transaction logs. I guess it breaks the chain of differential backups though?
The Scenario that happens every day is this. The production database is being backed up by a job or some backup solution to a share local or offsite dedicated to storing backup files.
A user connects to the SQL server and runs a normal Full backup and stores this backup to a different location for restoration there, no copy of this backup is stored in the regular location.
Tran log backups continue to run all day as if no full backup had been created.
An outage occurs that requires the DBA to restore the database to a point in time after the backup that was not stored in the usual location was created. This can not be done normally with the regular restore process because the Backup chain was broken and the tran log files do not match the full backup that is in the regular location.
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
Hmmm, the thread at http://www.sqlservercentral.com/Forums/Topic956447-357-1.aspx seems to contradict that.
If your backup solution/server version allows you to restore transaction logs created after a full backup to an earlier full backup file without having the differential backup or full backup then you are right.
Mine does not do this.
Are you using a 3rd party tool to do your backups per chance? I'm pretty sure the myths being discussed are in relation to how SQL Server works, a 3rd party tool could essentially be written to do any number of things but that's not SQL Server's fault.
August 8, 2013 at 9:41 am
Hi Chuck,
Absolutely right, that's what my article says. I think few myths have been cleared today!!
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:44 am
Hi,
Even 3rd party tools works on the same logic hence it valid for all backups.
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:45 am
chuck.hamilton (8/8/2013)
If your backup solution/server version allows you to restore transaction logs created after a full backup to an earlier full backup file without having the differential backup or full backup then you are right.
Mine does not do this.
The differential backup is the key here. You can't use any differential backup created after a missing/deleted full backup. Not unless the missing/deleted full backup was created with the copy_only option. If you have a full backup from a year ago, and all of the transaction log backups taken since then, and the recovery model has never changed, you could still restore the database up to and including the last transaction log backup. It would be a lot of work and very time consuming, but possible.
I would have to agree that this has worked this morning during a quick test on my 2008 R2 and 2012 servers.
My 2005 server gave me a weird error about missing the log tail that went away when I used the "Missing" Full Backup file.
This was only for one database that had a schema change that occurred so perhaps that is all part of it.
I guess we have a Myth here after all. Not certain if it is a version specific Myth or not.
August 8, 2013 at 9:53 am
chuck.hamilton (8/8/2013)
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
I dont think thats entirely true but I'll have to test it to see.
In the scenario you describe, restoring a differential backup taken after an adhoc full backup would not work, but restoring log backups should work just fine. Otherwise log shipping would break the first time another full backup was taken after the secondary database was created.
Great point. Actually had an issue a long time ago with log shipping like this. Support blamed it on a full backup that was saved to a different location. Their solution was to re-create the destination database and re-start the log shipping. All this was on SQL 2000 and the log shipping import had been down for so long there was no other option that made sense. It was faster to restart than ship all the missed logs.
August 8, 2013 at 10:05 am
PHYData DBA (8/8/2013)
chuck.hamilton (8/8/2013)
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
I dont think thats entirely true but I'll have to test it to see.
In the scenario you describe, restoring a differential backup taken after an adhoc full backup would not work, but restoring log backups should work just fine. Otherwise log shipping would break the first time another full backup was taken after the secondary database was created.
Great point. Actually had an issue a long time ago with log shipping like this. Support blamed it on a full backup that was saved to a different location. Their solution was to re-create the destination database and re-start the log shipping. All this was on SQL 2000 and the log shipping import had been down for so long there was no other option that made sense. It was faster to restart than ship all the missed logs.
I found and checked my notes about this example/issue and have to say it was caused by a differential backup being added. The previous DBA changed the nightly backups to differential and then a full once a week. I guess he never checked to see how this affected the log shipping partner before he left the company.
August 8, 2013 at 10:17 am
Myth 6: Full and differential backup breaks the log chain
Truly a myth.... since tran log backups are only linked to the last tran log backup the only way to break the chain would be to loose the first full and first tran log backup made since the database recovery option was updated.
Found a way to even test this quickly on a SQL 2000 server. Worked as advertised.
Not sure if replication or Log shipping make any differences.
One very important note:
It is at least 100% faster and takes at least 200% less disk space to restore from the last full backup than from the previous full backup and the transaction log backup that occurred after that full backup. This difference in restore time and disk space required could be what caused this Myth.
EDIT - This time and space thing may not be true if you use Bulk Logged recovery model. Only tested with Full.
August 8, 2013 at 11:38 am
August 9, 2013 at 11:56 am
PHYData DBA (8/8/2013)
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
Joie Andrew (8/8/2013)
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I suggest that it would be a great idea for PHYData DBA and Joie Andrew to go back to the first page of this discussion and edit their earlier comments. Someone reading them without going further in the chain will be sadly misinformed.
August 9, 2013 at 11:59 am
Festeron (8/9/2013)
PHYData DBA (8/8/2013)
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
Joie Andrew (8/8/2013)
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I suggest that it would be a great idea for PHYData DBA and Joie Andrew to go back to the first page of this discussion and edit their earlier comments. Someone reading them without going further in the chain will be sadly misinformed.
Great Idea.... Done... Unfortunately I can't edit where I was quoted.
August 9, 2013 at 11:59 am
I agree!! :-):-):-)
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply