Sql-server – Having trouble designing the preferred backup scheme

availability-groupsbackupsql serversql server 2014

First some background:

I have two Windows servers running MS-SQL-2014 setup in an availability group (synchronous auto-failover, preferred backup on secondary). They host many databases.

Right now we only need weekly backups (copy only-full backups). We don't need transaction log backups but we have to do them (and then immediately delete them) because the databases have to be in full recovery mode for the availability group, so we burn all our storage up if we don't. I use a maintenance plan (copied on both servers) to run these backups.

In the maintenance plan, I backup the dbs, then the transaction logs, then delete older then 6 months dbs, and then delete ALL transaction logs.

My Questions are these:

  1. How can I keep a smaller interval of backup longer, say keep 1 db backup every 6 months after the initial 6 months, but delete the others.
  2. Is there a better way of handling the transaction log issue, then making them and then deleting them. Feels like I'm burning hard drives cycles for nothing.

Best Answer

First of all grab Ola Hallengren's scripts to do the backups (http://ola.hallengren.com) and drop the maintainance plans. Ola's scripts have much better retention settings and will allow you to set a mark in hours how long to store the backups and know how to take the backups of the secondaries.

And for your questions:

  1. Set up a small script in SQL Server agent or Windows task scheduler that copies a backup file to a secondary folder where you keep it longer than the others or do a mirrored backup to a secondary folder every six months. A small powershell script or xcopy command that runs every six months and copies the last backup file will do just fine
  2. Backup log DatabaseName to disk ='NUL' will make a log backup to the windows equivalent of /dev/null, no disk writes or space wasted. Nogood to restore from though.
Related Question