Chapter 6. Database Maintenance

Terms you'll need to understand:

SQL Server Agent

Job

BACKUP command

RESTORE command

Database snapshot

Recovery model

The INIT and NOINIT commands

The SKIP and NOSKIP commands

The FORMAT and NOFORMAT commands

Scheduling

The STOPAT option

The HEADERONLY, VERIFYONLY, and LABELONLY options

Techniques you'll need to master:

Viewing job details and history

Using queries against the msdb system database

Backing up and restoring databases and logs

Utilizing database snapshots

Implementing recovery modes and recovery options

Scheduling maintenance

Database and server maintenance are ongoing in a production environment. As a database administrator or other IT manager, you must perform numerous maintenance activities to get the best performance out of the system and to be prepared for any outage that might occur.

Realistically, you cannot go through life working with computers without dealing with data recovery at some point. If you have not personally seen data corruption or hardware malfunctions, it is only a matter of time until you do.

Most SQL Server implementations provide mission-critical data services to corporations. Often, if not always, a system going down or data being lost has a direct financial impact on the company. This chapter looks into recovery operations you can undertake when the inevitable occurs. Chapter 9, "Implementing High Availability," takes this concept a step further by examining how to keep the server and its data available all the time.