What is the command for repair SQL database?

by
What is the command for repair SQL database

As a database administrator, one of the most important responsibilities is keeping your SQL databases healthy and optimized for performance. Over time, normal use and unexpected events can cause corruption or damage to database files, resulting in errors and instability. When this happens, it’s critical to know how to properly repair the database to restore full functionality.

Causes of Database Corruption

There are various reasons a SQL database may become corrupted or damaged. Some common causes include:

  • Hardware failures – Issues with storage devices, memory, CPU, etc can lead to data loss or corruption.
  • Power outages – Unexpected shutdowns or power loss can interrupt writes to database files.
  • Software bugs – Bugs in SQL Server code or drivers can introduce errors.
  • Incomplete transactions – Rollbacks or crashes during complex transactions can corrupt data.
  • Administrator mistakes – Errors in management, configuration or maintenance.
  • Concurrency issues – When multiple writes clash due to lack of isolation.

Often the damage is minor and limited to specific areas of the database. But if left unaddressed, it can spread and cause cascading failures. Running regular maintenance helps mitigate some of these risks.

Detecting Corruption

There are a few key indications that a SQL Server database has become corrupted:

  • Errors when reading or writing data – Such as timeouts, IO errors, inconsistent results, etc.
  • Database consistency errors – When DBCC CHECKDB finds corruption.
  • Startup failures – If the database cannot start due to damaged files.
  • Query failures – Certain statements may fail with cryptic internal errors.
  • ODBC/connectivity issues – Drivers may report errors due to corruption.

DBAs should monitor logs and alerts closely for any of these red flags. You can also run regular DBCC CHECKDB checks to detect corruption proactively.

Backup Before Repairing

Before attempting any repairs, always take a full backup of the damaged database. This provides a fallback snapshot you can restore if the repair fails or causes additional issues. Follow these steps:

  1. Stop access to the database by taking it offline or shutting down applications.
  2. Run a full database backup using T-SQL, SSMS or maintenance plans.
  3. Backup transaction log to capture details since the last full backup.
  4. Copy the backup files to separate secure storage.

With a solid backup available, you can safely attempt repairs on the live database while it’s offline. If issues arise, you can quickly restore the backup and start over.

DBCC CHECKDB

The DBCC CHECKDB command is the primary tool for checking and repairing SQL Server database corruption. It performs a detailed scan of database pages and structures looking for consistency errors. To run a basic check and repair:

DBCC CHECKDB (‘database_name’) WITH REPAIR_REBUILD;

Key parameters:

  • REPAIR_REBUILD – Repairs corrupt pages by rebuilding from existing data.
  • REPAIR_ALLOW_DATA_LOSS – More extreme repairs by dropping damaged rows/pages.
  • PHYSICAL_ONLY – Only checks allocation and page-level issues.
  • NO_INFOMSGS – Hides informational messages during check.
  • ALL_ERRORMSGS – Prints all error messages encountered.

Running DBCC CHECKDB regularly helps proactively catch corruption issues early before they escalate. But when corruption is already known, targeted manual runs may be needed to surface details and execute repairs.

Common CHECKDB Repair Messages

CHECKDB repair will output messages indicating what issues were found and what actions were taken. Here are some common messages:

  • REPAIR: Page (1:1715355) has been deallocated from object ID 184775321… – A page was deleted/unallocated. It will be reclaimed.
  • REPAIR: Index entry (1:179205) for object ID 322455681, index ID 1, is out of order… – Index data sorted incorrectly. Entries will be re-ordered.
  • REPAIR: AllocUnitId (72057594037936128) has been repaired – Extent allocation unit metadata corrected.
  • REPAIR: Repairing 1 bad page(s) – Counter tracking number of corrupt pages fixed.

These show the CHECKDB operation finding and fixing various page, index and metadata errors automatically. More serious issues may require picking REPAIR options or further manual steps.

Manual Page Repairs

For low-level page corruption, the DBCC PAGE command can directly inspect and repair individual pages. The key options are:

  • DBCC PAGE – Views page contents, space usage, row counts, etc.
  • PAGE, no_infomsgs – Suppresses info messages, shows only errors.
  • REPAIR_ALLOW_DATA_LOSS – Repairs page structural damage.
  • REPAIR_REBUILD – Rebuilds page content data from other pages.

For example, to rebuild page 1715355 in a database:

DBCC PAGE (MyDB, 1, 1715355, 3) WITH REPAIR_REBUILD

Manual page repair gives you more control but requires knowing the specific damaged pages. CHECKDB will output this when finding corrupt pages.

Rebuild Corrupted Indexes

CHECKDB may detect corruption in indexes without being able to rebuild them automatically. Targeted index rebuilds can often fix the underlying corruption. Steps:

  1. Identify damaged index using CHECKDB or other analysis.
  2. Rebuild index using ALTER INDEX REBUILD or DROP/CREATE.
  3. Run CHECKDB again to verify.

For example, to rebuild an index named IX_Orders in the Sales table:

ALTER INDEX IX_Orders ON Sales.Orders REBUILD;

Rebuilding is offline so it won’t affect concurrent queries. Rebuild individual indexes only when corruption is isolated and known.

More Aggressive Repair Options

If CHECKDB repairs don’t fully fix the damage, more aggressive options can be used as a last resort:

  • REPAIR_ALLOW_DATA_LOSS – Drops rows, pages, extents with unfixable corruption.
  • DUMP – Writes corrupt pages to a dump file for analysis.
  • LOAD – Reads pages from dumped file to overwrite corrupt version.

These can cause data loss but allow CHECKDB to complete. Weigh the risks carefully first. It may be safer to restore backups or copies of individual objects versus losing data.

Restore From Backups

If repair options don’t fix the corruption or cause additional issues, restoring from backups might be needed. This rolls back to an earlier known-good state:

  1. Restore latest full and log backup (stopping at error point).
  2. Run CHECKDB to validate.
  3. Replay additional log backups selectively to recover data.

Targeted restores can also be done at the object or page level to minimize rollback when damage is isolated.

Backups are your protection net when repairs go wrong. But frequent backup overhead can slow performance, so balance your retention policy.

Start Fresh With Database Restore

For extreme corruption scenarios, a database restore from scratch may be needed. This involves:

  1. Make full backup copy of current (damaged) database.
  2. Restore database to new instance from last known good backup.
  3. Transfer recovered data/objects into original database instance.
  4. Start applications using restored database.

This allows you to start fresh while preserving data from the damaged database for additional recovery. A last resort when all else fails.

Conclusion

SQL database corruption happens, but with the right recovery knowledge you can stay resilient:

  • Detect corruption early via alerts, logs and CHECKDB.
  • Have backup plans to make database restores easy.
  • Know repair options like CHECKDB, PAGE and index rebuilds.
  • Understand when more extreme measures may be needed.

Regular checks, maintenance windows and careful process helps minimize repair impacts. Stay proactive and you’ll keep your databases healthy.