Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Full, Log & Differential Backups Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 2:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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
Post #1389580
Posted Wednesday, November 28, 2012 2:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:48 AM
Points: 323, Visits: 986
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1389594
Posted Wednesday, November 28, 2012 2:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 40,672, Visits: 37,137
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 2008, MVP
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

Post #1389595
Posted Wednesday, November 28, 2012 2:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:30 AM
Points: 2,128, Visits: 5,565
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/
Post #1389603
Posted Wednesday, November 28, 2012 3:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 40,672, Visits: 37,137
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 2008, MVP
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

Post #1389606
Posted Wednesday, November 28, 2012 3:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:30 AM
Points: 2,128, Visits: 5,565
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/
Post #1389607
Posted Wednesday, November 28, 2012 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 6,765, Visits: 14,429
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"
Post #1389656
Posted Wednesday, November 28, 2012 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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?
Post #1390033
Posted Wednesday, November 28, 2012 12:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 6,765, Visits: 14,429
what is the error you are receiving?

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1390036
Posted Wednesday, November 28, 2012 12:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 40,672, Visits: 37,137
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 2008, MVP
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

Post #1390040
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse