We’re often asked to provide support during a crisis. Maybe a critical database is corrupt and no longer accessible. Or worse, an entire SQL Server instance is lost. Fortunately, many companies have a documented backup process. What’s far less common, however, is a well-documented process for restoring and recovering the critical databases. That part is usually neglected. Backups are important. But it’s the ability to perform database restores when a crisis strikes, that’s what’s really important.

Backups are just a means to an end.

How you do backup SQL Server?

When a new client in a crisis reaches out for help, we usually start by asking “how do you backup your SQL Server?” We typically hear one of four responses.

1. A 3rd party backup utility. These often include Veeam, Recoverypoint, Datto, and the like.
2. SQL Server native backups. This is generally the approach we recommend.
3. Virtual machine snapshots. We’re not fans of this approach. See Using VM Snapshots to Backup SQL Server?
4. There are no backups. It goes without saying that this is beyond bad for the company, especially if the database has critical data in it. Please don’t find yourself in this situation!

Here are a few follow up questions we ask:

1. What type of backup is being taken? If using a 3rd party application this can get a bit murky. Is SQL Server aware of the backup, are these snapshot-only backups? Is documentation readily available from the vendor on how to restore/recover?
2. When was the last successful backup?
3. Are the backups accessible?
4. Is the database being checked for corruption on a regular basis?
5. What recovery model is the database using? Full, Simple, Bulk-Logged?
6. At what point in time should the database be restored (if using the full recovery model and log backups are taken)?
7. Does the recovery model change during any type of ETL processing? For example, does it switch from Full to Bulk-Logged and back again? If so, how are backups taken during this transition?
8. Do you have any specific documentation for the backup or recovery process in your environment?

Performing database restores

Let’s look at what the process may look like when using 3rd party applications, and when using native SQL Server backups.

Performing database restores using 3rd party applications

Restoring a database using a 3rd party application may vary by vendor. Typically each one will have their own documented set of steps to follow. Here are some links that may help. It’s not exhaustive nor comprehensive. But it’s a start.

1. Red-Gate SQL Backup – Restoring – SQL Backup 10 – Product Documentation (red-gate.com)
2. Veeam Backup & Replication – Restoring Microsoft SQL Server Items – User Guide for VMware vSphere (veeam.com)
3. Dell Recoverpoint – Support for RecoverPoint | Overview | Dell US
4. Datto – Datto | The Managed Service Provider Technology Company (I had a hard time finding any specific documentation on how to restore SQL Server databases. If you use this tool, work with the vendor to identify where this documentation resides and make note of it.)

Understand the differences in restoring a system database versus a user database when using your 3rd party tool of choice. Document the process for each. Also, document any differences in the following scenarios if they apply to your environment:

1. SQL Server Failover Clustered Instance (FCI)
   • What’s the process for restoring to an FCI? System databases and user databases.
2. SQL Server databases contained in an Availability Group?
3. SQL Server standalone instances.

Before a crisis strikes, make sure you know where this documentation resides. Practice restoring databases using this process. Document any issues you encounter, or any workarounds needed for your specific environment.

Once the documentation is in place. Test it.

Then a month later, test it again. Rinse and repeat regularly. So, when an actual crisis strikes, you won’t be stumped. You won’t have to figure it out on the fly. When the clock is ticking and customers are waiting.

Performing database restores using native SQL Server backups

Restoring a user or system database that was backed up using the native SQL Server backups is a well documented process. See Restore a Database Backup Using SSMS – SQL Server | Microsoft Learn

Ola Halengren has created a backup solution that can rival most any other scripts I’ve seen. See SQL Server Backup, Integrity Check, Index and Statistics Maintenance (hallengren.com). The documentation is very detailed and this solution is what we use in most environments.

As with the 3rd part backup solution approach, you should document and practice database restores in your environment.

A sample backup schedule

For example, let’s say you are using native SQL Server backups and have the following backup schedule.

• Saturday – Full backup at 1:00 AM
• Daily (except Saturday) – Differentials at 1:00 AM
• Every 15 minutes – Transaction log backups (for databases using Full Recovery Model).

This is what we typically recommend. Of course, the approach and schedule can be adjusted as needed. With this schedule, you can recover to a specific point-in-time, assuming the backups are accessible and do not contain corruption. (You are checking for corruption on a regular schedule, right? See Schedule DBCC CHECKDB and When Was the Last Known Good DBCC CHECKDB Integrity Check?)

What happens when you encounter an issue, say someone drops a table or executes an update without a WHERE clause at 7:00 PM on a Thursday night?

Do you know how to restore and recover the database up to 6:59 PM using your backups?

A sample recovery process

To recover from a crisis at 6:59 PM, you’d would go through a process similar to this.

1. Restore the full database backup (usually as a different name if it’s to recover a specific table) using the latest backup from the previous Saturday. Don’t recover it; leave it in a restoring state.
2. Restore the latest differential from Wednesday night. Again, don’t recover it just yet.
3. Restore all of the transaction log backups which have occurred since the differential.
   • Wait, those occur every 15 minutes. So you’ll need to write a RESTORE LOG statement for each of the transaction log backups that have occurred since 1:00 AM the previous night, until 7:00 PM tonight (and you’ll need to use the STOPAT clause to stop just before the mistake happened). That’s about 18 hours * 4, so about 72 RESTORE LOG statements. Be sure not to recover until the last one and be sure to include the STOPAT clause or you’ll need to start the process all over again. ( Restore a SQL Server Database to a Point in Time (Full Recovery Model) – SQL Server | Microsoft Learn)

Document and write scripts before you need them

Don’t wait until the crisis hits before practicing this process. You don’t want to type out a 72 line restore log statement while everyone is waiting on the database to be restored. By documenting and practicing ahead of time, you would have discovered that you’ll need this script. Then you would have pre-written a script that could take a few inputs and output your 72 line restore log statement (or the whole restore process).

Here are a few tools that can can help with this:

1. dbatools – dbatools docs | Restore-DbaDatabase (my personal favorite)
2. sp_databaserestore – BrentOzarULTD/SQL-Server-First-Responder-Kit: sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning. (github.com)

Sound like a lot of work?

If you’re responsible for one or more SQL Servers, documenting your backup and restore processes is critical. So is practicing the processes and refining the documentation. Do this, and you’ll be a hero in times of crisis.

If you’re unsure whether your SQL Servers are being properly backed up, if you’re not confident that they can be restored in a crisis, if you’d like a second set of eyes on the process, let us know. We can help.

Contact us to learn more.