Ms SQL Server database-doubt-read-only-recovery steps

-- SQL Server database recovery steps after doubt <br/> -- 1. recovery Procedure: <br/> --. back up the smlog_log.ldf file to another directory. <br/> -- B. rename the smlog_log.ldf file in the source directory to smlog_log_bak.ldf; <br/> -- C. run the following statement to modify the database status: <br/> use master <br/> go <br/> Update sysdatabases set status = 32768 where name = 'database name' -- modify the status, set it to emergency failover <br/> go <br/> shutdown with Nowait -- stop the database server <br/> go <br/> -- d. exit the SQL statement and restart the SQL statement in command line mode using the following code: <br/> sqlservr-C-t3608-t4022 -- enable SQL Server in safe mode <br/> -- e. run the following statement in the query analyzer to view the database status that has just been modified: <br/> select name, status from sysdatabases where name = 'database name done' <br/> -- F. run the following code to create a log file: <br/> DBCC traceon (3604) -- Trace <br/> DBCC rebuild_log ('database name', 'Log file full-path failed ') -- the file name must have the full path and extension <br/> -- DBCC rebuild_log ('prs _ MSC ', 'd:/mscsql/MSSQL/data/prs_msc_log.ldf <br/> -- G. set the database back to normal: <br/> Update sysdatabases set status = 0 where name = 'database name' <br/> -- H. after the database is restarted, run the following statement to check the database: <br/> DBCC checkdb -- use the following statement to fix an error after execution <br/> -- I. to recover a database, you must change the database to single-user mode: <br/> exce sp_dboption 'database name', 'single user', 'true' --- ('false' restores multiple users) <br/> -- J. run the following statement to restore the database: <br/> DBCC checkdb ('database name', repair_allow_data_loss) <br/> repair_allow_data_loss: a relatively advanced restoration method <br/> repair_fast: is a simple and quick solution

 

/* <Br/> the processing status is "suspicious". <br/> back up the data file and perform the following steps: <br/> 1. create a database with the same name (the data file must be consistent with the original one) <br/> 2. stop SQL server again (do not detach the database) <br/> 3. overwrite the new database with the data file of the original database <br/> 4. restart SQL server again <br/> 5. in this case, when you open the Enterprise Manager, there will be a doubt. Ignore it first and execute the following statement (change the Database Name) <br/> 6. after completion, You can generally access the data in the database. In this case, the database itself is still problematic. The solution is to create a new database using the database script, and export the data. <br/> */<br/> use master <br/> go <br/> sp_configure 'Allow updates ', 1 <br/> go <br/> reconfigure with override <br/> go <br/> Update sysdatabases set status = 32768 where name = 'questionable database name' <br/> go <br/> sp_dboption 'questionable database name ', 'Single user', 'true' <br/> go <br/> DBCC checkdb ('questionable database name ') <br/> go <br/> Update sysdatabases set status = 28 where name = 'questionable database name' <br/> go <br/> sp_configure 'Allow updates ', 0 <br/> go <br/> reconfigure with override <br/> go <br/> sp_dboption 'questionable database name', 'single user ', 'false' <br/> go

 

/* <Br/> only the MDF file Restoration Technology <br/> for various reasons, if we back up only the MDF file, recovery is a very troublesome task. <Br/> if your MDF file is generated by the current database, you may be lucky to use sp_attach_db or sp_attach_single_file_db to restore the database, however, a message similar to the following appears: <br/> device activation error. The physical file name 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf' may be incorrect. <Br/> you have created a new log file named 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf. <Br/> however, if your database files are copied from other computers, the above method may not work. You may get an error message similar to the following: <br/> server: Message 1813, level 16, status 2, Row 1 <br/> failed to open the new database 'test '. Create database will be terminated. <Br/> device activation error. The physical file name 'd:/test_log.ldf' may be incorrect. <Br/> what should I do? Don't worry. The following is an example of the restoration method. <Br/> */<br/> -- A. We use the default method to create a database for recovery (such as test ). It can be created in SQL Server Enterprise Manager. <Br/> -- B. Stop the database server. <Br/> -- C. Delete the log file test_log.ldf of the database just generated, and overwrite the generated database data file test_data.mdf with the MDF file of the database to be restored. <Br/> -- d. Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database. <Br/> -- e. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select the database server, press the right -- key, and select "properties ", on the "Server Settings" Page, select "allow direct modification to System directories. You can also use the following statement. <Br/> use master <br/> go <br/> sp_configure 'Allow updates ', 1 <br/> go <br/> reconfigure with override <br/> go <br/> -- F. set test to emergency recovery mode <br/> -- set the following command in query MANAGER: <br/> Update sysdatabases set status =-32768 where dbid = db_id ('test ') <br/> -- in this case, you can see that the database is in "Read-Only/suspicious/offline/emergency mode" and the tables in the database are displayed, but there are only system tables <br/> -- G. perform the following recovery operations to recreate the Database Log File <br/> DBCC rebuild_log ('test', 'c:/program files/Microsoft SQL s Erver/MSSQL/data/test_log.ldf ') <br/>/* <br/> If the following message is displayed during execution: <br/> server: Message 5030, level 16, status 1, Row 1 <br/> failed to lock the database to perform this operation. <Br/> DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. <Br/> your other programs are using the database. If you opened the system table of the test database by using SQL Server Enterprise Manager in step f, then you can exit SQL Server Enterprise Manager. <Br/> the correct execution should be similar to the following: <br/> warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted. <Br/> DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. <Br/> in SQL Server Enterprise Manager, the database status is "only for DBO ". Now you can access the user tables in the database. <Br/> */<br/> -- H. verify Database Consistency (Omitted) <br/> DBCC checkdb ('test') <br/>/* The General execution result is as follows: <br/> checkdb finds 0 allocation errors and 0 consistency errors (in database 'test ). <Br/> DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */<Br/> -- I. set the database to normal <br/> sp_dboption 'test', 'dbo use only', 'false' <br/> -- if no error occurs, congratulations, now we can use the recovered database normally. <Br/> -- J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager, or use the following statement to complete <br/> sp_configure 'Allow updates ', 0 <br/> go <br/> reconfigure with override <br/> go

 

-- How to restore the database to normal if a log file is faulty (lost or the file format is invalid) <br/> -- If sp_attach_single_file 'test', 'c: /program files/Microsoft SQL Server/MSSQL/data/test_log.mdf 'failure requires the following steps to complete <br/> -- 1. remove or rename the MDF file from the suspicious database! <Br/> sp_detach_db 'test' <br/> -- 2. create a database named test in the original directory again <br/> -- 3. stop SQL Service, copy the previous MDF file back to overwrite (or rename), and delete the original log file (or rename) <br/> -- 4. start SQL Service (otherwise the following statement cannot be run) <br/> -- 5. set the database to emergency mode (status = 32768) <br/> sp_configure 'Allow updates ', 1 <br/> reconfigure with override <br/> Update sysdatabases set status = 32768 where name = 'test' <br/> -- recreate a log file <br/> DBCC traceon (3604) <br/> DBCC rebuild_log ('test', 'c:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf ') <br/> go <br/> -- 6. restart SQL Service <br/> -- 7. set the database to single-user mode (the following three statements can be used) <br/> -- sp_dboption 'test', 'single user ', 'true' <br/> Update sysdatabases set status = 4096 where name = 'test' <br/> -- alter database Test Set single_user </P> <p> -- 8. check the integrity and consistency of the database. If OK, you can use <br/> DBCC checkdb (TEST) </P> <p> -- 9. set the data access permission to multi-user mode <br/> sp_dboption 'test', 'single user ', 'false' <br/> -- or alter database Test Set multi_user </P> <p> -- 10. disable advanced options <br/> sp_configure 'Allow updates', 0 <br/> reconfigure with override <br/> -- end

 

SQL2000 database offline/read-only/urgent mode repair <br/> there are two files under the data plus path, one is. another MDF is. LDF, where ,. MDF is a data file that stores data information. <br/>. LDF files are log files but are indispensable. <br/> follow these steps to fix LDF files. <Br/> 1. Stop the Service Manager. <Br/> 2. Go to the database installation path. I installed the D Drive (D:/program files/Microsoft SQL Server/MSSQL/data) and found Tiger. LDF, delete (here we will use tiger as the object to explain and delete the corresponding names for other databases) <br/> 3. Start the Service Manager <br/> 4, go to the query analyzer and <br/> enter the following statement <br/> use master <br/> go <br/> sp_configure 'Allow updates ', 1 <br/> go <br/> reconfigure with override <br/> go <br/> note that the input is correct. If this statement is executed after the input, <br/> DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. <Br/> the configuration option 'Allow updates' has been changed from 0 to 1. Run the reconfigure statement for installation. <Br/> the execution is correct. If the preceding information is not displayed, check whether the information is incorrect! <Br/> this statement is used to set the database to allow direct operating system tables. <Br/> 5. After completing step 1, set the database to the emergency recovery mode. <Br/> Update sysdatabases set status =-32768 where dbid = db_id ('tiger ') <br/> (the number of affected rows is 1) <br/> 6. Continue and re-run the Database Log (LDF) file. <Br/> DBCC rebustm_log ('tiger ', 'd:/program files/Microsoft SQL Server/MSSQL/data/tiger. ldf') <br/> note that the database files on my computer are attached to the D drive, so the path on the top is the D Drive. If the path is different, modify the file. <Br/> in this way, the LDF log file of tiger is rebuilt. <Br/> warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. <Br/> the database option must be reset and redundant log files may need to be deleted. <Br/> 7. check whether there are any errors and enter the syntax <br/> DBCC checkdb ('tiger ') <br/> The following shows <br/> checkdb found 0 allocation errors and 0 consistency errors (in database 'tiger ). <Br/> it indicates that the SQL restoration mode is successfully established in step 1. <br/> 8, set the database to normal <br/> sp_dpartition tion 'tiger ', 'dho use only', 'false' <br/> 9. Last step, in Step E, you must allow direct modification to the system directory. It is dangerous to directly operate the system table. <br/>