SQL Server no log recovery database (2 methods), SQL Server

SQL Server no log recovery database (2 methods), SQL Server

SQL Server is a relational database management system and is widely used. During database operations on SQL Server, logs may be accidentally deleted or damaged due to other causes, due to the importance of data in the SQL Server database, data in the database must be restored after the above faults occur. The following describes how to restore a database log file.

Solution 1

1. Create a database with the same name

2. Stop SQL server again (do not detach the database)

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

4. Restart SQL server.

5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)

6. After completion, You can generally access the data in the database. At this time, the database itself usually needs problems. The solution is to use

The database script creates a new database and imports the data.

USE MASTERGOSP_CONFIGURE 'Allow updates', 1 reconfigure with overridegoupdate sysdatabases set status = 32768 where name = 'questionable database NAME 'gosp _ dbobtion 'questionable database name', 'single user ', 'true' GoDBCC CHECKDB ('questionable database name') Goupdate sysdatabases set status = 28 where name = 'questionable database name' Gosp _ configure 'Allow updates ', 0 reconfigure with overrideGosp_dboption 'questionable database name', 'single user', 'false' Go

Solution 2

Ineffective recovery steps

Additional database

_ Rambo: when there is no activity log in the deleted log file, you can restore it as follows:

1. to detach a suspicious database, use sp_detach_db.

2. Add a database. You can use sp_attach_single_file_db.

However, it is a pity that after execution, SQL Server will question the inconsistency between the data file and the log file, so it is impossible to attach the database data file.

DTS data export

No. The XXX database cannot be read. DTS Wizard reports "An error occurred in the initialization context ".

Emergency Mode

When no log is used for restoration, you can do the following:

1. Set the database to emergency mode.

2. Create a New log file.

3. Restart SQL Server.

4. Set the application database to single-user mode.

5. Perform DBCC CHECKDB

6. If there is no major problem, you can change the database status back. Remember to disable the system table modification option.

I tried to remove the data file of the application database, re-create a database XXX with the same name, stop the SQL service, and overwrite the original data file. Then, follow the steps in yihongyi.

However, it is also a pity that, apart from step 1, other steps are very successful. Unfortunately, after restarting SQL Server, this application database is still in doubt!

However, I am glad that, after doing so, I can Select the data and let me breathe a sigh of relief. However, when the component uses the database, the report says, "an error occurs:-2147467259, failed to run begin transaction in database 'xxx' because the database is in avoidance recovery mode ."

All steps for successful recovery

Set the database to emergency mode

Stop the SQL Server service;

Remove the data file XXX_Data.mdf of the application database;

Re-create a database XXX with the same name;

Stop the SQL service;

Overwrite the original data file;

Run the following statement to set the database to emergency mode;

Use MasterGosp_configure 'allow updates', 1reconfigure with overrideGo

Execution result:

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

The configuration option 'allowupdates' has been changed from 0 to 1. Run the RECONFIGURE statement for installation.

Then run "update sysdatabases set status = 32768 where name = 'xxx '"

Execution result:

(The number of affected rows is 1)

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 result:

The command is successfully completed.

Ü perform dbcc checkdb;

Run "dbcc checkdb ('xxx ')"

Execution result:

The DBCC result of 'xxx.

The DBCC result of 'sysobjects.

The object 'sysobjects' has 273 rows on 5 pages.

The DBCC result of 'sysindexes.

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

The DBCC result of 'syscolumns.

.........

Ü run the following statement to disable the system table modification option;

Run

“sp_resetstatus "XXX"gosp_configure 'allow updates', 0reconfigure with overrideGo”

Execution result:

Before updating the database 'xxx' entries in sysdatabases, the mode is 0, the status is 28 (the status is suspect_bit = 0), and no row in sysdatabases is updated, because the mode and status have been correctly reset. No error. No changes were made.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

The configuration option 'allowupdates' has been changed from 1 to 0. Run the RECONFIGURE statement for installation.

Re-establish another database XXX. Lost;

DTS export wizard

Run the DTS export wizard;

Copy the source to select the EmergencyMode database XXX and import it to XXX. Lost;

Select "copy objects and data between SQL Server databases" and try it multiple times. It seems that it cannot be done. It just copies all the table structures, but there is no data or views and stored procedures, in addition, the DTS wizard finally reports a copy failure;

Therefore, we finally chose "Copy tables and views from the source database", but later we found that we can only copy some table records;

Therefore, select "Use a query to specify the data to be transmitted" and export the missing table records;

Views and stored procedures are added by executing SQL statements.

Maintain the index of a table in SQL Server

Some problems are often encountered in the use and creation of database indexes. here we can use some alternative solutions...

-- Step 1: Check whether maintenance is required and whether the Scan Density/Scan Density is 100%

Declare @ table_id intset @ table_id = object_id ('table name') dbcc showcontig (@ table_id)

-- Step 2: reconstruct the table Index

Dbcc dbreindex ('table name', pk_index name, 100)

-- Redo the first step. If the Scan Density/Scan Density is less than 100%, rebuild all the indexes in the table.

-- It may not be 100%

Dbcc dbreindex ('table name', '', 100)

This is the recovery method of log files in the SQL Server database that I want to introduce to you. I hope this will help you.