Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to recover SQL Server 2000 damaged database files", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to recover SQL Server 2000 damaged database files" bar!

In SQL Server2000, what should we do if database files (non-system database files) encounter errors. The following are the author's previous notes. Applies only to non-master,msdb databases.

The explanation is as follows:

1 build a test database test (database type is complete) 2 build a table and insert point records

Create table a (C1 varchar (2)) goinsert into a values ('aa') goinsert into a values (' bb') go

3 make a full backup and make some changes to the file test_1.bak4

Insert into a values ('cc') gocreate table b (C1 int) goinsert into b values (1) goinsert into b values (2) go

5 shutdown database server 6 edits the database file test_data.mdf with ultraedit and modifies the byte content casually, which is equivalent to the fatal damage to the database. 7 start the database, and run Enterprise Manager, click on the database, and see that the test turns gray and shows doubt. 8 run isql-SLocalhost-Usa-P1 > backup log test TO DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP est_2.bak' WITH NO_TRUNCATE2 > go

Processed 2 pages that belong to the database 'test' 's file' TEST_Log' (on file 1). The BACKUP LOG operation successfully processed 2 pages and took 0.111 seconds (0.087 MB/ seconds).

9 restore the oldest full backup

1 > RESTORE DATABASE test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP est_1.bak' WITH NORECOVERY2 > go

96 pages have been processed that belong to the database 'test' 's file' TEST_Data' (on file 1). Processed 1 page that belongs to the database 'test' 's file' TEST_Log' (on file 1). The RESTORE DATABASE operation successfully processed 97 pages and took 0.107 seconds (7.368 MB/ seconds).

10 restore the most recent log

1 > RESTORE LOG test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP est_2.bak' WITH RECOVERY2 > go

Processed 2 pages that belong to the database 'test' 's file' TEST_Log' (on file 1). The RESTORE LOG operation successfully processed 2 pages and took 0.056 seconds (0.173 MB/ seconds).

The data has been fully recovered and ready to use.

Select * from ago

To sum up, DBA should have a complete database backup plan. In this case, the recovery of the database is impossible without a full backup

How to recover when the sql server database crashes?

No database system can avoid crashing, even if you use clustered, dual-computer hot standby. It is still impossible to completely eradicate the single point of failure in the system, not to mention that for most users, they cannot afford such an expensive hardware investment. Therefore, when the system crashes, how to restore the original valuable data has become an extremely important problem.

At the time of recovery, the ideal situation is that your data files and log files are intact, so you only need sp_attach_db to attach the data files to the new database, or copy all the data files (there must be master, etc.) to the original path when downtime, but this practice is generally not recommended, sp_attach_db is better, although a lot of trouble.

However, in general, when the database crashes, the system may not have time to write outstanding transactions and dirty pages to disk, so sp_attach_db will fail. So, count on dba to have 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 can still be read, congratulations! You can go back to the state you were before the crash.

In general, there is no full-time dba. If there is no available backup, it is more likely that the last backup was too long and led to unacceptable data loss, and your active transaction log is also in an unavailable state, that is the most troublesome situation.

Unfortunately, most database crashes are caused by the storage subsystem, in which case it is almost impossible to have available logs for recovery. Then we'll have to try these plans. Of course, it is required that at least your data files exist, if the data files, log files and backups are gone, don't call me, you can go to the roof and sing "God, help me".

First of all, you can try sp_attach_single_file_db, try to recover your data files, although it is unlikely to recover, but if the database happens to execute a checkpoint, it is possible to succeed.

If you are not lucky enough to touch the lottery, the most important database is not attach as you expected, don't be discouraged, there are other plans.

We can try to create a new log. Setting the status of the database to emergency mode,sysdatabases to 32768 indicates that the database is in this state.

However, the system table can not be changed casually, set it first.

Use master go sp_configure 'allow updates', 1 reconfigure with override go

Then update sysdatabases set status = 32768 where name =''now, pray for the blessing of the gods and Buddhas and re-create a log file. The chances of success are still quite high, and the system will generally recognize your newly created log. If no mistakes are reported, you can breathe a sigh of relief now.

Although the data is restored, don't think that even if the work is done, the ongoing transaction must be lost and the original data may be corrupted.

