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

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,

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

|||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,|||

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

|||

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?

|||

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.

|||

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.

|||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

Regards
Deepak