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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-16 : 08:48:34
|
| LInda writes "How to recover a MS SQL database (mdf) that has a corrupted trasaction log file? and the worst thing is I don't have the backup copy of the log file." |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-08-16 : 11:24:04
|
| ALWAYS BACK UP YOUR DATABASES!!! IT IS THE ONLY WAY TO BE ABSOLUTELY SURE YOUR DATABASE CAN BE RECOVERED!!!Having said that, have you tried to attach your MDF using sp_attach_db (without specifying a log file)? Try it! If it works -- wonderful. If it doesn't -- try attaching the database using sp_attach_single_file_db (but chances are it will fail also). If you can't attach your MDF, you should probably open a case with Microsoft Tech Support. If you can't afford tech support, you could try the following approach (courtesy of Glory and Ded Mazdai of sql.ru):NOTE: THIS IS NOT A SUPPORTED WAY TO RECOVER YOUR DATA! USE AT YOUR OWN RISK! 1. Create a new db with the same name, same filenames and same file locations as the original db. 2. Stop the server. Replace the MDF from the newly created database with your original MDF. 3. Start the server (the DB will probably come up as suspect -- do not try to reset its status)4. Run the following script: Use master go sp_configure 'allow updates', 1 reconfigure with override go 5. Switch your DB into emergency mode by running:update sysdatabases set status= 37268 where name = '<db_name>' 6. Restart SQL Server7. At this point the database should be accessible for reading (so you might want to script out all objects from the DB and bcp all the data out in case the rest of the steps don't succeed)8. Run DBCC REBUILD_LOG('<db_name>', '<path\name of new ldf>'). If the command is successful, SQL Server will return a warning: Warning: The log for database '<db_name>' has been rebuilt. 9. Run USE '<db_name>' GO sp_dboption '<db_name>', 'single_user', 'true' go DBCC CHECKDB('<db_name>', REPAIR_ALLOW_DATA_LOSS) go 10. Run sp_dboption '<db_name>', 'single_user', 'false' go Use master go sp_configure 'allow updates', 0 go |
 |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-09-06 : 15:12:45
|
| Is there a step missing from the script provided.Between step 5 and 6, should there be a switching of database files.Say the original with the newly created?TIA solart |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-09-06 : 16:09:13
|
| No. Files from the newly created db contain no data, so you definitely wouldn't want to overwrite the original with the newly created!The idea is to create a dummy database with the same file layout as original, and then substitute original data files for the dummy files (steps 1 and 2)... Once you do this, you can go through the rest of the steps to make the db usable.Edited by - izaltsman on 09/06/2002 16:11:16 |
|
|
|
|
|
|
|
|
Database Transaction Log File