| Author |
Topic |
|
Cedricsan
Starting Member
16 Posts |
 Posted - 2004-02-26 : 07:02:12
|
| Dear all,I am trying to attach a .mdf file that I received from a client but it does not work and I need your help.I am trying to run:EXEC sp_attach_single_file_db 'FDB', 'D:\MSSQL7\MSSQL\Data\FDB_Data.MDF'but have the following error message:Server: Msg 1813, Level 16, State 2, Line 1Could not open new database 'FDB'. CREATE DATABASE is aborted.Device activation error. The physical file name 'G:\Program Files\Microsoft SQL Server\MSSQL\Data\FDB_Log.LDF' may be incorrect.I've seen similar errors in the forum but without proper solution.Am I just stuck with an unusable MDF file or is there a solution?By the way there are no G drive on my Server. Could it mean that the database was not properly detached from the original server and it's looking for this log file?I am using sql 2000 SP4 & windows 2000.Thanks in advance for your helpCedric |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 08:25:01
|
| Can you find out if the file was detached using sp_detach_db?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 08:29:18
|
| I looked for more information about the process used to create the .mdf file but with no success so far.Cedric |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 08:37:33
|
| Also try to find out if the database had only one log file. DBs with multiple log files cannot be attached again in this way.Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 08:43:08
|
| Raymond,I'm getting closer to find out who created the file. I'm not sure that anybody wants to be held responsible for this so it's hard to find out who did what.I will add the multiple log files to my list.Would I need the log files if multiple log files are used?Cedric |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 08:45:48
|
| If you had all the log files you could use sp_attcah_db, but like Derrick said it would have to have had sp_detach_db used on it.Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 08:49:06
|
| Must admit I never had a db with multiple log files, it's the erason why I asked if I needed them.I normally use sp_attach_db and it works fine. But this time it's a new client and I think that it will take some time to find out the proper person to talk to.Cedric |
|
|
|
kish
Starting Member
45 Posts |
Posted - 2004-02-26 : 08:50:42
|
| Hi,Your client may be having more than one data files.I guess that is the reason you are getting this error. |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 08:54:11
|
| From what I know they are working with SQl 2000 and have many db each linked to different clients.They sent us one of their db, just the .MDF file as specified.So I think that they indeed have many data files. Cedric |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 08:54:13
|
| It's possible Kish, but the error specifically seems to be looking for a log file.Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 08:56:47
|
| Besides this log file path is not one that could possibly exits on our server.I think it certainly means that the db was not detached before beeing copied to a cd?Am I right or missing something?Cedric |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 09:01:31
|
| Yes I'd guess you are right. Makes me wonder how they got the MDF if they didn't use sp_detach_db - did they temporarily stop SQL to copy the file then restart SQl again I wonder? Seems like a long way to go about this, why not create a backup and put that on a Cd for you?Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 09:03:20
|
| Well as I said it's a new client and I don't know how they work.It may be possible that they stopped SQl to copy the db. I've seen other clients do that.Do you think that if they used sp_detach_dn I'd better ask for a backup?Cedric |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 09:04:35
|
| That would be the only way I would ever ask for a database from a client. If you have the option, go for it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 09:06:20
|
| The one issue that always gets me with backups from a different server and in particular different SQL versions (7 -> 2000) is the SQL Collation. A backup is preferable if you know their collation sequence.Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 09:07:45
|
| Raymond,What would be your best option ? A backup, mdf file, mdf + log file?Cedric |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-26 : 09:13:13
|
| I prefer backups.Raymond |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 10:15:23
|
| Dear all,It turned out that the guy who sent me the data did stop sql, copied the mdf to a cd and then restarted SQl.Will re-supply it properly this time.Thanks a lot for your helpCedric |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 10:26:31
|
| Last question if I am sent a backup of the DB which stored procedure should I use to load/attach the backup to our server.Cedric |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 12:04:14
|
| You need to create a database. You can then use EM to click on the database and run a restore. Or you can use the RESTORE DATABASE syntax. Just look it up in BOL. Gives some good examples.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2004-02-26 : 12:06:56
|
| Thanks, I know where to look now.Should be ok. |
|
|
|
Next Page
|
Attaching .MDF file error