0

We've created a non-production environment for SQL Server.
This environment uses the SQL Server Always-On Availability Groups as a HA-solution.

To work with SQL Server AG's you have to set the recovery model to full so the log can be synced with the other replicas, but setting the recovery model to full also implies that the log keeps on growing if you don't take backups.

Since this is a non-production environment we're not that interested in the backups of these databases. What do you guys think is the best way to go in this environment?

  • Create backups to disk and delete them
  • Do take backups of this environment
  • Other suggestions...
2

Why not just back up the database once a week and the log every n minutes (a good balance of frequency and keeping the log in check), and have a sweeper job delete new files as they come in (or after some age)? The nice thing about this is that if you decide at some point that backups in this environment actually are important, which you might, it is just a matter of disabling the sweeper job (or changing how often it runs / what it actually does).

5

So as a preface for this answer, please consider the following points:

  • If your system is meant to be HIGHLY AVAILABLE, what are your plans for inevitable down-time and data loss? Will the time taken to recover this environment mean it will simply be faster to keep backups?
  • If your system is simply a copy of production, why are you not mirroring your backup jobs on that system?

The above may highlight why you do in fact want to set up proper backup jobs. This said, here's a really bad idea™.

BACKUP LOG [DBName] TO DISK = N'nul';
BACKUP DATABASE [DBName] TO DISK = N'nul';

This uses the nul device to stream a backup into nothingness. It should take a similar time to how long the read phase of the backup takes.

N.B. The above should not go anywhere near any production systems - you could end up losing data, and your job. The backups taken using this code (and therefore your backup chain) are not recoverable in any way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.