none
SQL Server Database backup - append, overwrite or separate files?

    Question

  • Hi,

    I am in the process of working out a backup strategy for an SQL database that uses the full recovery model and also looking for a better general understanding that I would be able to apply to future databases.

    I am clear (I think) about using full / differential / log backups. However I'm confused about backup sets and whether to use append to existing set or overwrite or whether to write to a new file each time.

    Say for the sake of argument I have decided to take a full backup every month, a differential backup every week and a log backup every day and with the backup files going to a network drive.

    If I used append for every backup, I presume I could get recovery up to the nearest day if needed. However the file for the backup set would soon become pretty huge as it would have a new copy of full database added to it each month, as well as everything else.

    If on the other hand, I used overwrite every time does this mean I lose everything apart from the last backup? If this is the case and my last backup was the log (the most likely scenario), does this mean the backup would be useless for recovery as I would also need the full backup and any log backups before this one (all of which will have been overwritten) for recovery to work?

    If I have got this right, neither strategy seems acceptable. Would it instead make more sense to overwrite when taking the full backup and then append when taking the differential and logs. Then I would in effect start a group of backups each month (I would have to accept that I would not be able to restore any further back than the last month).

    I have noticed on some posts people say about making a different file for each backup. This makes some sense as well - however I'm not clear at all in what I should shouldn't do - eg can all backups go in different files or should all log backups go in the same file etc. When dabbling with this on a practice database, I get a message saying media is only formatted to support one media family.

    Whilst I could probably work out what seems a sensible way forward ,bearing in mind I'm pretty new to SQL server, I think it would be sensible to get an idea of is how experienced DBA's would normally approach this. The part of backups relating to full backups, log backups etc seems very well documented on the web with plenty of advice for any novices but I haven't found anything that combines this with whether backups should append, overwrite or be on different backup files

    Any feedback welcome - even if it is just to direct me to a page / blog that clearly explains this!

    Thanks

    Wednesday, April 11, 2012 9:27 AM

Answers

  • Hi,

    This does really depend. I ideally want to have two days worth of backups available to me on disk if I can therefore I don't need to rely on other people to get the data back off tape for example. Obviously on VLDB's this can be an issue and it not normally possible.

    In regards to append, overwrite or multiple files I generally want to go with multiple files which are then backed up to tape. I don't want to append as I don't want to end up with a single large file or with a single file that has multiple backups in case something causes the file to be unreadable and therefore could cause me to loose multiple backups. This goes for full and differential backups.

    Transaction log backups can be numerous. It is not uncommon to end up running a transaction log backup every minute. That's 60 backups per hour and therefore 1440 backups over 24 hours. In this kind of scenario I don't generally want to have 1440 transaction log backups that I have to restore 1 at a time in order. Therefore I may tend to go for appending to a backup set but create a new backup set every 1 hour. Therefore I have 24 backup sets needed for restore and each set contains 60 minutes worth of transaction data.

    Therefore in a large system I would ideally like to have a full backup once per week, plus differential backups every other day with transaction log backups every XX minutes each day with XX minutes being the amount of data that the business can tolerate losing.

    As you can probably guess my recovery strategy is flexible and can be different at different sites and even different databases however I try to keep it consistent to make monitoring and recovery standardized across the databases and instances within the organization.

    You need to have a very good understanding of what each type of backup contains and how you need to use them during a restore. Whatever method you choose you need to test recovering the database regularly I like to do this at least once in ever 3-6 month period. There is nothing worst than walking into a DR scenario and find that your recovery is not possible as backups have failed for the last 6 months as sometimes corruption or data loss cannot be resolved and it is always better to restore rather than attempt a recovery with data loss for example.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, April 11, 2012 10:21 AM
  • Hi,

    Your backup strategy is very much dependent on RTO [Recovery Time Object] and RPO [Recovery Point Object].

    RTO decides how much downtime is acceptable to your business and RPO decides how much data loss is acceptable to your business.

    Keeping RTO & RPO in consideration and backup strategy is planned. In general DISK + Tape backup is much more feasible which ensures quick RTO & RPO.

    With regard to your question on Append, Overwrite,seperate files the answer is it depends. It depends on size of your databases, their criticality,

    how often data changes etc. etc.

    Append is generally good for small databases where there is not much changes happen and mostly database remain static. In such case only the changes after last backup will be backed to same file.

    Overwrite is generally used where generally there is lot of database change occurs and you have limited disk space for storing mulitple backup files on disk.

    Seperate files: This is generally used as this ensures the disk has multiple number of backup files available with timestamp depending on the backup retention period set.

    Of course if you had VLDB (data warehouse ) then your backup strategy should include FILE/FILEGROUP backup too.

    Hope this helps!!


    Regards, Vishal Srivastava

    Thursday, April 12, 2012 11:02 AM

