O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required