How to recover a corrupted database file for SQL Server 2000 _mssql

In 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.

The description is as follows:

1 Build a test database testing (database type is complete)
2 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 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 Start the database, and run Enterprise Manager, click on the database, see test turned gray, and show doubt.
8 Running Isql-slocalhost-usa-p
1> BACKUP LOG test to disk= ' D:program filesmicrosoft SQL servermssqlbackup
Est_2.bak ' with No_truncate
2>go

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 filesmicrosoft SQL servermssql
BACKUP Est_1.bak ' with NORECOVERY
2> Go

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> Go

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

How do I recover when a SQL Server database crashes?

No database system can avoid a crash situation, even if you use a clustered, two-machine hot standby ... It is still not possible to completely eradicate the single point of failure in the system, and for most users, this expensive hardware investment cannot be sustained. So, when the system crashes, how to restore the original valuable data becomes an extremely important problem.

At the time of recovery, ideally, your data files and log files are intact, so you just need to sp_attach_db, attach the data files to the new database, or in the downtime of all data files (must have master, etc.) all copy to the original path under the line, However, generally do not recommend such a practice, sp_attach_db better, although a lot of trouble.

However, when the general database crashes, the system may not have time to write the unfinished transactions and dirty pages to disk, such a situation sp_attach_db will fail. Then, expect the DBA to make a good disaster recovery plan. According to your recovery plan, restore the latest full backup, incremental backup, or transaction log backup, and if your active transaction log is still readable, congratulations! You can revert to the state before the crash.

The average unit is not a dedicated DBA, if there is no backup available, it is more likely that the last backup time too long to cause unacceptable data loss, and your active transaction log is also in an unusable state, that is the most troublesome situation.

Unfortunately, the general database crashes are caused by the storage subsystem, and it is almost impossible to have the logs available for recovery. Then we have to try these plans. Of course, is to require at least your data file is present, if the data files, log files and backups are not, don't look for me, you can go to the roof to sing "God, help me."

First of all, you can try sp_attach_single_file_db, try to restore your data file, although the possibility of recovery is not very likely, but if the database just executes a checkpoint, it is possible to succeed.

If you don't have the luck to touch the lottery, the most important database is not as attach as you would like it to be, don't be discouraged, or a different plan.

We can try to recreate a log, first set the database to emergency mode,sysdatabases status of 32768 means that the database is in this state.

However, the system table can not be arbitrarily changed, set the first

Use master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go

And then
Update sysdatabases Set status = 32768 where name = '
Now, pray for the blessing of the gods, to re-establish a log file. The chances of success are still quite large, and the system will generally recognize your newly created journal. If you don't report anything wrong, you can breathe a sigh of relief now.

Although the data is restored, but do not think that even if the matter is completed, the ongoing transaction must be lost, the original data may be some damage.

First restart SQL Server, and then check your database.
Set up Single-user mode first, then DBCC

sp_dboption ', ' Single user ', ' true '
DBCC CHECKDB (')

If there is no big problem, you can change the database status back, remember to turn off the system table modification options.

Update sysdatabases SET status = the WHERE name = '-Of course your database state may not be this, change yourself to the appropriate value. You can also use sp_resetstatus
Go
sp_configure ' allow updates ', 0
Reconfigure with override
Go

CHECKDB may report some errors, and you may have to discard the wrong data.
CHECKDB has several repair options, look at it yourself, but in the end you may have to use Repair_allow_data_loss to complete all the repairs.
CHEKCDB does not complete all the repairs, we need to do a further fix, and use DBCC CHECKTABLE to check each table.

The list of tables can be found in the sysobjects, the objectproperty is the istable of all find to check it, this can basically solve the problem, if you also report the error, try to put the data select into another table to check.
Once all of this is done, rebuild all indexes, views, stored procedures, triggers, and so on. DBCC DBREINDEX may be of some help to you.

Recovery steps when the database log file is missing, describing how to recover the database after I accidentally deleted the transaction log file (. ldf) of the database.

But many netizens in the process of "database questioning" practice, but also produced a lot of new questions.
I will also summarize the appearance of several cases for reference.

2.Zach of the effective script

Zach says he runs the following script every time he encounters this kind of database doubt:
======================================================
--before running any script, run the following to set the
master database to allow updates
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with OVERRIDE
Go

--run the following script
UPDATE Master ... sysdatabases SET status = status ^ 256
WHERE name = ' database_name '

--run the following script
EXEC sp_resetstatus database_name

--stop and start the MSDTC at this stage

--after The procedure is created, immediately disable
Updates to the system tables:
EXEC sp_configure ' allow updates ', 0
Go
Reconfigure with OVERRIDE
Go
=====================================

As you can see from the above, the basic steps to deal with the doubt are the same as what I said in my article (Note the font color I used):
Execute sp_configure to allow the system table to be updated and then enforce the configuration with the RECONFIGURE with OVERRIDE statement;
Database reset emergency mode;
Perform a sp_resetstatus to close the database, but leave the other options of the database intact (only system administrators can do so). Restart the SQL Server service immediately after the procedure is executed;
Executes sp_configure to prevent updates to the system tables, and then enforces the configuration with the RECONFIGURE with OVERRIDE statement.

The meaning of status ^ 256 is:
Constant Value Description
Sqldmodbstat_suspect 256 Database integrity is suspect for the referenced database.

The difference is that sometimes the database log files are lost, and the following additional steps are required:
 set the application database to single user mode;
 do DBCC CHECKDB;
Before you can.

But the practice of several netizens was the result of this DBCC CHECKDB execution failure. "But DBCC checkdb just can't do it, always saying" the database is in evasive recovery mode, "said one Netizen Yang. I've tried so many times that I can't change the state. ”
There's also a Rui to execute the DBCC CHECKDB times wrong: "server:msg 943, level, State 1, Line 1 Database ' His_yb ' cannot is opened because its versio N (539) is later than the current server version (515).

For Yang, perhaps he did not do it step-by-step. My firsthand experience is that you can do DBCC CHECKDB after you set the application database to single user mode. After that, maybe SQL Server restarts to automatically check the database for normal. But the data should be readable, or at least be read by DTS Wizard. There are still problems with the database, such as when my component uses the database, and the report says: "Error:-2147467259, failed to run BEGIN TRANSACTION in database ' XXX ' because the database is in a bypass recovery mode." ”

For Rui, the error he encountered
Server:msg 943, level, State 1, line 2
Database ' XXXX ' cannot is opened because its version (5 Is later than
the current server version (515).
This indicates that Rui is trying to
recover from a database backup of SQL Server (version 539,536) to a SQL Server 7.0
or
turn a SQL Server (version 539,536 of the database attach to a SQL Server 7.0,
This is not allowed. If you have to use this SQL Server 2000 data backup, you first dump the backup into SQL Server 2000 and finally use DTS to transfer the database from SQL Server 2000 to SQL Server 7.0.