| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-05 : 08:49:22
|
| Mahfooz writes "HiAnother company sent us their MDF and LDF files and we tried to creat the database from those files. Is it possible to create a database from an MDF file? We are using Windows NT 4.0 with service pack 6a and MS SQL Server 7.0. The other company is using the same version." |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-05 : 08:56:13
|
| Sure! You will need to use sp_attach_db stored procedure. You can look up the syntax in BOL. |
 |
|
|
Afraid_of_Database
Starting Member
2 Posts |
Posted - 2001-12-05 : 15:08:30
|
sp_attach_db only works for those files which have been explicitly detached using sp_detach_db. But, u see, in this case i didn't even get the chance to detach as this is a completely new file for my server.quote:
Sure! You will need to use sp_attach_db stored procedure. You can look up the syntax in BOL.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-05 : 15:22:17
|
| Have you tried sp_attach_single_file_db? I know it says it only works for detached db's, but you never know.Alternately, if that doesn't work, create a database with the same name and filenames as the ones you received, then replace the newly created files with the old ones. |
|
|
|
Afraid_of_Database
Starting Member
2 Posts |
Posted - 2001-12-08 : 21:04:03
|
| Hi robvolkYes i tried sp_attach_single_file_db but as you guessed it didn't work as the file was not originally detached using this server.I have also tried to create a new database with the same name,detached both database and transaction log file, and then replaced them with the new files. But again it failed ..:( |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2001-12-12 : 13:26:26
|
| you should check your syntax. I had the exact same problem and was able to use sp_attach_db to attach a file which was sent to me from a different server. The client did not use sp_detach_db prior to sending me the file. I just used the exact syntax from BOL.cursors are like hammers - sometimes you have to use them, but watch your thumb! |
|
|
|
MuadDBA
628 Posts |
Posted - 2001-12-13 : 13:29:11
|
| What exactly is the error message you are receiving when it fails? I have done this as well, and the only problem I have urn into was when the servers used a different codepage (character set). |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-17 : 18:17:22
|
| Wait, Wait, Wait!You don't have to explicitly detach a database to reattach it. sp_attach_db will (Should) work for you.-Chad |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-18 : 11:46:57
|
| There are some (rare) cases when you can't attach a database if you have lost your log file. As far as I know, the only way to deal with this sort of a problem is to create a new database using original filenames, stop the server, overwrite the new MDF file with the original MDF, and re-start the server. The database will appear as corrupt. Then you should be able to place it in "Emergency mode" and copy all the data out.Edited by - izaltsman on 12/18/2001 11:47:45 |
|
|
|
vangman
Starting Member
2 Posts |
Posted - 2001-12-18 : 16:46:53
|
| I doubt this is the case for you, but..FYI- you can't use sp_attach_single_file_db when the database has multiple log files. |
|
|
|
jhunt
Starting Member
21 Posts |
Posted - 2002-02-27 : 21:51:00
|
| This is a pretty old thread, but in case someone needs more info on this.In this situation we created a database of the same name, then shut down SQL server. We then replaced the mdf and ldf files that had been created in the SQLroot\Data folder. Then we replaced them with the files that we had been sent. Then we restarted SQL server and it worked.Good Luck |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-27 : 23:58:06
|
| Did they not come up suspect? I have done this before, and I know it works in 6.5 (With the dat file if everything is exactly the same). But usually 7.0 and 2000 detect the change, and mark the DB suspect.-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-28 : 07:30:53
|
| I also tried the same thing jhunt did - had some databases on a trial version of SQL 7.0 - and when it expired I moved the MDF and LDF files to a new SQL installation (even moved the master database!) and they fired up no problem. I admit I was really surprised they worked right away, but they did. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-28 : 11:47:01
|
| Rob,Now that I can see. Because you moved the Master as well, it is not going to see the change (Because it is the same master).But I think if you tried it without moving Master (To a new server with a different Master, but a DB with the same name) it would come up suspect. At least I think it would.-Chad |
|
|
|
schellani
Starting Member
2 Posts |
Posted - 2002-02-28 : 15:10:25
|
| I've tried to do this.. How do you recover the data when the database is in suspect mode? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-28 : 15:14:27
|
| There is a system procedure called sp_resetstatus. It is documented in Books Online since v6.5, and in v7.0 and higher it is included in the master database. You may also find it under "suspect".READ THE INSTRUCTIONS IN BOOKS ONLINE COMPLETELY BEFORE YOU RUN IT, AND FOLLOW THE DIRECTIONS TO THE LETTER.After you run sp_resetstatus you need to stop and restart SQL Server. The database will go through the recovery process, and may take some time to complete. Do not interrupt the process as it could cause data loss. |
|
|
|
schellani
Starting Member
2 Posts |
Posted - 2002-02-28 : 16:49:59
|
| I'm tried reseting the suspect status and rebooting the server but have not been successful. I get the following message with sp_resetstatus Prior to updating sysdatabases entry for database 'irdb05', mode = 0 and status = 268 (status suspect_bit = 256).For row in sysdatabases for database 'irdb05', the status bit 256 was forced off and mode was forced to 0.Warning: You must recover this database prior to access.please adviseThanks for your help |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-28 : 17:56:07
|
| Try using sp_resetstatus again and restarting SQL Server (you don't have to reboot the computer unless Books Online says to reboot). If it fails again try it once more. After that you should contact Microsoft and open an incident to recover your database. |
|
|
|
|
Creating database from MDF file