SQL Server no log recovery database (2 methods) _mssql

SQL Server is a relational database management system, the application is very extensive, in the process of SQL Server database operations will inevitably occur mistakenly deleted or other reasons caused by log damage, and because of the importance of data in SQL Server database, The data in the database must be recovered after the above failure occurs. The following is a way to restore the database log files.

Solution 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= ' 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= ' suspect database name ' Go

sp_configure ' allow Updates ', 0 reconfigure with override

go

sp_dboption ' suspect database name ', ' Single user ', ' false ' go

Workaround Two

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

You can do this when there is no log for recovery:

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

All steps in the final successful recovery

Set Database as emergency mode

Stop the SQL Server service;

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;

Use Master

go

sp_configure ' allow updates ', 1

reconfigure with override

go

Execution results:

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

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

Then run "Update sysdatabases set status = 32768 where name = ' 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 single user mode;

Run "sp_dboption ' XXX", ' Single user ', ' true '

Execution results:

The command has completed successfully.

u do DBCC CHECKDB;

Run "DBCC CHECKDB (' 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 '.

.........

U 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

"

Execution results:

Before updating the entry for database ' XXX ' in sysdatabases, mode = 0, state = 28 (state suspect_bit = 0), no rows in sysdatabases are updated because the pattern and state have been reset correctly. No errors, no changes made.

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

Changed configuration option ' Allow updates ' 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.

Maintain indexes on tables in SQL Server

In the use and creation of database indexes often encounter some problems, here can be used in some alternative way to solve ...

--First step: see if you need maintenance to see if the scan density/scan density is 100%

DECLARE @table_id int

set @table_id =object_id (' table name ')

DBCC SHOWCONTIG (@table_id)

--Second step: Refactoring table Index

DBCC DBREINDEX (' table name ', PK_ index name, 100)

--Redo the first step, if you find that scan density/scan density or less than 100% all indexes of the refactoring table

--not necessarily up to 100%.

DBCC DBREINDEX (' table name ', ', ', ', 100)

This is what I want to introduce to you in the SQL Server database log file recovery method, I hope to help you.