No database log files restore SQL Server database methods two

Method One

1. Create a new database with the same name

2. Stop SQL Server again (be careful not to detach the database)

3. Overwrite the new database with the data file of the original database

4. Restart SQL Server

5. When you open Enterprise Manager, there will be doubt, regardless, execute the following statement (note modify the database name)

6. After the completion of the general access to the data in the database, at this time, the database itself generally have problems, the solution is to use

The database script creates a new database and guides the data into the line.

Use MASTER
Go
sp_configure ALLOW updates,1 reconfigure with OVERRIDE
Go
UPDATE sysdatabases SET STATUS =32768 WHERE name= the suspect database name
Go
sp_dboption suspect database name, single user, True
Go
DBCC CHECKDB (suspect database name)
Go
Update sysdatabases set status =28 where Name= the suspect database name
Go
sp_configure allow updates, 0 reconfigure with override
Go
sp_dboption suspect database name, single user, False
Go

Method Two

The cause of the thing

Yesterday, the system administrator told me that there was not enough disk space on one of our internal application databases. I noticed that the database event log file Xxx_data.ldf file has grown to 3GB, so I decided to shrink the log file. After shrinking the database and so on, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! I later read all the articles on database recovery and said, "It's important to keep the database log file in any case," and even Microsoft even has a KB article about how to recover the database by only log files. I really don't know what I was thinking at that time?!

It's broken! The database is not connected, and Enterprise Manager says "(suspect)" next to it. And most of all, this database has never been backed up. The only thing I can find is another database server that was migrated six months ago, but the application is available, but there are many fewer records, tables, and stored procedures. I wish it was just a nightmare!

Recovery steps with no effect

Attaching a database

_rambo said that when there is no active log in the deleted log file, you can do so to recover:

1, separate the suspect database, you can use the sp_detach_db

2, attach the database, you can use sp_attach_single_file_db

However, unfortunately, after execution, SQL Server challenged the data file and log file, so the database data file cannot be attached.

DTS Data Export

No, unable to read the XXX database, DTS Wizard reported "initialization context error".

Emergency mode

When there is no log for recovery, you can do this:

1, set the database to emergency mode

2, re-create a log file

3, restart SQL Server

4, the application database is set to single user mode

5, do DBCC CHECKDB

6, if there is no big problem will be able to change the database state back, remember to do not forget the system table to change the options to turn off

I practiced, the application of the data file to remove the database, to re-establish a database xxx with the same name, and then stop the SQL service, the original data file back to cover. After, follow the steps of satisfying red Childe.

However, it is also regrettable that the other steps have been very successful in addition to step 2nd. Unfortunately, after restarting SQL Server, this application database is still suspect!

But, to my relief, I was able to select the data and give me a big breath. Only when the component uses the database, the report says: "Error:-2147467259, failed to run the BEGIN TRANSACTION in database XXX because the database is in a bypass recovery mode." ”