Recovery methods for corrupted database files

Describes the following: SQL Server 2000 file Corruption recovery

1. Build a test database testing (the database type is complete).

2, build a table, insert point record.

CREATE TABLE A (C1 varchar (2)) go insert in a values (' AA ') go insert in a values (' BB ') go

3, make a full backup, to file Test_1.bak.

4, make a little change.

Insert into a values (' cc ') go to create TABLE B (c1 int) Go insert in B values (1) Go insert in B values (2) Go

5, Shutdown database server.

SQL Server 2000 file corrupted recovery 6, with UltraEdit Edit database file Test_data.mdf, casually modify the point byte content, equivalent to the database was fatally damaged.

7, start the database, and run Enterprise Manager, point open the database, see test turned gray, and show doubt.

8, run isql-slocalhost-usa-p.

1> BACKUP LOG test to disk= ' D:program filesmicrosoft SQL servermssqlbackup ' with Est_2.bak no_truncate

Processed 2 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).

The BACKUP LOG operation successfully processed 2 pages and took 0.111 seconds (0.087 Mb/sec).

SQL Server 2000 file Corruption Recovery 9, to restore the oldest full backup.

1> RESTORE DATABASE test from disk= ' D:program filesmicrosoft SQL servermssql BACKUP est_1.bak ' with NORECOVERY 2> g O

Processed 96 pages that belong to the file ' Test_data ' of the Database ' Test ' (located on file 1). Processed 1 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).

The RESTORE DATABASE operation successfully processed 97 pages and took 0.107 seconds (7.368 mb/sec).

10, restore the most recent log.

1> RESTORE LOG test from disk= ' D:program filesmicrosoft SQL servermssqlbacku P est_2.bak ' with RECOVERY 2> '

Processed 2 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).

The RESTORE LOG operation successfully processed 2 pages and took 0.056 seconds (0.173 mb/sec).

The data is fully recovered and ready for use.

SELECT * from a Go

To summarize, DBAs should have a comprehensive database backup plan. In this case, the recovery of the database is not possible without a full backup.