SQL Server Performance

Delete expired backups from a backup device

Discussion in 'General DBA Questions' started by cml, Dec 18, 2005.

  1. cml New Member

    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!
  2. ghemant Moderator

    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
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  3. Madhivanan Moderator

  4. satya Moderator

    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.
  5. cml New Member

    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=':)' />
  6. satya Moderator

  7. cml New Member

    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.
  8. satya Moderator

    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.
  9. Haywood New Member

    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.
  10. satya Moderator

    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.
  11. cml New Member

    <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.
  12. gkrishn New Member

    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
  13. satya Moderator

    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.

Share This Page