Avatar of leop1212
leop1212
Flag for United States of America 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?
Microsoft SQL Server

Avatar of undefined
Last Comment
lcohan

8/22/2022 - Mon
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
leop1212

ASKER
UI
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question