asked on

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?

lcohan

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

leop1212

ASKER

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?

lcohan

:( 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

leop1212

ASKER

leop1212

ASKER

here is screenshot
sqlerr.JPG

lcohan

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.

leop1212

ASKER

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?

leop1212

ASKER

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?

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial