Post reply

SQL Server User databases are in Suspect Mode

  • November 24, 2007 at 2:08 pm

    #153978

    Hi,

    We are having OS upgrade from Win 2000 to Win 2003.

    In the testing phase, I am trying to move my SQL Server 2 System and 3 User Databases from Win 2000 to Win 2003 server.

    Below are the steps I've taken to perform this activity and then my User DB's are in Suspect Mode now.

    Steps taken

    1. Stopped the Services of Server A (win 2000)

    2. Copy the 2 System DB's files (Master and MSDB only, Model is not required coz the User DB's gets created with a different templet) and 3 User DB files.

    3. Start the SQL Service of Server A

    4. Stop the SQL Services of Server B (Win 2003)

    5. Copy all system DB's files as Backup

    6. Restore/overwrite the System DB files of Server B from Server A (step2) having the same file structure of Server B as Server A.

    7. Restart the SQL Services of Server B.

    Expectation:

    SQL Server B will start functioning normally and the 3 user DB's (step2) will get recognized automatically and DTS, Linked Server configuration will resume....

    Result:

    SQL Server B started fine, system db's are good but all User DB's are in Suspect Mode including 3 User DB's of Step 2.

    Please shed some light on this activity, Thanks for your time...


    Zaheer

  • November 24, 2007 at 7:43 pm

    #753787

    Hi Zaheer,

    Use the way described in the following links to transfer logins.

    http://support.microsoft.com/kb/246133

    Regards,

    Ahmed

  • November 24, 2007 at 7:52 pm

    #753788

    Hi Ahmed,

    Thanks for your reply, the issue is all User DB's are in Suspect Mode now.

    How to move Master database between win 2000 and Win 2003 servers.

    As I've followed the steps (given in my previous email) to move master db, after moving it the SQL Server started and recognizing the entries of user databases but the database status are Suspect Mode now....

    I am unable to make why the status of the user databases are in Suspect now ?

    Thanks


    Zaheer

  • November 24, 2007 at 8:00 pm

    #753789

    Hi Zaheer,

    Check the following

    http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-suspect-database/

    http://support.microsoft.com/kb/224071

    Ahmed

  • November 25, 2007 at 7:19 am

    #753810

    Have you considered moving the user daabases another way, such as backup restore or detach attach.

    Gethyn Elliswww.gethynellis.com

  • November 25, 2007 at 1:03 pm

    #753827

    I've copied the user DB files in Server B to the same location as it they were in Server A, so that when SQL Server restarts (Server B) it should recognize the User Database file locations and starts the User DB.


    Zaheer

  • November 26, 2007 at 2:58 am

    #753903

    Hi There

    Did you backup and restore the log files as well? Normally a DB will go suspect when the log file or data file is corrupt. From the description of your problem it seems like you did not bring the log files over. There is a way to move the DB's out of suspect mode but there can be data loss. I would suggest you do a full backup and a transaction log backup and restore both to the server. I hope it helps.

    Regards

    neil

  • November 26, 2007 at 1:02 pm

    #754218

    I've copied all .mdf, .ndf and .ldf files of 3 User DB's to server B in the same location as they were in Server A.

    The Master, MSDB, Model has come online and all the Jobs of Server A is now showing in Server B, also I can create a database, job's, etc. But the 3 User DBs are not recognized and are in Suspect Mode.

    The concern is why SQL Server is not recognizing the User DB's when they identified System DB's.


    Zaheer

  • November 26, 2007 at 1:06 pm

    #754219

    Hi Zaheer, Did you tried what I already post.

  • November 26, 2007 at 6:19 pm

    #754340

    Hi Ahmed,

    Not yet coz, the process which was followed has been finalized in the discussion so I am trying to find the solution from my first approach currently.

    If I do not see anymore clues or options, I shall try will the given process posted on KB : 224071

    Thanks,


    Zaheer

  • November 26, 2007 at 7:12 pm

    #754343

    I believe my answer comes a bit late, however first thing should be done when a db is marked as suspect is to run sp_resetstatus followed by a service restart. If the db is still in suspect mode then you can only restore from the latest backup. Unfortunately nothing else would be of anu help. Keep in mind the the mark "suspect" is not the same with database corruption.

    Good luck

  • November 28, 2007 at 12:45 pm

    #755110

    I could bring all 3 user DB's online from Suspect Mode by detaching and reattaching them.

    The reason of User DB's not recognized by the server was the DB Owner (The owners of the DB's were the Users).

    Intially when I copied the System and User Database files (.mdf, .ndf and .ldf) from Server A to Server B, SQL Server (Server B) recognized all system DB's and User DB's went into Suspect Mode, because

    SA is the owner of System Databases (Master, MSDB and Model) and users were the owner of User Databases.

    I detached the User DB's one after other, it has given me some error (OS error, I clicked OK), I refreshed the Database Component on Enterprise Manager (The Databases were actually got detached)

    Then I attached them by changing the Owners of User DB's to SA, and THAT's ALL....

    Thanks all for your help...


    Zaheer

  • November 28, 2007 at 12:50 pm

    #755112

    Thanks you for sahring the info.

  • December 1, 2007 at 2:32 am

    #756220

    thanks for sharing this info!!!

  • December 1, 2007 at 3:59 pm

    #756261

    Shutdown SQL server instances and start it in Single user mode. problaly you will be able to see some errors, why your user databases ar in suspect mode. Normally, the databases will be in suspect mode only when the datafiles/logfiles are not found as its defined in master DB.

    To start in single user mode

    In command window from the "....\binn" directory issue "sqlservr -c -m"

    Just to find and ensure, you keep all your users *.mdf, *.ndf, *.ldf in the same location, where you keep the files for master and msdb databases.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply