| Author |
Topic |
|
koln5
Starting Member
29 Posts |
 Posted - 2002-12-30 : 15:36:44
|
| I detached a DB from server A, while on a client machine. As I now try to re-attach, I get this error:Server: Msg 5105, Level 16, State 4, Line 1Device activation error. The physical file name 'E:\mssql7\data\ABC_data.mdf' may be incorrect.When I go to the Server and try to log in, it wont let me, since the default dbase is the same as the one I detachedWhat to do now ??...go into isql ? and change the default dbase ?How, and where do start isql ?Thank you in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-30 : 15:42:39
|
| It sounds like you have a bad file. Do you do a database backup prior to the detach, which you should ALWAYS do before a detach? Please post your attach code so that we can see if the code is the problem. |
 |
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2002-12-30 : 15:52:04
|
| You should be able to log in using the sa system account. Then you can change the default dbase.Also make sure that the phisical file is on the directory used in the attach db. |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2002-12-30 : 15:52:32
|
| Here it is:sp_attach_single_file_db @dbname = 'ABC',@physname = 'E:\mssql7\data\ABC_data.mdf' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-30 : 16:02:28
|
| Well does the ABC_data.mdf file exist in the E:\mssql7\data directory?I am not sure what the default database has to do with this scenario. |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2002-12-30 : 16:16:26
|
| the file resides on the server box, it does not reside on my client machine.When I walk ove to the server box, I cannot start EM or QA, since the ABC database, was my default dbase, when starting SQL Server.What can I do ? |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2002-12-30 : 16:23:26
|
| I'm sorry, I can't realize why the Query Analyze or the EM does not come up. Provided you have registered the SQLServer in the EM using the SA or a System wide administrator, EM MUST come up. Also, the default database for the sa user is the master. If you not changed it to the databases that you now cant attach, you or your DBA should be able to log in and change the default dbase.Are you the DBA? If so, you can log in using the SA system account and change the default db. This is your first step. |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2002-12-30 : 16:28:23
|
| Well, even when you are connected to SQLServer using a client, or when you issue a command that makes reference to a directory, the SQL Server will understand it as a Server directory, not a client. So, the E:\mssql7\... dir must exist on the server. Does not matter if it exists on your box or not, neither if it was dettached from the server or from the client.We're searching and testing to help youSérgio |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-30 : 16:32:25
|
| When you run the attach command, the path that you provide is for the server and not for the client machine. Verify on the server, not by using EM or QA but through Explorer or CMD, that the file exists in the directory. You must specify the path where the file exists. If you do not want the file to reside there, then move the file, and then run the attach command and pass it the new path. This path also cannot be mapped network drive. It must be a local drive on the server. As far as the default database problem goes, do as shsmonteiro suggests. But I'm confused how you are able to even get this error - Server: Msg 5105, Level 16, State 4, Line 1 Device activation error. The physical file name 'E:\mssql7\data\ABC_data.mdf' may be incorrect. - when you must have started QA in order to get the error. Please explain further. |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2002-12-30 : 16:47:31
|
| Kin,At the time you detached the database, it has some log file? If so, you should attach it together, using the sp_attach_db proc, not the sp_attach_single_file_db.Sérgio Monteiro |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2002-12-30 : 17:13:36
|
| Good questino TDUGGAN I was in QA..default dbase was master, when I ran the detach script...then I wanted to re-attach, and I got my error msg....So currently Im still in QA on the client machine, but am not able to log in when I walk over to the Server machineand SHS..yes, it did have a log file, which I wanted to delete (since it had grown)...but I didnt delete it yet, just renamed...so technically I only had one file...should I rename my log file and use the 'sp_attach_db' procedure ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-30 : 17:16:51
|
| You might want to also try attaching the database to another SQL Server machine (just copy the file to another machine and run the statement). |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2002-12-30 : 17:28:15
|
| Yahoo !!!Just by chance, I turned of the Server box...and then back on....after that I was able to log onto QA and re-attach again....really weird.TDUGGAN and SHSMONTERIO: Thank you for all your input !!!, greatly appreciated ! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-30 : 18:08:23
|
| The file was probably still in use by SQL Server. So by doing a reboot, it released the file. It should have released the file when you did the detach, but who knows what happened.Congrats! |
|
|
|
|
URGENT: attaching a db from client machine.