2

I have problems with database after reconnecting a hard disk:

  1. .Net application works with database.

  2. Disconnect hard disk with physical files.

  3. Make any request to the database (of course we will have an exception, Unable to open the physical file).

  4. Reconnect hard disk.

  5. Any query to the database in the future will still throw an exception, Unable to open the physical file.

How can I recover connection with physical files without restarting the SQL Server service?

  • 1
    what is the purpose of doing this ? You might end up corrupting the databases. – Kin Shah Aug 4 '13 at 17:35
  • purpose is recover connection. It happens from time to time because the database is actually located on the remote disk. – Avant Aug 4 '13 at 17:48
  • 3
    Why do you have a database on a remote disk? This is exactly why you shouldn't. Did you see what state your database is in after this incident? If it is OFFLINE try ALTER DATABASE foo SET ONLINE; - however don't be surprised if it goes into SUSPECT or worse - GET A REAL DISK. – Aaron Bertrand Aug 4 '13 at 17:56
4
  1. First, tell your customer to get a real disk. Using a remote disk this way is exactly why you are having difficulty at this stage, and it could very well lead to a corrupt / unrecoverable database. You are rolling the dice every time this drive goes offline, even if only temporarily.

  2. Next, check the status of the database:

    USE master;
    GO
    SELECT state_desc 
      FROM sys.databases
      WHERE name = N'your_database';
    

    If this if OFFLINE, and you're sure the drive is available, run:

    ALTER DATABASE your_database SET ONLINE;
    

    Now, this might not work - as I suggested the database could come up SUSPECT or it might not come online at all.

  3. If / when it does come back online, take a full backup. Schedule these (and/or log backups if in full recovery) to run much more frequently, to cover your butt.

  4. Tell your customer to get a real disk. This may work in the short term but this is a ticking time bomb.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.