Differential Backup Size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Differential Backup Size

16 posts, Page 1 of 2

1 2 »»

Differential Backup Size

Rate Topic

Display Mode

Topic Options

Author

Message

ESAT ERKEC

Posted Tuesday, March 16, 2010 12:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 222, Visits: 707

I have maintance plan. When I rebuild Index my Diffrential backup size is enormous (very big) what can I do.

P.S. = I take full backup after rebuild index finished but When the rebuild index is going the diffrential backup is always grow and grow

Post #883566

GilaMonster

Posted Tuesday, March 16, 2010 1:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

If you're rebuilding all your indexes, you're modifying just about the entire database, hence any differential run after that, before the next full backup will be large, because they contain changes since the last full backup.

How long does index rebuild take and how often are your diff backups that you say backups during the rebuild are large?

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 #883590

ESAT ERKEC

Posted Tuesday, March 16, 2010 9:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 222, Visits: 707

My rebuild index taken about 5 hours and my diffrantial backup runs about per one hour about that time
diff backup starting to grow up i think maybe ı can write a query if index plan is running does not get diff backup bu i think it can be another solution

Post #883875

Brandie Tarvin

Posted Tuesday, March 16, 2010 12:50 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 6,664, Visits: 5,690

Ouch!

Might I recommend only using Differential on a daily basis (not an hourly) and backing up the Transaction Log on an hourly basis instead?

I've never seen Differentials as an "hourly" type of backup. Others may have different opinions, but Differentials, IMHO, should be used so you don't have to have a daily FULL backup if your database is large.

If I'm misunderstanding your backup plan, I apologize.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #884114

GilaMonster

Posted Tuesday, March 16, 2010 1:14 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

Why are you taking differential backups every hour?

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 #884135

ESAT ERKEC

Posted Tuesday, March 16, 2010 11:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 222, Visits: 707

If I lost my Db I have only one hour ı data lost and my recovery model is simple

Post #884411

vidya_pande

Posted Tuesday, March 16, 2010 11:43 PM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 04, 2012 5:00 PM
Points: 891, Visits: 225

If your database is critical. It is recommanded to have database in full/bulked log recovery model. And plan for log backups. here you recover up to last point of failure as well.

Taking up Differential backups so frequently may be costly operation as it takes differential data from last full backup. (Not from last differential backup)

Post #884416

SQLRNNR

Posted Tuesday, March 16, 2010 11:50 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Change to Full recovery mode and run transaction log backups. With transaction log backups you can recover to the point just before failure. That is much more acceptable than losing 1 hour of data.

Using differential backups in this fashion is inefficient and costly.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #884418

ESAT ERKEC

Posted Tuesday, March 16, 2010 11:52 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 222, Visits: 707

if ı make it my ldf file size is very big

Post #884419

SQLRNNR

Posted Tuesday, March 16, 2010 11:56 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

ESAT ERKEC (3/16/2010)

if ı make it my ldf file size is very big

That's why you take transaction log backups. If you don't backup the transaction log then it will grow. The ldf is your transaction log and thus contains the information you will need to perform a point in time recovery and thus minimize data loss.

Post #884420

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions