September 2, 2010 at 2:08 pm
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!
September 2, 2010 at 2:23 pm
You can also implement CDC - change data capture. It will write all changes (updates, delets, inserts) to another table.
September 2, 2010 at 2:33 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 2, 2010 at 2:53 pm
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!
September 2, 2010 at 2:57 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 4:11 pm
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.
September 2, 2010 at 11:38 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply