We help IT Professionals succeed at work.

SQL 2008 R2 problem with DB

leop1212
leop1212 asked
on
134 Views
Last Modified: 2017-03-29
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

lcohanDatabase Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

Author

Commented:
UI

Author

Commented:
here is  screenshot
sqlerr.JPG
lcohanDatabase Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION