SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full, Log & Differential Backups


Full, Log & Differential Backups

Author
Message
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Hello,

I've started out testing how a database using the full recovery model is backed up starting with a full database backup and subsequent regular log backups and believe I understand the basic principle.

i.e the full DB backup is the basis or foundation of the backup chain and the subsequent log backups form the linked chain allowing point-in-time recovery to any period falling within that chain.

So, my questions are:

1. Why perform a differential database backup as opposed to another log backup?
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?



Regards

Steve
sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1022
1) Space Issue
2) Imagine if you have 100 of transactional log backup after full backup.Now if you want to restore database or in case of disaster ,you need to apply all the 100 transactional log backup files after full backup ,that time consuming ,
thats why frequently full and differential backups

-----------------------------------------------------------------------------
संकेत कोकणे
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87205 Visits: 45271
raotor (11/28/2012)
1. Why perform a differential database backup as opposed to another log backup?


Speed of restores. When restoring a diff, you just need the latest one. When restoring logs, you need to restore all log backups since the full/diff.
It can be the difference between restoring 1 full, 1 diff and 10 log backups vs 1 full and 40 log backups for eg.

2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?


Full backups don't break the log chain, so the question is moot.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6506
To add a bit about the second question. At Pass summit 2011 Paul Randle had a presentation that he told about a bank that had one old full backup that they've done and then thousands of log backups that were done for few years (yes years, this is not a type nor a mistake). After few years without full backup or differential backup they had to do restore. They found themselves restoring few thousands log backups. Amazingly all the files still existed and were in good condition, so the chain didn't break. Unfortunately the whole process of restore took them 3 days. During that time they were close for business.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87205 Visits: 45271
Adi Cohn-120898 (11/28/2012)
Unfortunately the whole process of restore took them 3 days. During that time they were close for business.


Amazing that they reopened afterwards. When I worked for a bank, 3 days downtime (no business at all) and we probably wouldn't have had a business to recover.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6506
After a short time (don't remember if he said how long), they ran out of business.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19962 Visits: 17242
raotor (11/28/2012)
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?

Full backups DO reset the Differential Base LSN, so the only chain they break is for differential backups not for transaction logs.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
raotor
raotor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 239
Perry Whittle (11/28/2012)
raotor (11/28/2012)
2. Why ever destroy the existing chain by performing a second full backup other than for purely space or management reasons?

Full backups DO reset the Differential Base LSN, so the only chain they break is for differential backups not for transaction logs.


I have a test scenario running where I'd performed an initial full backup last week and then performed a log backup every hour.

Today I performed a second full database backup after thinking I wouldn't destroy the chain and now I cannot restore the database to a point in time before the most recently performed full backup done today, so hasn't the chain been broken if that's the case?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19962 Visits: 17242
what is the error you are receiving?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87205 Visits: 45271
raotor (11/28/2012)
Today I performed a second full database backup after thinking I wouldn't destroy the chain and now I cannot restore the database to a point in time before the most recently performed full backup done today, so hasn't the chain been broken if that's the case?


Are you using the correct full backup? (the one taken last week)?
Has the recovery model been changed at any point since that full backup last week?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search