Skip navigation

How can I overwrite expired backups on a disk device to save on space and still keep a rolling x days there?

A. You can't. Without searching the entire file SQL has no way of knowing how much space each backup takes. To delete one part it would have to read the current backup file and write out all the un-expired dumps to a new file and then delete/rename. And only then could it write the new one to the end. All this would take far too long and too much disk space.

The expiry date is JUST there to prevent accidental overwrites and nothing else.

If you want to keep a rolling number of dumps then write them out with different names each day using the DISK=option of the dump command. Then have a regular task check these dumps and delete old ones.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.