[轉貼][MSSQL] How to recover user databases from a Suspect sta

有關資料庫的討論, 都可以在這發表哦~~
回覆文章
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

[轉貼][MSSQL] How to recover user databases from a Suspect sta

文章 tim »

How to recover user databases from a Suspect status
http://www.myitforum.com/articles/18/view.asp?id=7381

By: Svetlana Kuvshinkova
Posted On: 5/24/2004

The problem

Consider a following scenario that produces an error:


1. Hard drive, contains a database transaction log (*.ldf file) failed;
2. Fortunately, database is detached successfully from the SQL Server Enterprise Manager console through the use of sp_detach_db stored procedure;
3. After a failed hard drive replacement - sp_attach_single_file_db command is run, specifying the primary data file.



An attempt to attach primary data file result in a following error messages (returned by SQL Server 7.0):


* Server:Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'path to primary data file' may be incorrect.

* Server: Msg 945, Level 14, State 1, Line 1
Database your 'database name' cannot be opened because some of the files could not be activated.

* Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database �your database name'. CREATE DATABASE is aborted.



SQL Server 2000 returns the following error messages:


* Server: Msg 945, Level 14, State 2, Line 1
Database 'your database name' cannot be opened because some of the files could not be activated.

* Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'your database name'. CREATE DATABASE is aborted.



The solution


1. Make sure you have completed sucessfully all the prerequisite steps, i.e.: 1) database, marked as suspect was de-attached 2) failed hard drive was replaced;

2. Move de-attached database file to any safe location;

3. Delete a suspect database using SQL Server Enterprise Manager console;

4. Create a new database with the same (as the suspect database has) logical name and physical *.mdf and *.ldf file names. All the *.mdf and *.ldf files should be located in exactly the same locations (directories) as the suspect database;

5. Stop SQL Server services;

6. Replace a valid *.mdf file with suspected one (created on step 1 and 2);

7. Start SQL Server services;

8. Run SQL Server Query Analyzer and execute the following script

use master
go
sp_configure 'allow updates', 1
reconfigure with override
go



According to SQL Server BOL:

Use the �allow updates� option to specify whether direct updates can be made to system tables. By default, allow updates is disabled (set to 0), so users cannot update system tables through ad hoc updates. Users can update system tables using system stored procedures only. When allow updates is disabled, updates are not allowed, even if you have the appropriate permissions (assigned using the GRANT statement).

When allow updates is enabled (set to 1), any user who has appropriate permissions can update system tables directly with ad hoc updates and can create stored procedures that update system tables.



Probably the safest way to implement direct updates to a system tables would be starting SQL Server with minimal configuration. Running SQL server from the command prompt with sqlservr �m command result in automatic: 1) single-user mode and 2) �allow updates� option enabled by default.

9. Execute the following script

select status from sysdatabases where name = 'your database name'



Write down the value returned (for any future references);

10. Execute the following script

update sysdatabases set status= 32768 where name = 'your database name'



This code updates master . . sysdatabases table. The �status� column value set to �32768� instruct your database server to put the database (you trying to recover) into emergency mode;

11. Restart SQL Server services;

12. Make sure a database (you trying to recover) visible in SQL Server Enterprise Manager console (having �emergency mode� status). After doing this, you can enter the database and SELECT the data or use BCP to get it out. You may encounter errors while doing this, but in most cases much of the data can be retrieved;

13. Execute the following script

dbcc rebuild_log ('your database name', 'full path to a new transaction log file')



After issuing this command SQL Server should return an output similar to: Warning: The log for database 'your database name' has been rebuilt. Pay attention that DBCC REBUILD_LOG is undocumented and unsupported SQL Server command.

14. Execute the following script

use 'your database name'
go
sp_dboption 'your database name', 'single_user', 'true'
go
dbcc checkdb ('your database name', repair_allow_data_loss)
go



DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS clause performs:




15. nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss;
16. time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss;
17. allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors. These repairs can result in some data loss;
18. deletion of corrupted text objects. These repair can result in some data loss, too.



# Execute the following script

update sysdatabases set status= 0 where name = 'your database name'



# Execute

DBCC CHECKALLOC ('your database name')

and

DBCC CHECKDB ('your database name')

to verify database integrity.

According to SQL Server Books OnLine:

DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to repair these errors. However, to ensure that all errors, including allocation errors, are properly repaired, execute DBCC CHECKDB with a repair option rather than DBCC CHECKALLOC with a repair option

.

# Execute the following script

sp_dboption 'your database name', 'single_user', 'false'
go
use master
go
sp_configure 'allow updates', 0
go




The procedure described above can be applied in situation, when primary database file contains a logical errors (that can not be recovered by DBCC commands) also.

Final words of wisdom: use the procedure described above as the last chance to recover user data. Otherwise, you might damage your database. As usually, having a database backup might help you to solve all the tricky problems.

Microsoft Technet introduce one more way to repair user databases, which has been marked as suspect � resetting database suspect status with sp_resetstatus stored procedure. Do you know how? Take a look at Microsoft Technet knowleadge base articles PRB: Missing Device Causes Database to Be Marked Suspect (PSS ID Number: 180500). Here is an excerpt from this article:

Cause

At startup, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process (for example, backup software) or if the file is missing, the scenario described above will be encountered. In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.

Workaround

To work around this problem, perform the steps below. Note that the final step is critical.


1. Ensure that the device file is actually available.

2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online. For SQL Server 6.0 and 6.5, if you have not already done so, create this procedure by executing the Instsupl.sql script, found in the MssqlInstall directory. For SQL Server 7.0 and later, this procedure is created at installation by the inscat.sql script, found in the MssqlInstall directory.

3. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the MssqlInstall directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.

4. Execute sp_resetstatus in the master database for the suspect database:

use master
go
exec sp_resetstatus your_database_name



5. Stop and restart SQL Server.

6. Verify that the database was recovered and is available.

7. Run DBCC NEWALLOC, DBCC TEXTALL and DBCC CHECKDB.
多多留言, 整理文章, 把經驗累積下來.....
回覆文章