August 4, 2014 at 3:04 pm
We are running SQL Server 2012 on Windows Server 2008. Just wondering what type of actions would break the backup-chain or backup sequence? For instance, if you have tlog backups being taken every 10 minutes and you stop the SQL Server Service for say 30 minutes. Would this action break the backup chain? Or would everything return to normal once the SQL Server Service is restarted?
Thanks in advance, Kevin
August 4, 2014 at 3:14 pm
To break the backup chain:
- Switch to simple recovery and back to full
- Delete a log backup file (the .trn)
- Delete the transaction log
- Revert to a snapshot
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
August 5, 2014 at 12:34 am
GilaMonster (8/4/2014)
To break the backup chain:- Switch to simple recovery and back to full
- Delete a log backup file (the .trn)
- Delete the transaction log
- Revert to a snapshot
My additions to this list:
- Using 3rd party Backup software, not always but there's a good chance for it to happen.
- Taking a FULL backup without COPY_ONLY
Edit: Hit the Post button too soon 🙂 Stopping and starting SQL Server will not break your log backups.
August 5, 2014 at 1:01 am
MikaS (8/5/2014)
- Using 3rd party Backup software, not always but there's a good chance for it to happen.
That won't break the backup chain. Can make it hell to restore if some log backups are native and some are 3rd party.
It can also sometimes make diffs fail.
- Taking a FULL backup without COPY_ONLY
NO!
Full backups do not and never have broken the log chain. The reset the differential base, that is all. Copy_Only allows for a full backup to not reset the differential base.
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
August 5, 2014 at 1:52 am
NO!
Full backups do not and never have broken the log chain. The reset the differential base, that is all. Copy_Only allows for a full backup to not reset the differential base.
That was poor wording on my part, thank you for the correction. What I wanted to say is that if someone takes a Full backup outside the regular backup schedule without using the COPY_ONLY, there's a possibility of ending up with tlog backups that cannot be used to restore your database. If the second full backup is not available (it's moved and/or deleted) the log backups taken after that cannot be restored anywhere.
August 5, 2014 at 1:56 am
MikaS (8/5/2014)
NO!
Full backups do not and never have broken the log chain. The reset the differential base, that is all. Copy_Only allows for a full backup to not reset the differential base.
That was poor wording on my part, thank you for the correction. What I wanted to say is that if someone takes a Full backup outside the regular backup schedule without using the COPY_ONLY, there's a possibility of ending up with tlog backups that cannot be used to restore your database. If the second full backup is not available (it's moved and/or deleted) the log backups taken after that cannot be restored anywhere.
Still wrong.
Transaction log backups don't have an effect on the full backup, so you can take as many fulls in either copy only or not and it wont affect your log chain.
The only thing that relies on a specific full backup (none copy only) is your differential backups.
August 5, 2014 at 2:19 am
MikaS (8/5/2014)
What I wanted to say is that if someone takes a Full backup outside the regular backup schedule without using the COPY_ONLY, there's a possibility of ending up with tlog backups that cannot be used to restore your database.
There is absolutely zero possibility of that happening.
If the second full backup is not available (it's moved and/or deleted) the log backups taken after that cannot be restored anywhere.
Yes, they can (and it's trivial to test and prove). Full backups DO NOT and never have had any effect at all, whatsoever on the log backup sequence.
The only thing that Copy_Only on a full backup does is not reset the differential base
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
August 5, 2014 at 2:47 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply