SQL Server backup notes

Back Up Database Task

Several points of issue with this routine. It is not as obvious as it may seem:

  • Backup set will expire After n days or On a date – when using a backup device
    • Although the backup expires it does automatically get overriden and space released. It stays there so the backup device will continue to grow and potentially fill-up the disk. Disappointing.
  • If backup file exist: Append or Overwrite.
    • Append is clear. Overwrite is not so clear. As far as I can tell it will clear out any-all existing backups in the device. As opposed to clearing out expired backups.

For a long time I had not used the backup set expire. I have been using the expire for about 3 months with 100 days. I was expecting it to start clearing out the old backups, so keeping the backup device file size roughly static. However this did not happen and the disk started to fill up.

So in conclusion I will revert to my previous method of renaming the backup device with a date on the end yyyy-mm-dd which means the backup task then recreates it. Then after a period of time move the old backup devices off to a separate NSA drive.

Grandfather – Father – Son
Now do backups several times a day, but now also do a monthly backup with the intention of eventually deleting daily backups.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: