To Backup or Not To Backup Test Databases [closed]

Question

Introduction

This question is explicitly asking for answers based on experience to allow me to make a decision based on the experience of fellow database administrators in similar situations. This question has been revised to remove irrelevant information.

I am asking the following questions, because I believe there can be reasons which support using a SIMPLE recovery model in a SQL Server test environment and reasons that support using the FULL recovery model for databases, even in a pure test environment.

Questions

• Would you recommend using the SIMPLE Recovery Model in a pure test environment?
  • Without pseudo-backup?
  • With pseudo-backup ¹⁾?
• What reasons support using the FULL Recovery Model in a test environment?
  • With pseudo-backup ¹⁾?

^{1): Pseudo-backup: Backup to NUL or backup to disk with a retention time of max 24 hours. Advantage: Transaction Log is CHECKPOINT-ed}

Similar Questions

A number of questions I have already read which popped up in the between-bar/sidebar while I was typing this question:

• Reasons to Backup (or not backup) QA and Dev Databases
• Switch from full to simple recovery
• Full backup of Simple recovery model - how much log is backed up if there is an open transaction
• Why does my database using the simple recovery model have such a large transaction log?
• Transaction Log is big! But simple recovery mode is chosen!
• Why is my transaction file growing when db is in simple mode and there are no waiting transactions

The answers provided in those questions do not provide any answers to my actual question of implementing either a FULL or SIMPLE Recovery Model in a test environment.

The Environment

The environment consists of the following types of serves, databases and backup procedures.

Server Types

• Productive servers
  • includes some Dev/Quality servers
• Test servers

Database Types

• Test databases
• Quality / Developemnt databases
• Production databases

Database options

All user databases are currently in the FULL recovery model.

Combinations of Servers and Databases

• The productive and development/quality databases are running on productive servers.
• The test databases are running on test servers.

Backup Procedure

Backups of productive databases are performed on a regular basis.

• Weekly Full (SA 20:00)
• Daily Differential (SO-FR 20:00)
• Hourly Transaction Log (xx:30)

Backups of test databases were performed on the same basis, but have recently been removed from the standard backup procedure for monetary reasons.

---

Optimising Test Environment

Modifications to data in the test environment will tend to be minor and will normally not have an impact on the size/growth of the transaction log file. Test databases are normally used for quick tests while implementing new versions of a given software. No need for them to be backed up really, as they can/are restored from productive backups of the the productive databases should the need arise.

Reasons for optimising the test environment are to keep the Transaction Log files at bay.

Simple Recovery Model

In order to keep the transaction log files small and because we are no longer performing backups, I at first decided to switch all user databases in the test environment to the SIMPLE Recovery Model.

Pros

• Committed data is automatically written to the database
• No transaction log backups required
• No disk space required on server

Cons

• Long running transactions will still have an impact on the size/growth of the transaction log file. (This is an issue for the FULL recovery model too)

^{Reference: Recovery Models (SQL Server) (Microsoft | SQL Docs)
Reference: Complete Database Restores (Simple Recovery Model) (Microsoft | SQL Docs)}

Full Recovery Model with Pseudo Backup¹⁾

In the process of implementing the SIMPLE Recovery Model in our test environment I thought about implementing a pseudo-backup¹⁾ procedure similar to the production environment. The idea would be to keep the user databases in the FULL Recovery Model and perform backups (preferably using Ola Hallengren's backup script) similar to the productive environment, which would retain the backup files for a maximum of 24 to 48 hours on some disk.

Pros

• Restore to Point-in-Time possible

Cons

• Disk space required for Transaction Log (possibly) and backup files (evidently)
• Long running transactions will still have an impact on the size/growth of the transaction log file.
• Transaction Log backup required.

^{Reference: The Transaction Log (SQL Server) (Microsoft | SQL Docs)}

---

Questions

• Would you recommend using the SIMPLE Recovery Model in a pure test environment?
  • Without pseudo-backup?
  • With pseudo-backup?
• What reasons support using the FULL Recovery Model in a test environment?
  • With pseudo-backup?

Answer 1

In my opinion, the answer is... well, 'simple'. Justify the use of the FULL recovery model.

• Is it a requirement to be able to recover your test databases to a specific point-in-time?
  • No? Use SIMPLE
  • Yes? Then, the FULL recovery model is your only option.
  • Don't know? Err on the side of caution and be the hero should disaster strike - Use FULL

As far as backups for the test databases, you indicated

Modifications to data in the test environment will tend to be minor

so, ask the question

If the test database disappeared, got corrupted or got updated beyond repair right now, what would be the impact?

• meh, I've saved my change scripts - we just need to restore a fresh copy of production, apply my change scripts and we're back in business. You would not need any backups of the test database.
• man, that's a bummer - I just lost a bunch of changes and I didn't save the scripts or I can't easily replay the transactions. If you're in this situation, you're basically back to the FULL recovery model scenario. You just never know when disaster will strike.

---

Regarding your hourly transaction log backups on production, are you really OK with the possibility of losing up to 1 hour of transactions in production? Back Up Transaction Logs Every Minute. Yes, Really.

Answer 2

My two cents for this. Assuming backup space is not an issue, then I treat them the same as I do production, just without any alerts and I only check on them if the developers ask me to do so.

However, backup space is almost always an issue and since at least one of the dev environments should be a close mirror of prod (with production levels of data) backups for them are the first to go out the window.

So the philosophy is this, assuming the business is ok with this kind of response time. We are developer heavy, running custom in-house software to support our primary business line with a top down committment to best practices, which means we have the full DEV/QA/STAGE/PROD pipeline.

Anything the developers are working on should be in source control. So at most any changes from the last commit are lost. So we aren't risking that much development effort.

The data is harder to replicate, but for us comes in two varieties. Either a minimal version of production (mostly schema and some test data) or a copy of production. Depending on which one gets hosed my restore options are pretty solid.

For the minimal version, I can restore one of the other developers database, or use my gold-standard version (maybe a month behind?). For the production size copy, I just restore production and scrub any data required.

Does having developers idle cost money? Of course it does, but the risks are small and self contained to a handful of internal customers (the developers).

But all of these risks are known and signed off on by management. I include every server I am supposed to support when I talk with management about HA/DR, SLA's and RPO/RTO. For production, with a hard dollar amount assigned for every minute of outage, it's straightforward to give them a RPO/RTO that everyone can live with. But getting the same requirements for the lower environments with the much lower cost of downtime is hard to justify the increase in cost.