Post reply

Restored, rebooted and now the databases come up as "suspect"

  • August 31, 2006 at 2:56 am

    #62701

    Hi Guys,

    I restored 2 databases from backups. they were working fine untill I had to reboot the box as the mouse just hung for some reason. After the reboot, both my databases are showing up as "suspect".

    Then I did some research, and found that the best way to get back to normal is to restore the database from the original backup. I tried restoring the first database, with replace, using the following command :

    RESTORE DATABASE SUNDOMAINDB

       FROM DISK = 'D:\SUNDOMAINDB_db_200608182014.BAK'

       WITH MOVE 'SUNDOMAINDB' TO 'F:\SQL\Data\MSSQL\SUNDOMAINDB_Data.MDF',

       MOVE 'SUNDOMAINDBlog' TO 'H:\SQL\Data\MSSQL\SUNDOMAINDB_Log.LDF'

    ,REPLACE

    and I get the following error:

    Server: Msg 5105, Level 16, State 2, Line 1

    Device activation error. The physical file name 'F:\SQL\Data\MSSQL\SUNDOMAINDB_Data.MDF' may be incorrect.

    Server: Msg 3156, Level 16, State 1, Line 1

    File 'SUNDOMAINDB' cannot be restored to 'F:\SQL\Data\MSSQL\SUNDOMAINDB_Data.MDF'. Use WITH MOVE to identify a valid location for the file.

    and same for the ldf file as well.

    Now the thing is that the F and H drives as SnapDrive Virtual disks as we are trying to move to SAN. My SQL error log says:

    2006-08-31 11:33:27.01 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\Sql\Data\SUNDOMAINDB_Data.MDF.

    2006-08-31 11:33:27.09 spid11    FCB:pen failed: Could not open device F:\Sql\Data\SUNDOMAINDB_Data.MDF for virtual device number (VDN) 1.

    2006-08-31 11:33:27.09 spid12    Starting up database 'Staging'.

    2006-08-31 11:33:27.09 spid12    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\SQL2000\Data\Staging_Primary_Data.MDF.

    2006-08-31 11:33:27.09 spid12    FCB:pen failed: Could not open device F:\SQL2000\Data\Staging_Primary_Data.MDF for virtual device number (VDN) 1.

    2006-08-31 11:33:27.10 spid12    Device activation error. The physical file name 'F:\SQL2000\Data\Staging_Primary_Data.MDF' may be incorrect.

    2006-08-31 11:33:27.15 spid5     Clearing tempdb database.

    2006-08-31 11:33:27.25 spid11    Device activation error. The physical file name 'F:\Sql\Data\SUNDOMAINDB_Data.MDF' may be incorrect.

    2

    I have checked the drives, the folders and the files, they are all there. I can access them from the server without any issues. I can successfully copy new files to these folders, and delete them. No problems in any way I can see.

    Any comments? Any ideas? Your help is highly appreciated.

  • August 31, 2006 at 3:13 am

    #657752

    Looks like SQL Server can't see the disks, if you try to create a new database can you see the F Drive?

    I've had a db show as suspect because it couldn't read from the drive before now.

    Regards

    Carolyn

     

     

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • August 31, 2006 at 3:34 am

    #657754

    Thanks carolyn. I was able to restore 1 database successfully. So, the other one was still marked "suspect". I wanted to test if the reboot would cause a problem again, and guess what? Now both the databases are in normal mode.

    I am not exactly sure what happened here. possibly the first reboot wasn't a clean reboot! But now I can't identify what caused the problem in the first place, so as to be careful in future!

  • September 5, 2006 at 11:43 am

    #658620

    Sometimes, SQL Server seems to 'misread' the disk when the databases are attached (a reboot really does a hard detach and reattach). I've had this problem and solved it by just detaching and reattaching the suspect database.

    -SQLBill

Viewing 4 posts - 1 through 3 (of 3 total)

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