SQL Server: Restore the database without logs

SQL Server: Restore the database without logs
Author: chinaitlab Source: chinaitlab Date: 2008.08.04 (1 comment in total) I want to comment on the cause of the incident
　　
Yesterday, the system administrator told me that the disk space of an internal application database is insufficient. I noticed that the database Event Log File xxx_data.ldf has grown to 3 GB, so I decided to narrow down the log file. After failing to contract the database, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! Later, I saw all the articles about database Restoration: "It is critical to ensure the existence of database log files no matter what the situation is ", microsoft even has an article in KB about how to restore a database only by using log files. I really don't know what I thought at that time ?!
　　
This is broken! The database cannot be connected, and the enterprise manager says "(doubt)" next to it )". And the most terrible thing is that this database has never been backed up. The only thing I can find is another database server that was migrated six months ago. The application is usable, but many records, tables, and stored procedures are missing. I really hope this is just a nightmare!
　　
Accidental deletion of database log files or other causes of Database Log damage
　　
Method 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 master
　　
Go
　　
Sp_configure 'Allow updates', 1 reconfigure with override
　　
Go
　　
Update sysdatabases set status = 32768 where name = 'suspicious database name'
　　
Go
　　
Sp_dboption 'questionable database name', 'single user', 'true'
　　
Go
　　
DBCC checkdb ('questionable database name ')
　　
Go
　　
Update sysdatabases set status = 28 where name = 'questionable database name'
　　
Go
　　
Sp_configure 'Allow updates', 0 reconfigure with override
　　
Go
　　
Sp_dboption 'questionable database name', 'single user', 'false'
　　
Go
　　
Method 2
　　
Cause
　　
Yesterday, the system administrator told me that the disk space of an internal application database is insufficient. I noticed that the database Event Log File xxx_data.ldf has grown to 3 GB, so I decided to narrow down the log file. After failing to contract the database, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! Later, I saw all the articles about database Restoration: "It is critical to ensure the existence of database log files no matter what the situation is ", microsoft even has an article in KB about how to restore a database only by using log files. I really don't know what I thought at that time ?!
　　
This is broken! The database cannot be connected, and the enterprise manager says "(doubt)" next to it )". And the most terrible thing is that this database has never been backed up. The only thing I can find is another database server that was migrated six months ago. The application is usable, but many records, tables, and stored procedures are missing. I really hope this is just a nightmare!
　　
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 Yi Hongyi says there is no log for restoration, you can do this:
　　
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;
　　
Run "use master"
　　
Go
　　
Sp_configure 'Allow updates', 1
　　
Reconfigure with override
　　
Go"
　　
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"
　　
Go
　　
Sp_configure 'Allow updates', 0
　　
Reconfigure with override
　　
Go"
　　
Execution result:
　　
Before updating the database 'xxx' entries in sysdatabases, the mode is 0, the status is 28 (the status is suspect_bit = 0 ),
　　
No row in sysdatabases is updated because the mode and status are 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 int
　　
Set @ 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 necessarily be 100%.
　　
DBCC dbreindex ('table name', '', 100)