All replies

  • Hi,

    This does really depend. I ideally want to have two days worth of backups available to me on disk if I can therefore I don't need to rely on other people to get the data back off tape for example. Obviously on VLDB's this can be an issue and it not normally possible.

    In regards to append, overwrite or multiple files I generally want to go with multiple files which are then backed up to tape. I don't want to append as I don't want to end up with a single large file or with a single file that has multiple backups in case something causes the file to be unreadable and therefore could cause me to loose multiple backups. This goes for full and differential backups.

    Transaction log backups can be numerous. It is not uncommon to end up running a transaction log backup every minute. That's 60 backups per hour and therefore 1440 backups over 24 hours. In this kind of scenario I don't generally want to have 1440 transaction log backups that I have to restore 1 at a time in order. Therefore I may tend to go for appending to a backup set but create a new backup set every 1 hour. Therefore I have 24 backup sets needed for restore and each set contains 60 minutes worth of transaction data.

    Therefore in a large system I would ideally like to have a full backup once per week, plus differential backups every other day with transaction log backups every XX minutes each day with XX minutes being the amount of data that the business can tolerate losing.

    As you can probably guess my recovery strategy is flexible and can be different at different sites and even different databases however I try to keep it consistent to make monitoring and recovery standardized across the databases and instances within the organization.

    You need to have a very good understanding of what each type of backup contains and how you need to use them during a restore. Whatever method you choose you need to test recovering the database regularly I like to do this at least once in ever 3-6 month period. There is nothing worst than walking into a DR scenario and find that your recovery is not possible as backups have failed for the last 6 months as sometimes corruption or data loss cannot be resolved and it is always better to restore rather than attempt a recovery with data loss for example.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, April 11, 2012 10:21 AM
  • Hi,

    Your backup strategy is very much dependent on RTO [Recovery Time Object] and RPO [Recovery Point Object].

    RTO decides how much downtime is acceptable to your business and RPO decides how much data loss is acceptable to your business.

    Keeping RTO & RPO in consideration and backup strategy is planned. In general DISK + Tape backup is much more feasible which ensures quick RTO & RPO.

    With regard to your question on Append, Overwrite,seperate files the answer is it depends. It depends on size of your databases, their criticality,

    how often data changes etc. etc.

    Append is generally good for small databases where there is not much changes happen and mostly database remain static. In such case only the changes after last backup will be backed to same file.

    Overwrite is generally used where generally there is lot of database change occurs and you have limited disk space for storing mulitple backup files on disk.

    Seperate files: This is generally used as this ensures the disk has multiple number of backup files available with timestamp depending on the backup retention period set.

    Of course if you had VLDB (data warehouse ) then your backup strategy should include FILE/FILEGROUP backup too.

    Hope this helps!!


    Regards, Vishal Srivastava

    Thursday, April 12, 2012 11:02 AM
  • Thanks for feedback. Yes this is helpful to hear how other people do this. I see the point about having transaction logs in the same set. As mentioned, I need to understand how I would do a recovery from these files. I will try this out on a practice database.
    Thursday, April 12, 2012 1:18 PM