SQL Server Performance Forum – Threads Archive
Delete expired backups from a backup device
Hi, Can anyone tell me how i delete expired backups, from a backup device, in a sql script? or how i overwrite them with the running backup. thanks!Hi,
please clear your question , if you wish to delete old backups , its their in maintanance plan (EM) in complete backup tab . Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
Do you need something like this?
http://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.html Madhivanan Failing to plan is Planning to fail
As suggested you can use Maintenance plan to remove the old backups, sometimes it is not easy option to do so. Its better to use a script to delete them in order to ensure no issues with the job. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Okay ill explain it a bit better then.<br /><br />Ive set up log shipping between two servers.<br /><br />ServerA does a log backup every 30 mins to a file (backup device), and serverB restores a backup from the same file (backup device) every hour.<br /><br />Ive set serverA’s backup job with the parameter RETAINDAYS=3 so backups expire after 3 days:<br /><pre id="code"><font face="courier" size="2" id="code">USE MASTER<br />BACKUP LOG DP_AOJ<br /> TO DP_AOJ_Translog<br />with RETAINDAYS =3</font id="code"></pre id="code"><br /><br />And now my problem is that i can see that the backups are expired, by viewing the contents of the backup device, but it does not automaticly clean it up.<br /><br />so i wanted some help to clean out old backups.<br /><br />better? <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
Then better to use the script to drop those old files, take help fromhttp://www.sqlteam.com/item.asp?ItemID=11672 link. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Satya<br /><br />Ive read the link you posted, the script in the link does seem to use a file backup for every backup (im not an expert DBA but as far as i can see it does <img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> ), and deletes with a custom retention period, by looking at the filename of each backup, it deletes the older one.<br /><br />This is not how my setup look, i have a backup device i backup my log to.<br /><br />so i need a script or parameters to my exisisting backup jobs, that deletes or overwrite expired backups in my backup device.
If you are using log shipping then it should be backed up to a file, check the path under where the backup files are stored. I’m not comfortable to backup the database or log to a device than to a file. The syntax in BACKUP to retention must work when you are using a backup to a device. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
For devices (not files) you will have to INIT the device to wipe out the previous backups. There is no deleting of old backups from a device, only INIT as part of your backup command.
Please refer to MEDIA RETENTION DAYS topic also under books online for more information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Haywood</i><br /><br />For devices (not files) you will have to INIT the device to wipe out the previous backups. There is no deleting of old backups from a device, only INIT as part of your backup command.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />That worked <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />THANK YOU! and have a nice holiday.
Hi cml, But i think using INIT will overwrite all backup sets in the device.So using RETAINDAYS =3 wont have any effect,Or Is that what your requirement ?
Rajiv
Junior DBA-MSSQL My archived posts are better place for newbies to start with
True the INIT clause will delete previous details on that device and only RETAINDAYS would work if you need to keepup the files for x number of days as per the caluse. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>