Restoring a database is an essential part of any DBA's job running Workforce Central. If you cannot restore a database, you will almost certainly lose your job. Thus, the topic of restoring a database seems well worth digging into more deeply.

A Quick Review of a Basic Restore Operation

In our last post, we talked about recovering your database from the database backups you took previously. Your recovery strategy is only as good as your backup strategy. A "fire drill" can test to make sure that you've got the appropriate kinds of backups, that they were written to the appropriate kinds of media, and that they were taken at the appropriate frequency to match your recovery goals.

Once you have the backups, you should practice these tasks:

• Restore the databases.
• Apply differential backups.
• Apply differential logs.
• Try recovering to a specific point in time to understand how that works.
• Recover to a marked transaction point.
• Recover the logs over a period of several logs to understand approximately how long it actually takes to perform the restore operation.
• Try to use a variety of media (local hard drives, SAN storage, and, dare we say, tape), and discover the advantages and disadvantages with each type of media.

Where Should the Restored Database Go?

This question is harder than it appears at first. To get the best sense of how things will go in a real situation, you would want to recover your backups to your production server (preferably in place) during some downtime on the server. However, this can be a high-risk operation because you have the possibility of affecting your production databases. Additionally, you would be placing a load on the system. You could possibly restore with a new database name on the same server, but that only slightly lowers the risk. Therefore, you will probably end up restoring to a test server. This will complicate the restoration, as your disk, files, and filegroup layouts may be different on another server and this may make the restore commands more complex. However, it significantly lowers your risk of accidentally damaging your production server. Additionally, if you make a critical mistake, you can always drop a test database and start the recovery all over.

Recovery Other Than Database Backups

We introduced this topic in the last post, but it's worth revisiting the topic. A great fire drill is to assume that your database server was either damaged or lost (perhaps it was stolen, a fire occurred, the HVAC failed and the computer will not startup anymore—pick your favorite scenario). Can you answer these questions?

• How would you go about recovering that server?
• Do you have copies of Windows Server handy?
• Not your job? Do you know who to call?
• How long will it take to get another computer with similar memory, storage, and processor capabilities? Do you have a backup computer available?
• What settings were used to configure the computer before SQL Server was installed?
• Are the service accounts local, or are they domain accounts?
• Who had administrator rights on the computer?

If you cannot answer these questions, you need to spend some time investigating and documenting this information so that it is available in a recovery situation.

After you correctly install and configure Windows, install SQL Server. Here are the questions to think about:

• What collation was used before?
• What service accounts will you need to set up?
• Do you remember the location of the system drives?
• Did you use any trace flags?
• Who were the members of the sysadmin fixed server role?
• Will you need other server-wide rights?
• Did you also install SQL Server Integration Services on that system?
• What about local management tools?
• Do you have backups of the master and msdb databases?
• Do you have scripts to re-create critical logons, security objects, and jobs?
• Did you use encryption, and do you have backups of any security keys and certificates?

As you can tell, going through this exercise will almost certainly lead you to discover items you have missed or don't have documented.