Restart sql server first, and then check your database. Set it to single-user mode first, and then do dbcc

Sp_dboption', 'single user',' true' dbcc checkdb ('')

If there is no big problem, you can change the database state back, and don't forget to turn off the modification option for the system table.

Update sysdatabases set status = 28 where name =''- of course, your database status may not be this, so change it to the appropriate value. You can also use sp_resetstatus go sp_configure 'allow updates', 0 reconfigure with override go

Some errors may be reported during checkdb, and you may have to discard the wrong data. There are several repair options for checkdb. See for yourself, but in the end, you may have to use repair_allow_data_loss to complete all the fixes. Chekcdb does not complete all the fixes. We need further fixes. Check each table with dbcc checktable.

The list of tables can be obtained in sysobjects. Find out and check that objectproperty is all of istable. This can basically solve the problem. If errors are also reported, try to select into the data to another table to check. When all this is done, recreate all indexes, views, stored procedures, triggers, and so on. Dbcc dbreindex may be able to help you with something.

The recovery steps when the database log file is lost, describing how I tried to recover the database after I mistakenly deleted the database transaction log file (.ldf).

However, many netizens have many new questions in the practice of dealing with "database doubt". I would like to sum up a few situations for reference.

2.Zach 's validated script

Zach said that every time he encountered such a database doubt situation, he would run the following script, which worked well: = =-- before running any script, run the following to set the master database to allow updatesUSE masterGOsp_configure 'allow updates', 1GORECONFIGURE WITH OVERRIDEGO

-- Run the following scriptUPDATE master..sysdatabases SET status = status ^ 256 WHERE name = 'Database_Name'

-- Run the following scriptexec 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', 0GORECONFIGURE WITH OVERRIDEGO==

As you can see from the above, the basic steps for dealing with skepticism are still the ones I said in my article (note the font color I used): perform sp_configure to allow updates to system tables, and then enforce the configuration with RECONFIGURE WITH OVERRIDE statements; database reset emergency mode; execute the sp_resetstatus query flag to shut down the database, but leave the database's other options intact (only system administrators can perform). Immediately after performing this procedure, restart the SQL Server service; execute sp_configure to prevent updates to the system table, and then enforce the configuration with the RECONFIGURE WITH OVERRIDE statement.

Status ^ 256 means: Constant Value DescriptionSQLDMODBStat_Suspect 256 Database integrity is suspect for the referenced database.

The difference is that sometimes the database log files are lost, which requires the following additional steps:  sets the application database to Single User mode;  can only do DBCC CHECKDB;.

But the result of the practice of several netizens is that the implementation of this DBCC CHECKDB failed. A netizen yang said: "but DBCC CHECKDB just can't be executed, always saying 'the database is in avoidance recovery mode'. I've tried many times, but I just can't change this state." Another Rui executive DBCC CHECKDB Times error: "Server: Msg 943, Level 14, State 1, Line 1 Database 'his_yb' cannot be opened because its version (539) is later than the current server version."

For Yang, maybe he didn't do it step by step. My personal experience is that after setting the application database to Single User mode, you can do DBCC CHECKDB. After that, maybe after SQL Server restarts, it automatically checks whether the database is normal. But the data should be readable, or at least readable by DTS Wizard. There are still problems with the database at this time, such as when my component uses the database, it reports: "error:-2147467259, failed to run BEGIN TRANSACTION in the database 'XXX' because the database is in avoidance recovery mode."

For Rui, the error he encountered was Server: Msg 943, Level 14, State 1, Line 2Database 'XXXX' cannot be opened because its version. Is later thanthe current server version. This indicates that Rui is trying to restore from a database backup of SQL Server 2000 (version 539536, etc.) to a SQL Server 7.0or attach a database of SQL Server 2000 (version 539536, etc.) into a SQL Server 7.0, which is not allowed. If you must use this SQL Server 2000 data backup, please first pour this backup into SQL Server 2000, and finally use DTS to transfer the database from SQL Server 2000 to SQL Server 2000.

Thank you for your reading, the above is the content of "how to recover SQL Server 2000 damaged database files", after the study of this article, I believe you have a deeper understanding of how to restore SQL Server 2000 damaged database files, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.