SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error while Detaching / Attaching a Database


Error while Detaching / Attaching a Database

Author
Message
paulnamroud
paulnamroud
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 43
Hello,

I want to change the Folder/Path of my database. I read about this process over the net and I tried different methods but it keeps returning the same error.

First, I copied the files ".mdf" and ".ldf" from the old folder E:\Data to the new folder E:\MSSQL\Data. And i make sure that these files are NOT flagged as Read Only.


Second, I tried manually to do the following actions:
- Right click and detach the database.
- Then, I attached the database by seclecting the ".mdf" from the new folder. The Database is Read Only!

So I tried to run the following script to make the database as Read-Write, but it didn't work!

USE master;
GO
ALTER DATABASE MY_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MY_DB SET READ_WRITE;
GO
ALTER DATABASE MY_DB SET MULTI_USER;
GO

It returned the following error messages:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\Data\MY_DB.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\Data\MY_DB_log.LDF". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 945, Level 14, State 2, Line 1
Database 'MY_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


Third, I tried to run another script and I got the same error message!

Script:
USE master;
GO
EXEC sp_detach_db @dbname = N'MY_DB';
GO

USE master;
GO
CREATE DATABASE MY_DB
ON (FILENAME = 'E:\MSSQL\Data\MY_DB_Data.mdf'),
(FILENAME = 'E:\MSSQL\Data\MY_DB_Log.ldf')
FOR ATTACH;
GO

Error:
[red]Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\Data\MY_DB_Data.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
[/red]

Note:
- I have more than enough space on my disk (> 100GB) and my database size is around 40 MB.
- I'm using SQL Server 2008 SP2 Standard Edition (64-bit)

Can anyone help me ?

Thank you

Paul



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)

Group: General Forum Members
Points: 240816 Visits: 9673
This error can mean 2 things, either the server doesn't have read / write access to that folder. Or the file already exists.
paulnamroud
paulnamroud
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 43
I fixed one error!

The Database is Read-Only doing Detach/Attach. So when i try to make it Read-Write, it keeps returning the following error:


Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\Data\MY_DB.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\Data\MY_DB_log.LDF". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 945, Level 14, State 2, Line 1
Database 'MY_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


any clue ?
Thank you



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)SSC Guru (240K reputation)

Group: General Forum Members
Points: 240816 Visits: 9673
If you can reattach to the original server, then it means the db files are fine... and that it's a permission issue.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)SSC Guru (530K reputation)

Group: Administrators
Points: 530628 Visits: 20691
To follow on from the Ninja, the SQL Server database engine service account must have permissions to read/write in the new folder. Your account permissions do not matter. When you do this in SSMS, you are asking the SQL Server service account to actually take a lock on the MDF file and open it for read/write.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196798 Visits: 18537
paulnamroud (3/28/2011)
First, I copied the files ".mdf" and ".ldf" from the old folder E:\Data to the new folder E:\MSSQL\Data. And i make sure that these files are NOT flagged as Read Only.

Check the NTFS permissions ACLs on E:\Data and replicate them on E:\MSSQL\Data

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
paulnamroud
paulnamroud
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 43
Thank you Perry

I give it a right to the user name SQLServerMSSQLUser$xxx and now It works fine!



Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196798 Visits: 18537
paulnamroud (3/28/2011)
Thank you Perry

I give it a right to the user name SQLServerMSSQLUser$xxx and now It works fine!

That looks like one of the local groups created during the SQL server install, no matter though as the service account will have membership of that group.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search