<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Full, Log & Differential Backups
19 posts, Page 1 of 2
1
2
»»
Full, Log & Differential Backups
Rate Topic
Display Mode
Topic Options
Author
Message
raotor
raotor
Posted Wednesday, November 28, 2012 2:16 AM
SSC-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
sanket kokane
sanket kokane
Posted Wednesday, November 28, 2012 2:42 AM
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323,
Visits: 960
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
GilaMonster
GilaMonster
Posted Wednesday, November 28, 2012 2:44 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639,
Visits: 29,893
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
Adi Cohn-120898
Adi Cohn-120898
Posted Wednesday, November 28, 2012 2:58 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:07 PM
Points: 2,018,
Visits: 4,911
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
GilaMonster
GilaMonster
Posted Wednesday, November 28, 2012 3:02 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639,
Visits: 29,893
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
Adi Cohn-120898
Adi Cohn-120898
Posted Wednesday, November 28, 2012 3:05 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:07 PM
Points: 2,018,
Visits: 4,911
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
Perry Whittle
Perry Whittle
Posted Wednesday, November 28, 2012 5:22 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 5,201,
Visits: 11,148
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
raotor
raotor
Posted Wednesday, November 28, 2012 11:58 AM
SSC-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
Perry Whittle
Perry Whittle
Posted Wednesday, November 28, 2012 12:04 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 5,201,
Visits: 11,148
what is the error you are receiving?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Post #1390036
GilaMonster
GilaMonster
Posted Wednesday, November 28, 2012 12:06 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639,
Visits: 29,893
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 »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
