none
"Append to Media" Vs "Overwrite an existing media" in SQL Server Database backup

    Question

  • Dear All,

    I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
    currently I am using Append to Media option, and every day backup, I see my backup files growing in size.

    can someone give me nice example about these two options,

    Thanks,
    Monday, September 24, 2007 2:10 PM

Answers

  •  

    The risk is that if you overwrite the previous backup, and then the current one fails for any reason you are left with no backup for your database.  Not a good situation.

    What I'd recommend instead, which is a fairly common practice, is to do each backup to a new file which incorporates the current date/time as part of the filename.

    That way you always have unique filenames, you can easily prune out old backups, and it's obvious which backup is from what time.

    Friday, September 28, 2007 11:21 PM
    Moderator

All replies

  • When you append to the media the previous backups also remain in the backup file.  One backup file can contain numerous backups.  When you overwrite the backup file is recreated every time so it contains just one file. 

     

     

    WesleyB

    Visit my SQL Server weblog @ http://dis4ea.blogspot.com

    Monday, September 24, 2007 4:05 PM
  • Thanks for your reply.

    That means, overwrite backup media will occupy less space, then why would i choose Append to media,

    any risk involved in overwriting option ?, becuase every body recommends appending, why not ovewritting.

    Thanks,
    Monday, September 24, 2007 4:12 PM
  • It will definitely use less space.

    I do not see the risk of overwriting your backup unless you need a full backup that was taken a couple of days earlier to be accessible.  In most cases I suppose you will only be interested in the latest version of your database and not the one you had 3 months ago.  For the 'special' occasions where a specific version may be required I would simply take a separate backup.

     

    WesleyB

    Visit my SQL Server weblog @ http://dis4ea.blogspot.com

    Tuesday, September 25, 2007 6:59 AM
  • Thanks, your advice is helpful,

     

    but if i want to be able to recover point in time, then I have to go with append to media, along with Transaction long back , RIGHT?

     

    Tuesday, September 25, 2007 1:32 PM
  •  

    The risk is that if you overwrite the previous backup, and then the current one fails for any reason you are left with no backup for your database.  Not a good situation.

    What I'd recommend instead, which is a fairly common practice, is to do each backup to a new file which incorporates the current date/time as part of the filename.

    That way you always have unique filenames, you can easily prune out old backups, and it's obvious which backup is from what time.

    Friday, September 28, 2007 11:21 PM
    Moderator
  • Thanks,

     

    but even if i append to media, and it fails, i will left with no backups , Right?

     

    also for your best suggestion, can you send me sample query or how to do in Enterprise manager.

     

     

     

    Saturday, September 29, 2007 4:46 AM
  • Fahim,
             If you are appending to a media daily and if the backup fails today (assumption) then you are atleast left with the backups of previous days since you are appending the previous backups will be present ! but if chose the overwrite option it will overwrite the existing backup file and then will backup the database so if your backup fails due to some reason you are left with no backups for your database.
    You can make use of the maintenance plan in sql 2000 to configure backup job and you can specify the number of days to retain the backup file........the backup file will be created with  dd/mm/hr format so that it can be deleted by chosing the option "Remove files older than"
    http://www.databasejournal.com/features/mssql/article.php/3530486

    Friends pls correct me if am wrong Smile

    Regards
    Deepak
    Sunday, September 30, 2007 5:34 AM
    Moderator
  • It depends on what you do with your backup of course.

    We take disk backups which are in their turn saved to tape and copied to another server.  In this case we always have our previous backup on tape or a separate server in case we have the bad luck of destroying our 'original' backup.  In case you do not have a safety copy of your backup you are 100% right. 

     

    Also do not forget the most important aspect of a backup is the restore!  Many people blindly trust their backups without restoring it on a test server once in a while to make sure

     

    WesleyB

    Visit my SQL Server weblog @ http://dis4ea.blogspot.com

    Monday, October 01, 2007 5:29 PM