568
Part V: Enterprise Data Management
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 fi le 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
fi le in the \Log directory under the MSSQL directory. SQL Server creates a new fi le
every time the SQL Server service starts. The six previous versions of the Errorlog
fi le are saved in the same directory. Some errors may also be written to the Windows
Application Event Log.
To retain more than six Errorlogs, right-click SQL Server Logs in Management Studio, and select Confi gure.
You can also manually “roll the log” by using the stored procedure sp_cycle_errorlog. This can be helpful if
you want to keep the error log’s content limited to a certain time period. For example, you can schedule an agent job
to execute the sp_cycle_errorlog command every day at midnight.
In addition to rolling over the log on a scheduled basis, you probably want to increase the number of logs to retain
from the default value, as stated in the previous Note. When confi guring the number of logs to retain because the
log rolls over with every service restart, you need to confi gure a number large enough for you to accommodate unex-
pected service restarts along with your scheduled ones.
c21.indd 568c21.indd 568 7/31/2012 9:22:51 AM7/31/2012 9:22:51 AM
http://www.it-ebooks.info