[HOWTO] How to restore SQL Server2000 database files when corrupted

server| Recovery | data | database


How to recover
when the database file is corrupted




welcome everyone to communicate with me: small white enhydra_boy@tom.com





Welcome reprint, please keep this statement, thank you!





SQL Server2000, what do we do if a database file (a non-system database file) encounters an error. The following is the author's previous notes. Applies only to databases that are not master,msdb.





description is as follows:





1 to build a test database testing (the database type is complete)


2 to build a table, insert point record


CREATE TABLE A (C1 varchar (2))


Go


INSERT into a values (' AA ')


Go


INSERT into a values (' BB ')


Go


3 for full backup to file Test_1.bak


4 is making a little change


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 Database Server


6 Edit the database file Test_data.mdf with UltraEdit, modify the point byte content casually, the equivalent database is fatal damage.


7 starts the database and runs Enterprise Manager, points to the database, sees test turned gray, and displays doubt.


8 Run Isql-slocalhost-usa-p


1> BACKUP LOG test to disk= ' D:\Program Files\Microsoft SQL Server\mssql\backup\t


Est_2.bak ' with No_truncate


2>go


has 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).





9 to restore the oldest full backup


1> RESTORE DATABASE test from disk= ' D:\Program Files\Microsoft SQL Server\mssql\


Backup\test_1.bak ' with NORECOVERY


2> Go


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


has 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 restores the most recent log


1> RESTORE LOG test from disk= ' D:\Program Files\Microsoft SQL Server\mssql\backu


P\test_2.bak ' with RECOVERY


2> Go


has 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 has been fully restored and ready for use.


SELECT * from a


Go





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