March 28, 2011 at 10:28 am
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_DBSET 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
March 28, 2011 at 10:32 am
This error can mean 2 things, either the server doesn't have read / write access to that folder. Or the file already exists.
March 28, 2011 at 10:50 am
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
March 28, 2011 at 10:53 am
If you can reattach to the original server, then it means the db files are fine... and that it's a permission issue.
March 28, 2011 at 11:30 am
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.
March 28, 2011 at 12:32 pm
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" 😉
March 28, 2011 at 1:18 pm
Thank you Perry
I give it a right to the user name SQLServerMSSQLUser$xxx and now It works fine!
March 28, 2011 at 1:54 pm
paulnamroud (3/28/2011)
Thank you PerryI 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" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply