| Author |
Topic |
|
khcrookie
Starting Member
7 Posts |
 Posted - 2005-03-25 : 12:29:07
|
| Recently had a HD crash on her SQL server, unfortunately, whoever set it up was backing up BAK and TRN to same drive and also was not the instance that was LIVE (it was a TEST).I recovered from a DRS (Data Recovery Service) the original .MDF from the LIVE side.My problem now is how do I get that pure .MDF file into the database instance. I've looked at DTS but it doesn't allow me to choose a .MDF as my source data. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-25 : 12:57:06
|
| sp_attach_single_file_db. See Books Online for details on its use. |
 |
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-25 : 16:40:44
|
| Tried, here's what I got.Server: Msg 1813, Level 16, State 2, Line 1Could not open new database 'dentrix'. CREATE DATABASE is aborted.Device activation error. The physical file name 'E:\MSSQL_live\Data\Dentrix_Log.LDF' may be incorrect. |
|
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-25 : 16:42:10
|
| The only file that was recoverable from the crash was the .MDF file, the log file (.LDF) was not. According to what I read, it should allow me to attach the single file .MDF and then the log file would automatically be created for me> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-25 : 16:42:51
|
| So did you put the MDF into the E:\MSSQL_live\Data directory, then run:EXEC sp_attach_single_file_db @dbname = 'Dentrix', @physname = 'E:\MSSQL_live\Data\Dentrix_Data.mdf'I'm assuming the mdf is named Dentrix_Data.mdf; if it's not, change the name.Make sure that the ldf does not exist in the Data directory prior to running sp_attach_single_file_db as that stored procedure will create it for you.Tara |
|
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-26 : 14:00:19
|
| Thanks, since my last post, I have created a new database (DENTRIX_DATA.MDF), then copied the recovered database overtop it. The database is there but in a (SUSPECT) state. I then used DTS to copy the tables/views to another database called DENTRIX2_DATA.MDF. I am now able to access the database and most of the files are there. I noticed some of the tables/views errored out during the DTS process, they would start to copy but would not complete. Is there anyway to fix/rebuild in the SUSPECT database so they could be copied to another without error or are these tables/views damaged beyond repair. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-26 : 14:05:35
|
| Yes, look up "suspect" and "sp_resetstatus" in Books Online. Read everything about them at least twice. FOLLOW THE PROCEDURES TO THE LETTER, MAKE SURE YOU RESTART THE SERVER AS DIRECTED. If you cannot do the entire procedure as described, do not even start it. |
|
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-26 : 14:15:54
|
| OK, thanks, I completely understand. |
|
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-28 : 07:11:40
|
| Still shows suspect. As I stated above, I have found a way to copy Table/Views from the old (SUSPECT) to a new one and most of my data has been recovered. BUT on a few tables/views, I receive the following in the copy process "Error at Source, Row #93701, I/O Error (bad page ID) detected during read at offset 0x0000003ae7e000 in file ?????.MDF, is there any other way to fix the actual tables in the source database, then copy to the new one. |
|
|
|
khcrookie
Starting Member
7 Posts |
Posted - 2005-03-28 : 10:32:07
|
| Database now in "Emergency Mode" ? How do I get it out of this mode to normal mode. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-28 : 19:25:31
|
| About the only thing you can do is to run DBCC CHECKDB and see if it reports errors. If it does, you can re-run it with the REPAIR_REBUILD setting. Read up on it in Books Online first so you are familiar with how it works. If it fixes the errors, MAKE A FULL DATABASE BACKUP IMMEDIATELY, to a safe location. If it doesn't fix it, you can try contacting Microsoft and see if they can help or recommend a third party who can. |
|
|
|
|
Help Needed