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.
| Author |
Topic |
|
Danny_Bowles
Starting Member
1 Post |
 Posted - 2004-05-17 : 09:22:45
|
| Hi all, Having a few problems with a database sent to me by a client, they have sent 2 mdf files on 8 cd's compressed using winRAR and once un-zipped I am having trouble restoring them. I've tried using sp_attach - EXEC sp_attach_db @dbname = N'DM_V31B00', @filename1 = N'E:\Back\DM_V31B00_1.mdf', @filename2 = N'E:\Back\DM_V31B00_2.mdf'and get the following :Server: Msg 1829, Level 16, State 3, Line 1The FOR ATTACH option requires that at least the primary file be specified.I have also tried attaching the files using enterprise manager but get a different message :"The file you 've specified is not a vaild SQL Server database file"I don't know if the files where detached before being copied onto the cd so this may be a issue. I have also tried to create a dummy database and overwrite the new datafiles with the old ones then when the database is reported as suspect run the sp_resetstatus cmd, but this didn't work either. Any ideas anyone. ThanksDanny |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-05-17 : 10:50:17
|
| First thought : you may not have all the files from the database they (the client) had. Ask the client to run a sp_helpdb "database_name" for the database - that will list the actual files underlying.Also, if they just shut down SQL and then copied the files, this may work from an attach point of view, but I have had 1 case (ironically the only time it happened, on a test server, was directly after I read a post saying that attaching files that were not detached first, might fail) where files that were simply copied, and not detached, gave me an issue when trying to attach.See if you can't get the client to make a proper backup.*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-17 : 13:46:32
|
| They have not included an .ldf file??? This is normally the extension of the log file. You use .mdf for data. It's possible they sent you two different databases. Try using:EXEC sp_attach_single_file_db @dbname = 'DM_V31B00', @physname = 'E:\Back\DM_V31B00_1.mdf'MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mahesh
Starting Member
6 Posts |
Posted - 2004-05-18 : 15:01:08
|
| try sp_attach_single_file_db instead sp_attach_db.For more information refer books online for sp_attach_single_file_dbMahesh ParanjpeSQL DBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-18 : 18:50:56
|
| That's a great idea.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-18 : 19:01:30
|
| It has 2 mdf's so you can't do a attach single file and it will need the ldf.>> I have also tried to create a dummy database and overwrite the new datafiles with the old ones then when the database is reported as suspect run the sp_resetstatus cmd, but this didn't work either. That sounds like the best bet but it won't be able to recover so you need to set it to emergency mode.update master..sysdatabases set status = 32768 where name = 'mydbname'Now you shold be able to access the data (not through enterprise manager).If that doesn't work go back and ask for a backup.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Restore problem