I''m getting error when attaching database

Question

I''m getting error when attaching database

andy-232663

SSChasing Mays

Points: 603
More actions
June 8, 2005 at 5:00 am

#164008

Hello all,
I'm new to SQL Server administration. Please help me.
I'm getting error when i try to attach database to different network drive. Error says ..
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name ' E:\sqldata\test11_db.mdf' may be incorrect.
Can anyone help me , its urgent.
Initially i got the error when i was detaching database.
Thanks in advance.
Kind Regards,
Nar

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Rick Phillips-220796 SSC Eights! Points: 997 More actions · Answer 1

The following command should work assuming that the database was named test11_db previously and that the stated files are present (Based on logical drives on your SQL server).

EXEC sp_attach_db @dbname = N'test11_db',

@filename1 = N'E:\sqldata\test11_db.mdf',

   @filename2 = N'F:\sqldata\test11_db.ldf'

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

If you are not logged in to the machine using the same account as SQL Server is running as, then there is most probably not an E: drive available for SQL Server to use.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hi,

Is your E: drive a network or local drive? If it is a network drive then with one of the following errors

5105 "Device Activation Error"

-or-

5110 "File 'file_name' is on a network device not supported for database files."

See the following link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

INF: Support for Network Database Files

Network databases are not supported by default and not recommended.

"......This behavior is expected. Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files"

Yelena

Regards,Yelena Varsha

Rick Phillips-220796 SSC Eights! Points: 997 More actions · Answer 4

You do not need to be logged onto the server with the account that SQL server uses. The logical drives during the SP_ATTACH_DB will be taken with respect to the account that SQL is running under.

Generally when attaching databases, I have used Query Analyzer using the SA account(Since we were in mixed mode authentication and we had a standard that all databases were owned by SA) this would make the database owner "SA".

If I logged in through NT Aunthentication then the database would end up being owned by my NT account and I would have to do a SP_CHANGEDBOWNER to reset ownership to "SA".

A good way to cross check that the standard naming is correct would be to run a SP_HELPDB on another user database on the server. This will show the logical drives that are being used.

Rick Phillips

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Rick,

he may be trying to attach to the network drive, see my reply above, we posted our replies at the same time - Yelena

Regards,Yelena Varsha

Jeff W SSCertifiable Points: 5207 More actions · Answer 6

If he is trying to connect to a Network drive (UNC) then the trace flag is going to have to be set and the RDBMS restarted. SQL Server (for obvious reasons) does not automatically let you place data files across a network.

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

If this isn't the case, then like was suggested earlier, use the T-SQL commands. If for no other reason, you will get a more precise error message. Usually the T-SQL command solve all woes. Also check the spelling and that the files exist.

"Keep Your Stick On the Ice" ..Red Green

Spring SSC Enthusiast Points: 131 More actions · Answer 7

After detaching the database move the .mdf and .ldf files from current locations to to your desired locations (cut and Paste), then run the sp_attach command.

Thanks,