Restoration of Database File Corruption

Description: SQLServer2000 file damage recovery 1. Create a test database (the database type is complete ). 2. Create a table and insert vertex records. Createtablea (c1varchar (2) goinsertintoavalues (aa) goinsertintoavalues (bb) go3. Make a full backup to the file test_1.bak. 4

Description: SQL Server 2000 file damage recovery 1. Create a test database (the database type is complete ). 2. Create a table and insert vertex records. Create table a (c1 varchar (2) go insert into a values ('A') go insert into a values ('bb ') go 3. Perform full backup, to the test_1.bak file. 4

SQL Server 2000 File Corruption recovery

1. Create a test database (the database type is complete ).

2. Create a table and insert vertex records.

Create table a (c1 varchar (2) go insert into a values ('A') go insert into a values ('bb') go

3. Make a full backup to the test_1.bak file.

4. Make some modifications.

Insert into a values ('cc') go create table B (c1 int) go insert into B values (1) go insert into B values (2) go

5. shutdown the database server.

SQL Server 2000 file damage recovery 6. Use ultraedit to edit the database file test_data.mdf. Modify the dot-byte content at will, which is equivalent to a fatal damage to the database.

7. Start the database, run the Enterprise Manager, open the database, and the test turns gray.

8. Run isql-SLocalhost-Usa-P.

1> backup log test to disk = 'd: Program FilesMicrosoft SQL ServerMSSQLBACKUP est_2.bak 'WITH NO_TRUNCATE 2> go

Two pages have been processed. These pages belong to the 'test _ log' file of the database 'test' (located on file 1 ).

The backup log operation successfully processed two pages and took 0.111 seconds (0.087 MB/second ).

SQL Server 2000 file damage recovery 9. Restore the oldest full backup.

1> restore database test from disk = 'd: Program FilesMicrosoft SQL ServerMSSQL BACKUP est_1.bak 'WITH NORECOVERY 2> go

96 pages have been processed. These pages belong to the 'test _ data' file of the database 'test' (located on file 1 ). One page has been processed, which belongs to the 'test _ log' file of the database 'test' (located on file 1 ).

The restore database operation successfully processed 97 pages, and took 0.107 seconds (7.368 MB/second ).

10. Restore the latest log.

1> restore log test from disk = 'd: Program FilesMicrosoft SQL ServerMSSQLBACKU P est_2.bak 'WITH RECOVERY 2> go

Two pages have been processed. These pages belong to the 'test _ log' file of the database 'test' (located on file 1 ).

The restore log operation successfully processed two pages, and took 0.056 seconds (0.173 MB/second ).

The data has been completely restored and can be used.

Select * from a go

In conclusion, DBA should have a complete database backup plan. In this example, the database cannot be restored without a full backup.