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:
- 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.
- 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.