method one
1. Create a newly named database
2. Stop SQL Server again (Be careful not to separate the database)
3. Overwate this newly built database with the original database data file
4. Restart SQL Server
5. At this time, you will be suspicious when you open the Enterprise Manager. Whether you do this, you will do the following statement (pay attention to the database name)
6. The data in the database can be accessed after completion. At this time, the database itself is generally issued, the solution is to create a new database using the database's script and will pass the data.
Use masser
SP_Configure 'Allow Updates', 1 Reconfigure with Override Go
Update sysdatabases set status = 32768 where name = 'quoted database name' Go
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'True' Go
DBCC CHECKDB ('Troubleshooting Database Name) GO
Update sysdatabases set status = 28 where name = 'quoted database name' Go
SP_Configure 'Allow Updates', 0 Reconfigure with Override Go
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'False' Go
Method Two
The cause of things yesterday, the system administrator told me that the disk space where we have internal application database is inadequate. I noticed that the database event log file XXX_DATA.LDF file has grown to 3GB, so I decrease the log file. After the failure of the contracting database, I made a maximum and fooling error since I have entered the industry: I accidentally deleted this log file! Later, I saw all the discussions and database recovery articles: "If you have to ensure that the database log file exists, it is critical, even Microsoft even has a KB article how to recover only by log files. I really don't know what I think at that time? !
This is broken! This database can't be connected, and the enterprise manager is written next to its "(quenus)". And the most important, this database never backed up. I found some of the database server for half a year ago, it can be used, but there are fewer records, tables, and stored procedures. I really hope this is just a nightmare!
Recovery steps that have no effect, attached database _Rambo speechless when there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db 2, additional database, you can use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server questioned that the data files and log files did not match, so they were unable to attach the database data file.
The DTS data is not available, and the XXX database cannot be read, and the DTS Wizard reports "Initialization Context has an error".
Emergency mode Yihong Bani said that there is no log for recovery, you can do this:
1. Set the database to Emergency Mode
2, re-establish a log file
3, restart SQL Server
4, set the application database to single user mode
5, do DBCC Checkdb
6. If there is no big problem, you can change the status of the database. Remember to turn off the modification options for the system table.
I practiced, remove the data file applying the database, re-establish a database XXX of the same name, then stop the SQL service, and then override the original data file back. Thereafter, walk according to the steps of the Yihong Mon. However, it is also unfortunate that other steps are very successful in addition to step 2. Unfortunately, after restarting SQL Server, this application database is still quilted!
However, let me be pleased, after doing this, it is able to select data, let me take a breath. However, when the component uses the database, the report says: "An error occurred: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode."
Finally, all steps to successfully restore the database to stop the SQL Server service for the emergency mode; remove the data files xxx_data.mdf of the application database; re-establish a database XXX; stop the SQL service; cover the original data file back ; Run the following statement, set the database to emergency mode; run "Use master go sp_configure 'allow updates', 1 Reconfigure with Override Go"
Execute: DBCC is executed. If DBCC outputs an error message, contact your system administrator. The configuration option 'allow updates' has been changed from 0 to 1. Run the Reconfigure statement to install.
Then run "update sysdatabase set status = 32768 where name = 'xxx'" execution results: (The number of rows affected is 1 line)
Restart SQL Server service;
Run the following statement, set the application database to Single User mode;
Run "sp_dboption 'XXX', 'Single User', 'True'"
Results of the:
The command has been successfully completed.
Do DBCC CHECKDB;
Run "DBCC Checkdb ('XXX')"
Results of the:
'Xxx' DBCC results.
'sysObjects' DBCC results.
Object 'sysObjects' has 273 rows, which are located in page 5.
'sysindexes' DBCC results.
Object 'sysIndexes' has 202 rows, which are located in page 7.
'syscolumn' 's DBCC results.
.........
Run the following statement to turn off the modification options for the system table;
Run "sp_resetstatus" xxx "
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go "
Results of the:
Update the entry of the database 'xxx' in sysdatabases, mode = 0, status = 28 (status Suspect_bit = 0),
No rows in sysdatabases are updated because the mode and status have been properly reset. There is no error, no changes are made. DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 1 to 0. Run the Reconfigure statement to install.
Re-establish another database xxx.lost;
DTS Export Wizard Run the DTS Export Wizard;
Copy Source Select EmergencyMode's database XXX, import to XXX.LOST;
Select "Replication objects and data between SQL Server Database", trying multiple times, it seems that you can't copy all table structure, but there is no data, no view and stored procedures, and the DTS wizard finally reports the replication;
So finally select "Source Database Copy Table and View", but later discover, this always can only copy a part of the table record;
So select "Data to be transferred with a query", which is missing which table record, which is
The view and stored procedures are added to the SQL statement.
This way, the XXX.LOST database can replace the original application database.
