SQL 2008 R2 problem with DB

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?
leop1212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
0
leop1212Author 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?
0
lcohanDatabase AnalystCommented:
:( 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
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

leop1212Author Commented:
UI
0
leop1212Author Commented:
here is  screenshot
sqlerr.JPG
0
lcohanDatabase AnalystCommented:
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.
0
leop1212Author 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?
0
leop1212Author 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?
0
lcohanDatabase AnalystCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.