SQL Backup, Diff, Log

js_0505

Okay I am currently doing a full backup every sunday and a differential backup every 15 minutes. I am sightly confused why some do log shipping between differentials and how it is benficial? A differential captures data that has changed since last differential correct? So what is the point of logging? To give it a little extra data incase of failure? Also when a differential overlaps the next scheduled differential then you loose that time to create a updated differential and then logging would get messed up as well correct? I am trying to implement the best possible solution for my group. Currently we are on a 10mb line that is shared and 2 gig differentials seems to be common and take an hour or 2 to move over to our local bu server. What can I do to increase performance or lower the size of the files that are being moved? I amusing SQL Backup 6.1 for all of my bu's etc. BTW on my backup server I would like to have the database live at all times with the most recent data moved over. Thanks for your help.

petey

A differential captures data that has changed since last differential correct?

No, a differential backup captures data that has changed since the last full backup. So in your case, it's most likely that Monday's differential backups are smaller compared to Saturday's, assuming your database is one that has new trxs added daily.

See here for some graphic details on the 3 backup types.

js_0505

So given the limited bandwidth because i am moving from server a in fl to server b tx, then I should keep a full bu going on sunday midnight and then what would you recommend for differential backups and transaction logs? If i create the jobs and set them all to start at midnight tonight, won't the transaction logs overlap between the full and differentials? The logs will move over to server b quicker then the differential. I am a little lost there on receiving the transaction logs while the differential bu is moving across the network. Thank you for your assistance.

js_0505

Also, if i decide to include transaction logging, I will not be able to use the bu db because I would have to set the recovery to simple so there will be only a particular period of time that I can read data from the backup? Lastly, is there a way I can program red gate to restore the most recent differential automatically as a job. Like I would like the db to restore every hour with the most recent differential file. Right now they naming is automatic.

ChrisAVWood

I am assuming that you are running SQL2005 or SQL2008 not SQL2000.

Remember that a full backup does not flush the transaction log and I believe the same would be true of a differential backup.

Unless your database is in the simple recovery model your log must be increasing in size without the log backup.

To restore a database you need the last full followed by the last differential followed by the log backups run after the differential.

HTH

Chris

Eddie D

Thank you for your post into the forum.

The answer to question depends upon the importance placed upon the data in your database. This drives the decision on the types of backup you wish to perform.

For a database whose data changes rarely or a small number of transaction occur per day, having the database in Simple Recovery model and performing a weekly Full Backup and a Differential Backup daily maybe sufficient.

For a busy database that has many transaction a day, like an 'Orders' database and recovery to a point in time is required, having the database in the Full Recovery model will be required. One possible simple backup strategy would to perform a Full Backup twice a week, Differential Backup twice a day and Transaction Log backups every 15 minutes. By performing a transaction log backup, the DBA will be able to perform a point in time recovery.

Another important point to consider is how quickly you wish to recover lost data and / or how much data are you willing to lose.

SQL Server records every transaction in a write-ahead transaction log. The transaction log is a storage area that automatically tracks the changes to the database. The transaction is recorded to the transaction log before it is committed to the database.

When using the Simple Recovery model, SQL Server itself manages the transaction log, the DBA can only perform Full and Differential Backup tasks. To recover the data, the DBA needs to restore the last Full Backup followed by the last Differential Backup. Therefore the database can only be recovered to the time of last Differential backup, so any transaction performed after the Differential backup may be lost.

Using the Full Recovery, the DBA needs to administer and manage the transaction log. When you backup the transaction log, the backup stores the changes since the last transaction log backup and then truncates the log, which clears out transaction that hve been committed. To recover the database you would need to restore the last Full Backup, the last Differential Backup followed by an unbroken chain of log backup taken since the last differential backup.

By performing regular log backups, you improve the performance of your database. If a transaction log backup has never been performed, it is possible for your transaction log file to keep growing until you run out of disk space.

You are incorrect in your analysis regarding a Differential Backup:

A differential captures data that has changed since last differential correct?

A Differential is all the changes committed to a database since the last Full Backup. Therefore the size of each Differential Backup file continues to grow until the next Full Backup is taken.

Log Shipping is one possible solution for providing High Availability solution, by log shipping the transaction log to a standby server, you have an up to date copy of your database. In the event of failure of the primary production database. You can bring the secondary database on line to take over from the failed primary.

Many Thanks
Eddie