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

Incremental Backups for SQL 2008? Expand / Collapse
Author
Message
Posted Thursday, September 02, 2010 2:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
I recently inherited a decently sized data base (80+ GB with data going back ~20 years). New data is added to the DB almost every day - which includes loading data files and adding to indices. Occasionally there are table additions, etc.

I'm now being asked to store incremental changes to the DB off-site once a week - preferably by writing to a DVD. What is the best way to go about doing this?

I guess I'm looking for something where if I took a complete snapshot of my DB today, then next week I could save only the differential between that snapshot and the most recent DB... which would (I assume) fit easily onto a DVD.

Any ideas? I've never had to deal with managing back-ups, so this is new to me.

Thanks!


Post #979881
Posted Thursday, September 02, 2010 2:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 360, Visits: 1,136
You can also implement CDC - change data capture. It will write all changes (updates, delets, inserts) to another table.
Post #979897
Posted Thursday, September 02, 2010 2:33 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
There are three types of backups available in SQL Server - and they all work in relationship with each other.

First, you have a full backup - which is required to restore a database, at a minimum.

Second, you have differential backups - which backup the changes in the database since the last full backup.

Finally, you have transaction log backups. This type of backup does not backup the database, it backs up the transaction log - which is a record of all modifications since the last transaction log backup was taken.

A normal schedule of backups could be:

Full Backup Nightly
Transaction Log backups every hour (or as often as every 10 minutes).

In the above scenario, recovery is done by restoring the latest full backup and every transaction log backup that was completed after the full backup completed.

To reduce the number of transaction log backups to be recovered, you could add in differential backups. So, that schedule could look like:

Full backup nightly
Differential backups every 12 hours
Transaction log backups every hour

Recovery in this scenario would be to restore the latest full backup, the latest differential backup following the full backup, and all of the transaction log backups that completed after the latest differential completed.

How you build your backup scheme is going to depend upon your business requirements for recovery. How much data loss can you support in the event of a server crash? How much time do you have to recover? etc...

If you need to be able to fit the backups on DVD - I don't see how that is going to work. Without the full backup, you cannot restore the system and having a differential backup or log backups available won't help.

If you need to save space, look at Redgate's SQL Backup tool or Quest's Litespeed, or you can look at Hyperbac to compress the backup files.



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #979906
Posted Thursday, September 02, 2010 2:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
Jeffrey -

This is a pretty low priority DB in terms of time for recovery, and in terms of threshold for data replace-ability. And there are few enough changes made each week that backing up weekly is probably sufficient.

Thanks for explaining the difference between the full backups, differential backups, and transaction logs.

That helped clarify things - and I think I really only need to be working with a full backup (which can be stored offsite on a portable HD), and then write the differential backups weekly to a DVD.

That way, if something happens - I can restore the DB to the most recent differential backup using just the full backup and the most recent differential backup, right?

Thanks again!
Post #979921
Posted Thursday, September 02, 2010 2:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
bkmooney (9/2/2010)
That helped clarify things - and I think I really only need to be working with a full backup (which can be stored offsite on a portable HD), and then write the differential backups weekly to a DVD.

That way, if something happens - I can restore the DB to the most recent differential backup using just the full backup and the most recent differential backup, right?


Exactly correct. You'll want to 'reset' your full backup now and then as well. Most shops usually full backup on a weekend and differential through the week. I personally wouldn't want to need to deal with more then 10 differentials.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #979925
Posted Thursday, September 02, 2010 4:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 32,781, Visits: 14,942
I'd agree with Craig. Too many differentials increases your effort, and your risk. If that full cannot be restored for some reason, then you could lose everything.

At the very least, I'd run a full a month, run that offsite, and then move your diffs offsite. Note that I wouldn't try to write a backup to a DVD. I'd write it to disk, then copy to a DVD.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #979961
Posted Thursday, September 02, 2010 11:38 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 482, Visits: 512
Hi, Correctly said by members.

the ideal backup schedule should be
Full backup = Once in week ( preferably on weekends)
Differential Backup = Daily
Transaction log backup = throughout the day ( preferably the backup schedule should be in between 15-30 minutes.

It is also recommended to have backup first to disk then copy to tape / burn the DVD.
Post #980040
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse