Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
what is the purpose of doing this ? You might end up corrupting the databases. –  Kin 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
add comment

1 Answer

up vote 4 down vote accepted
  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.

share|improve this answer
    
I checked a database state. It's RECOVERY_PENDING. –  Avant Aug 4 '13 at 18:33
2  
@Avant Did you delete the log file? If you can't get it online, restore to a recent backup. If you don't have a recent backup, that's strike two; you need to advise your customer to make better decisions. If you are stuck here see Paul's posts: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database and Emergency mode repair - the last resort –  Aaron Bertrand Aug 4 '13 at 18:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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