SQL database MDF data file database recovery

Source: Internet
Author: User
Tags mssql server
EXEC sp_attach_db @ dbname = 'dbname ',
@ Filename1 = 'd: \ dbname_Data.MDF ',
@ Filename2 = 'd: \ dbname_log.ldf'

Sp_attach_single_file_db @ dbname = 'dbname'
, @ Physname = 'physical _ name'
Dbname: name of the database to be restored.
Physname: physical file name.
Physical_name: Specifies the. mdf file path.

Database: mssql server 2000 Enterprise Edition
Problem description: Database doubt. The database backup file is damaged. Copy the physical database file (*. mdf) and use the database appending function. The attachment failed.
Error message:
Server: Message 1813, level 16, status 2, Row 1
Failed to open new database test. Create database will be terminated.
Device activation error. The physical file name d: test_log.ldf may be incorrect.
Find the relevant information solution as follows:
A. We use the default method to create a database for recovery (such as test ). It can be created in SQL server enterprise manager.
B. Stop the database server.
C. Delete the log file test_log.ldf of the database you just generated, and overwrite the generated database data file test_data.mdf with the mdf file to be restored.
D. Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database.
E. Set the database to allow direct operation of system tables. In SQL server enterprise manager, right-click the database server and select "properties". On the "server Settings" Page, select "allow direct modification to system directory. You can also use the following statement.
Use master
Go
Sp_configure allow updates, 1
Go
Reconfigure with override
Go
F. Set test to emergency repair mode.
Update sysdatabases set status =-32768 where dbid = db_id (test)
At this time, you can see in SQL server enterprise manager that the database is in the "read-only questionable offline emergency mode" to see the tables in the database, but only the system tables
G. Execute the true recovery operation below to recreate the database log file.
Dbcc rebuild_log (test, c: program filesmicrosoft SQL servermssqldatatest_log.ldf)
If the following prompt is displayed during execution:
Server: Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
Dbcc execution is complete. If dbcc outputs an error message, contact the system administrator.
This indicates that other programs are using this database. If you opened the system table of the test database using SQL server enterprise manager in step f, you can exit SQL server enterprise manager.
The prompt for correct execution should be similar:
Warning: the database test log has been rebuilt. Transaction consistency is lost. Dbcc checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.
Dbcc execution is complete. If dbcc outputs an error message, contact the system administrator.
In SQL server enterprise manager, you can see that the database status is "only for dbo ". Now you can access the user tables in the database.
H. Verify Database Consistency (omitted)
Dbcc checkdb (test)
The general execution result is as follows:
Checkdb finds 0 allocation errors and 0 consistency errors (in database test ).
Dbcc execution is complete. If dbcc outputs an error message, contact the system administrator.
I. Set the database to normal
Sp_dboption test, dbo use only, false
If no error occurs, Congratulations. Now we can use the recovered database normally.
J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step e. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL server enterprise manager or use the following statement.
Sp_configure allow updates, 0
Go
Reconfigure with override
Go

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.