Attach database error - SQL Server Forums

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

Other SQL Server Topics (2005)

Attach database error

Author

Topic

sureshprabhu
Starting Member

32 Posts

Posted - 2007-10-27 : 08:59:17

Hi!
I created one database on one server which system working with Win2003server. Now I tried to attach the database to another server which is working with WinXP SP2, I got this error
'Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\ASPNETDB.MDF". Operating system error 5: "5(Access is denied.)".
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'Everydayonline'. No entry found with that name. Make sure that the name is entered correctly.'
is anything i am missing while attaching? i just copied the .mdf and .log file from first system to second system. I was tried to make backup of the database and restore, but it was also not workedout. When I tried to restore the database I got this error
'Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\ASPNETDB.MDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'ASPNETDB_983ed943e1fe49e3ae7fa189b823b238_DAT' cannot be restored to 'F:\ASPNETDB.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\ASPNETDB_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'ASPNETDB_TMP_log' cannot be restored to 'F:\ASPNETDB_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.'

Please help me to do this.

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 10:22:04

The Restore will be trying to place the database in the same driver/folder as the original server - looks to be F:\ASPNETDB.MDF and F:\ASPNETDB_log.ldf - which presumably do not exist on the target Server?

Using the Restore command you can specify a different location / name.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-27 : 13:08:59

How did you try to attach db? Are e:\ and f:\ local disks on that machine?

Omarooo
Starting Member

1 Post

Posted - 2007-11-01 : 20:58:19

hi

If you'r using Windows Authentication Mode
when you detach database using Management Studio 2005
,Management Studio modify mdf file permission and delete all user from permission list except Connected User

when you attach it again it undo permission modification

To resolve this proplem add SYSTEM and SERVICE to permission list of mdf and ldf files with full control

if you want to now why SYSTEM and SERVICE mail me.

Lonster777
Starting Member

2 Posts

Posted - 2007-11-14 : 09:41:16

I am running into a similar problem. My usere create/attach and detach databases all day long. When a user detaches a database, the operating system rights on the file are overwritten and the file becomes exclusively theirs. I'm using 32bit SQL 2005 sp2 in mixed mode but the problem only seems to occur with windows authentication. The SQl server account and the users in question are all members of the same group and therefore have the same file permissions. Any ideas on how to fix?

Lonster777
Starting Member

2 Posts

Posted - 2007-11-14 : 14:58:21

It looks like this behavior is by design:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources