How to recover a MSSQL database without logs

Many times, because of backup or other reasons, the log log files of the database have been lost, what should we do when the database files cannot be attached through normal SQL Enterprise Manager?

Here are two ways for you to refer to:

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 again

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.

Usemaster
Go

sp_configure ' allowupdates ', 1RECONFIGUREWITHOVERRIDE
Go

Updatesysdatabasessetstatus=32768wherename= ' Suspect database name '
Go

sp_dboption ' suspect database name ', ' SingleUser ', ' true '
Go

DBCCCHECKDB (' Suspect database name ')
Go

Updatesysdatabasessetstatus=28wherename= ' Suspect database name '
Go

sp_configure ' allowupdates ', 0reconfigurewithoverride
Go

sp_dboption ' suspect database name ', ' SingleUser ', ' 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

Unfortunately, after the execution, SQL Server challenged the data file and log file, so the database data file could not be attached.

DTS Data Export

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

Emergency mode

The pleasant Red Childe said that there is no log for recovery, you can do this:

1, set the database to Emergencymode

2, re-create a log file

3, restart SQL Server

4, the application database is set to single user mode

5, do DBCCCHECKDB

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 BeginTransaction in database ' XXX ' because the database is in a bypass recovery mode." ”

All steps in the final successful recovery
Set Database as emergency mode
Shut down SQL Server services;
Removing the data file of the application database xxx_data.mdf;
Re-establish a database with the same name xxx;
Stop the SQL service;
The original data file to be overwritten back;
Run the following statement to set the database to emergency mode;
Run the Usemaster
Go
sp_configure ' allowupdates ', 1
ReconfigureWithOverride
Go "

Execution results:
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Changed configuration option ' Allowupdates ' from 0 to 1. Please run the RECONFIGURE statement to install.

Then run "Updatesysdatabasessetstatus=32768wherename= ' XXX".
Execution results:
(The number of rows affected is 1 rows)

Restart the SQL Server service;

Run the following statement to set the application database to SingleUser mode;

Run "sp_dboption ' XXX", ' SingleUser ', ' true '

Execution results:

The command has completed successfully.

Do dbcccheckdb;

Run "dbcccheckdb (' XXX ')"

Execution results:

DBCC results for ' XXX '.

DBCC results for ' sysobjects '.

The object ' sysobjects ' has 273 rows, which are in 5 pages.

DBCC results for ' sysindexes '.

The object ' sysindexes ' has 202 rows, which are in 7 pages.

DBCC results for ' syscolumns '.

.........

Run the following statement to turn off the modification options for the system table;

Run "sp_resetstatus" XXX

Go

sp_configure ' allowupdates ', 0

ReconfigureWithOverride

Go "

Execution results:

Before updating the entries of the database ' XXX ' in sysdatabases, the pattern =0, state =28 (state suspect_bit=0),

No rows in the sysdatabases were updated because the mode and state were reset correctly. No errors, no changes made.

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

Changed configuration option ' Allowupdates ' from 1 to 0. Please run the RECONFIGURE statement to install.

Re-establish another database xxx.lost;

DTS Export Wizard

Run the DTS Export Wizard;

Copy source Select Emergencymode database xxx, import to xxx.lost;

Select "Copy objects and data between SQL Server Databases", try it a few times, just copy it. All table structures, but no data, no views and stored procedures, and the DTS Wizard finally reports replication failures;

So the last option is to copy tables and views from the source database, but it turns out that you can always copy only a subset of the table records;

So choose to "use a query to specify the data to be transferred", which is missing the table record, which guide;

Views and stored procedures are added by executing SQL statements.

So, XXX. The lost database can replace the original application database.