SQL Backup, Diff, Log

Comments

• petey Redgate › Posts: 2,358 New member

  September 9, 2009 11:28AM

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• js_0505 Posts: 8

  September 9, 2009 11:38AM

  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.

  0 · Share on Twitter
• js_0505 Posts: 8

  September 9, 2009 11:41AM

  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.

  0 · Share on Twitter
• ChrisAVWood Posts: 361

  September 9, 2009 11:47AM

  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

  English DBA living in CANADA

  0 · Share on Twitter
• Eddie D Redgate › Posts: 1,807 Rose Gold 5

  September 9, 2009 12:05PM

  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

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter