sql server moved database files now it says recovery pending

jackjohnson44
jackjohnson44 used Ask the Experts™
on
I moved my database files from my C drive to my J drive, then ran this code to move the database locations.  It appeared to work, but my databases all say "Recovery Pending".  If I try to open one, I get an error that says The database HOD200301 is not accessible. (ObjectExplorer).

How can I fix this?

ALTER DATABASE  HOD200203 MODIFY FILE (NAME = HOD200203 , FILENAME = 'J:\OptionDB\HOD200203.mdf ');
ALTER DATABASE  HOD200203 MODIFY FILE (NAME = HOD200203_log , FILENAME = 'J:\OptionDB\HOD200203_log.ldf ');
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
I usually prefer backup databases then drop db and restore in new folder

Or detach db then stop SQL service, move files then attach the db

In your case seems that alter database is done to db hod200203 while error is on db hod2002031 there is a 1 on the final .... Maybe is this the problem

Another kind of similar problem can occurr when you have moved files without stopping the service.... When I've done such things on SQL 2000 the db got corruption and had to restore a backup !!!!
jackjohnson44

Author

Commented:
I am actually detaching them all now then I am going to recreate, like you said.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial