SQL Backup, Diff, Log
js_0505
Posts: 8
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.
Comments
See here for some graphic details on the 3 backup types.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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 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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com