Microsoft SQL Server 2012 Bible

Recovery Operations

Many reasons exist to restore a database, including the following:

• A disk subsystem has failed.
• A sleepy programmer forgot a where clause in a SQL UPDATE statement and updated everyone's salary to minimum wage.
• Zombie apocalypse destroys your primary data center.
• A large import worked but with yesterday's data.

The best reason to restore a database is to practice the backup/restore cycle and prove that the recovery plan works. You must perform regular testing of your backup and restore strategy as a fire drill. Without confidence in the recovery, there's little point in doing backups. Remember this mantra: Backups are worthless; restores are priceless.

Detecting the Problem

If a database file is missing, clicking the database in Management Studio pops up a message saying that the database is unavailable. To further investigate a problem, check the SQL Server Errorlog. In Management Studio, you can view the log under Management → SQL Server Logs. SQL Server writes errors and events to an error log file in the \Log directory under the MSSQL directory. SQL Server creates a new file every time the SQL Server service starts. The six previous versions of the Errorlog file are saved in the same directory. Some errors may also be written to the Windows Application Event Log.

Note

To retain more than six Errorlogs, right-click SQL Server Logs in Management Studio, and select Configure.

Tip

You can also manually “roll the log” by using the stored procedure ...