Error while Detaching / Attaching a Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error while Detaching / Attaching a Database

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2011-03-28 : 11:53:35

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:

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)".

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-28 : 12:00:13

Does SQL Server have permission to those folders? It needs full permissions

--
Gail Shaw
SQL Server MVP

paulnamroud
Starting Member

26 Posts

Posted - 2011-03-28 : 12:04:32

I thinks Yes it does! Because it contains another Databases that are working without any problems!

paulnamroud
Starting Member

26 Posts

Posted - 2011-03-28 : 12:52:08

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

paulnamroud
Starting Member

26 Posts

Posted - 2011-03-28 : 15:20:58

Now it works fine!

It was a NTFS permissions ...

So I set the same permission to both folders.
For the new folder I give it as a right to the user name SQLServerMSSQLUser$xxx and It works fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources