SQL Server Performance Forum – Threads Archive

Retention period for backupsets

I was under an impression that if I set retention period in sp_configure to a specific day, then my backupsets would be retained for that period and would be overwritten when the period expires; thus, my xxxx.bak, which contains multiple backupsets, would not grow that much and disk space could be saved because existing backupsets would be overwritten. However, I found that xxxx.bak is still continuing to grow and backupsets are still being generated with new backupset id’s and position numbers. I’d like to know how I can remove older backsets from xxxx.bak file. I use EM to do the backups and ‘append to media’ option is chosen. The free disk space is reducing everyday and eventually will be running out. Any suggestions in this regards will be appreciated.

In the BACKUP statement which option you are using WITH INIT or WITH NOINIT?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

… use WITH INIT to overwrite the backup sets, with NOINIT will append to the media.
http://www.sqlteam.com/Item.asp?ItemID=2339 useful link.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

This is the statement generated by Enterprise Manager: BACKUP DATABASE [IVR_Survey] TO [IVR_Survey_DiffBkup] WITH NOINIT , NOUNLOAD , DIFFERENTIAL
, NAME = N’IVR_Survey _DiffBkup’, NOSKIP , STATS = 10, NOFORMAT It says NOINIT because I chose ‘append to media’ option, and that’s what I want, so that new backupsets will be appended to existing media and restoring from multiple backupsets will be possible. So, as a test, I set retention period to 2 days in sp_configure, and stop/start SQL Server, and hoping existing media will be overwritten after 2 days, but it does not work the way I’ve anticipated.

]]>