SQL 2008 R2 problem with DB

leop1212
leop1212 used Ask the Experts™
on
I have SQL server 20008
I replaced one parturition
i drive letter are were shifted.
When I reassign drive letter now
SQL server is running I do see all DB listed but not operational
I also do see all DB Files  db and logs intact
It looks like I have to detach and attach DB back on. but when I do detach i am getting errors that DB should be on drive F
while DB on on e Drive
if  change drive letter to E than SQL service will not start
what is the best way to resolve it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Find all database files by running a command like below against your server:

USE master SELECT name, physical_name FROM sys.master_files ;

Then as described here: https://technet.microsoft.com/en-us/library/gg452698.aspx

1. Take the database you want to work with offline by typing these commands:
ALTER DATABASE YoudDBnameHere SET offline GO;

2.Move one file at a time to the new location by typing the following:
ALTER DATABASE YoudDBnameHere MODIFY FILE ( NAME = YoudDBnameHere , FILENAME = "E:\Data\YoudDBnameHere .mdf")
GO

  Repeat the previous step to move other data and log files.

3. Put the database online by typing the following commands:
ALTER DATABASE YoudDBnameHere SET online
GO

Author

Commented:
Thank you
 it looks good.
I've already done most of DB by take off line first then detach and then attach from new locations worked 90%
but a couple DB giving me an error when trying to attach .
see screenshot. Could you tell me what is wrong?
lcohanDatabase Analyst

Commented:
:( I cant see any screen shot....what do you use to attach them? UI or T-SQL script?
I would try use the UI and browse to EACH of the DB files new location
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
UI

Author

Commented:
here is  screenshot
sqlerr.JPG
lcohanDatabase Analyst

Commented:
OK so did you checked that the file exists exactly as per screen shot?
What is the size and timestamp on it? looks close with time when that event happened?
Check file attributes to make sure is not read-only and try to attach again. if you get that same error can you try copy it elsewhere? Just copy not cut/paste if is not huge and you have room somewhere else to see if is not locked by some other process.

Author

Commented:
DB file does exist and I even restore db file from sql backup to a another location but can't attached any db with that name.  I can't even create any new empty DB with that name.
it looks like when I taking it of line and dropping it some pointers to db may staid.
when I ran
USE master SELECT name, physical_name FROM sys.master_files
I don't see any pointer to DB in question.
is there another way to clear out that DB out pointer out of master ?? or another system table?

Author

Commented:
i tried copy to another location same results
I tried to restore from backup to alternative location as files and still same problem
how do I check and see if its locked by a process?
Database Analyst
Commented:
So are you saying that restoring from backup via UI and relocate the database files does not work? that does not make too much sense.
Lest try this:

Start restore process via UI and navigate to the backup file location, select that FULL backup, and where you have the DB name to restore as please change it to someting like ..._NEW then also change EACH file location as populated to a new location AND new file name so it will be like a restore WITH MOVE .

If that works all you'll need is to drop old useless db if can't be recovered anyway and rename this ..._NEW to match the old DB name.

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