Grab our RSS feeds Follow us on Twitter Join our Facebook Group Connect with us on LinkedIn
myITforum.com, Powered by You.
you are not logged in

Articles

Newslinks

Links

Downloads

Site Services

Community Forums

Discussion Lists

Article Search

Newsletter

Web Blogs

FAQs

Live Support

myITforum TV

Take a Poll

Monthly Drawing

myITforum Network

User Group Directory

Our Partners

About Us

Register

Login

BRONZE PARTNER:

BRONZE PARTNER:



Industry News:




  Home : Articles : SQL Server print | email | | Forums |   print | email | | Blogs |   print | email | | Wiki |   print | email | | FAQs |   print | email | Article Search  
How to recover user databases from a “Suspect” status


Bookmark and Share

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:

    [list=1]
  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

    [/list]

    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 Mssql\Install directory. For SQL Server 7.0 and later, this procedure is created at installation by the inscat.sql script, found in the Mssql\Install 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 Mssql\Install 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.


    Credits to:

    [list]
  • Microsoft TechNet web site: http://www.microsoft.com/technet and Microsoft TechNet (September 2003 Edition) CD-ROM.




  •   myITforum.com ©2010 | Legal | Privacy