Hot to del older backupsets from a bkup file | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Hot to del older backupsets from a bkup file

I issued this command: USE msdb
GO
SELECT backup_size, server_name, backup_finish_date, position, name
FROM backupset
WHERE database_name = ‘IVR_Network2’
order by backup_finish_date desc And, produced this result (patial list): db_size server_name backup_finish_date position name 771072BCCSMARS2004-10-25 14:00:09.00048IVR_Network2 _TranLogBkup
968704BCCSMARS2004-10-25 12:00:08.00047IVR_Network2 _TranLogBkup
245760BCCSMARS2004-10-25 10:00:09.00046IVR_Network2 _TranLogBkup I’d like to know how to delete backupset position 46 and 47 from the backup file named IVR_Network2 _TranLogBkup as shown above. In other words, how do I remove older backupsets (by positions) from a physical backup file (xxxx.bak) ? Thanks.
I don’t think it’s possible to drop an individual backup file from a backup device. sp_dropdevice drops the entire device.
I haven’t seen such statement to delete few set from the backupset. Maybe you should verify sp_configure ‘media retention’. You could add RETAINDAYS = 5 to make sure you can overwrite the file, or you could use the SKIP option. ANd finally you can use WITH INIT option to overwrite the backup device everytime when it backups the database …
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.
Per your suggestiong, I did this: USE master
EXEC sp_configure ‘media retention’, ’30’ — DBCC execution completed. If DBCC printed error messages, contact your system administrator.
— Configuration option ‘media retention’ changed from 0 to 30.
— Run the RECONFIGURE statement to install.
RECONFIGURE
EXEC sp_configure Does this mean backupsets will be kept for up to 30 days and be overwritten after 30 days?
Where do I use RETAINDAYS = 5? in sp_configure? or any whare else? or it is the same as parameter of ’30’ in this example? Thanks.
Yes based upon the value given on the media retention it will maintain the backup sets, and ensure to restart SQL Server services after this configuration changes. If you want to keep last 5 days then the value must be 5. 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.
Thanks for your help!
]]>