locked
Differences between differential database backup and Transaction log backup RRS feed

  • Question

  •  

    Hi All,

    I use SQL Server 2005 for my MOSS Portal.

    I took my full  DB backup 10 days before. Now I want to take the bakcup again. But since I need not take full backup of the DB I thought I'll take Transaction log backup. In this context can some one tell me whats the exact difference between differential backup and transaction log backup?.

     

    I feel transaction log backup will be smaller in size than Differential backup.

     

    And also I need to send this backup to my onsite guys. Can some one tell me if I send this transaction log separately will they be able to restore it?

     

    I think I need to take the transaction log backup on the full backup file I took 10 days before.In that case I think I'll have to unncecessarily send a huge file.

     

    Could some on suggest me on which backup I should take and also which backup I should send to the onsite guys?

     

    Friday, August 8, 2008 12:36 PM

Answers

  • I think it would be more comfortable to send a full backup if you dont have any problems with the size of the database. If you are uploading the database to an FTP site you might want to keep the size minimum. Otherwise, it is easier to send a full backup and chances of getting a phone call with 'Hey, it is giving an error!' may be lesser with a full backup.

     

    On the otherhand, sending differential backups or trn logs might be easier for you if you have issues with the size of the file. But restoring them needs more attention. You need to make sure that the files are restored in the correct order etc.

     

    These are just hints. I think you are the best person to decide what should be done here. I am pretty sure you can take the right option that suites your specific situation and environment.

     

    regards

    Jacob

    Monday, August 11, 2008 4:27 PM

All replies

  • I think the answer depends on the level of High Availability that your application wants to maintain. Usually, people use a combination of full backp, diff backup and log backups.

     

    For example, you may choose to take a full backup every week, differential backup every day and TRN LOG backups every 15 mins. This would you to restore database to the specific time of failure. If the primary database fails, you can restore the most recent full backup, the last differential backup and all the trn log backups after the last diff backp was taken.

     

    These MSDN topics might help to understand these backup methods in detail.

    Differential Database Backups

     
    Regards
    Jacob
    Friday, August 8, 2008 6:00 PM
  • Thank you Jacob,

    Then I can take any kind of backup for my requirement. since I have one SQL server with one FrontEnd, I feel I can take either differential or Transaction log backup , Am I correct?

     

    Then one more thing, can you suggest me which backup I should send to Onsite? Will they be able to restore only the transaction log backup? or I need to take backup on my full DB file ?

     

    I'll have to send it by this week end.. Smile

    Thank you.

     

    Monday, August 11, 2008 5:01 AM
  • I think it would be more comfortable to send a full backup if you dont have any problems with the size of the database. If you are uploading the database to an FTP site you might want to keep the size minimum. Otherwise, it is easier to send a full backup and chances of getting a phone call with 'Hey, it is giving an error!' may be lesser with a full backup.

     

    On the otherhand, sending differential backups or trn logs might be easier for you if you have issues with the size of the file. But restoring them needs more attention. You need to make sure that the files are restored in the correct order etc.

     

    These are just hints. I think you are the best person to decide what should be done here. I am pretty sure you can take the right option that suites your specific situation and environment.

     

    regards

    Jacob

    Monday, August 11, 2008 4:27 PM
  •  

    Thanks Jacob.

    I think I'll send the full backup.

    Tuesday, August 12, 2008 12:15 PM
  • To answer the original question:

    A full backup copies all pages in the database that are in allocated extents.

    A differential backup copies all pages which have been modified since the last full backup.

    A transaction log backup copies the contents of the transaction log since the last transaction log backup.

     

    T-log backups are fundamentally different because instead of just taking a snapshot of the database at a point in time, they record the changes as they happened to the databse, and allow you to replay them.

    This allows far more granularity in restore because you can choose to stop applying the log backups at a specific time (say just before someone accidentally did that DROP TABLE...)

    T-log backups only work if the database is in full recovery mode.

    If the database is in full recovery mode, the transaction log must retain all log records until the next log backup.  So, if you don't back up the log, it will continue to grow until either you back it up, or it runs out of disk.

     

    • Proposed as answer by SQLChamps Wednesday, December 16, 2015 8:28 AM
    Thursday, September 11, 2008 6:00 PM
  • Let me tell you the brief about Differential and Transaction Log Backup:

    Differential Backup will backup the entire data from the data file after the changes made by the full backup or recent full backups. So, the size of the differential backup is much smaller than the full but, it is quit bigger than the transaction log backup. In fact in real time scenarios we use differential backup on daily basis so as to reduce the time consumption and size of the disk despite, of taking the daily full backup (If the database size is big).

    Transaction log will backup only transaction logs (current running transaction information) from the log file. So, this T-log backup will run after the changes made by full or Differential backup occurs or we can say it after the recent backups and the size of transaction log backup is much smaller than full and Differential. In real scenarios will set the transaction log for 15 minutes.

    As my suggestions you can go with the Full backup what you have taken 10 days ago or Else take again full, Differential and Transaction log backup and sent it across to the online team which is easy to restore the database.

    But before sending check the backups using RESTORE VERIFYONLY T-SQL statement.

    Wednesday, December 16, 2015 8:47 AM