Recovery of improperly detached MDF files
-
I have a client that decided to upgrade Windows the hard way. They did not detach their databases from SQL Server 2000 SP3a. The MDF's were on a separate (D: ) hard drive so they thought they were safe (no backups, either, SHEESH!). The LDF's were on the (C: ) drive. They reformatted the (C: ) drive, did the Windows installation, and reloaded SQL Server with SP3a.
How in the world do you recover the improperly detached MDF's? I tried the following on one of their smaller files...
sp_attach_single_file_db 'eraseme' ,'d:\sql_data\Stuff_Data.MDF'
...and this was the error I got...
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Stuff'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\SQL_Log\Stuff_Log.LDF' may be incorrect.
Are they toast or is there a way out of this mess?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Never mind folks... I got it... it's a bit convaluted but I'll clean up what I did in the form of step by step instructions and post it... hmmm... maybe an article?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This works so nice that I thought I'd put it out here now in case someone else needs it right away and then write the article. Man, did I get lucky!
Addmittedly, I got some of the information from another site and that person (Cmoreno) got it from some Russian SQL site and I got some of it from BOL. Then, I put it all together so there wasn't as much jumping back and forth between EM and QA. I also added a couple of "safeguards". I suppose it could be turned into some parameterized SQL but wanted to get this info out as soon as I could...
Here's the instructions and, Boy!, is my client happy I could recover his data!!!
Prepare:
1. Make sure SQL Server 2000 is up and running. (Part of the safeguard for Step 5)
2. Make a new directory off the root of your drive called SQLSafe.
3. Copy (not move) all the MDF files you wish to recover from the original directory to the SQLSafe directory. (another safequard)
4. Verify that the MDF files have been successfully copied.
5. Delete the MDF files you wish to recover from the original directory. You will not be able to delete any files that are active on the database.
Follow these steps for each MDF file you wish to recover...
1. From Enterprise Manager and logged in as SA, create a new database as indicated by the name of the MDF file you wish to recover making sure that you write down where both the MDF and LDF files are being created. For example, if the filename is MyDatabase_Data.MDF, then the database name would be (should be) MyDatabase.
2. Close Enterprise Manager.
3. Stop the server.
4. Replace the new MDF file with a copy of the MDF file from the SQLSafe directory (Obviously, you should know where the new file was created from step 1.) Again, to be on the safe side, this should be a COPY and not a MOVE.
5. Delete the new LDF File (location identified in Step 1)
5. Start the server
6. Login to Query Analyzer as SA, load the following script, and change all occurances of <dbname> to the database name from Step 1. You MUST be logged in as SA or someone with SA rights to the server.
- USE MASTER
- GO
- EXEC sp_Configure 'ALLOW UPDATES', 1
- GO
- RECONFIGURE WITH OVERRIDE
- GO
- UPDATE SYSDATABASES SET STATUS = 32768 WHERE NAME = '<dbname>'
- GO
- EXEC sp_Configure 'ALLOW UPDATES', 0
- GO
- RECONFIGURE WITH OVERRIDE
- GO
- EXEC sp_DBOption '<dbname>', 'SINGLE USER', 'TRUE'
- GO
- EXEC sp_DBOption '<dbname>', 'SELECT INTO/BULKCOPY', 'TRUE'
- GO
- DBCC REBUILD_LOG ('<dbname>', '<path>\<dbname>_Log.ldf')
- GO
- EXEC sp_DBOption '<dbname>', 'DBO USE ONLY', 'FALSE'
- GO
- EXEC sp_DBOption '<dbname>', 'SINGLE USER', 'TRUE'
- GO
- DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)
GO
EXEC sp_dboption '<dbname>', 'SINGLE USER', 'FALSE'
GO
7. Replace <path> with the full drive/path to where the log file in Step 1 is located.
8. Execute the script.
9. Open Enterprise Manager and change the growth settings to the way you want them and you're done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The Process you followed works perfectly fine, but I would like add few points of caution here.
These steps should be followed as a last resort because the script uses 2 very dangerous un-documented (un-supported if run without Microsoft Recommendation) commands i.e DBCC Rebuildlog and DBCC CheckDB with Repair_Allow_Data_loss option.
When you loose the T Log file , you should first try attaching it from the SQL Enterprise Manager (Make sure the Path of the Log file is existing, I mean if the log file was previously located at C:\MSSQL\Data, then this drive and folder should exist, other wise change the path to an existing location). In most of the cases this method succeeds.
If this fails, try running sp_attach_single_file_db <db name>, <mdf file>.
If this fails, try restoring from the Backups.
If there are no latest backups, then is the time to run the DBCC RebuildLog. But then you should explain the Client about the possible Logical Data Corruption that might be caused by this command. (if there were any Uncommitted Transactions at the time the database was last used and improperly closed, then the changes made by these Uncommitted transactions will stay in the database causing logical data inconsistency. 
Make sure that you check for any Foreign Key violations, Known Data inconsistency (very tedious work).Running DBCC CheckDB with Repair_Allow_Data_Loss is not required in this case since the Data File was in safe place. This command is very dangerouos and should be used very sparingly because this drops the pages that have errors and fixes the Physical inconsistency, but causes a great deal of Logical Data inconsistency.
Maintaining the Logical Data Consistency is an important aspect in recovering the Data.
Hope this helps,
M.S. Reddy
-
Reddy,
Thanks for the advice especially about the Repair_Allow_Data_Loss option... I'll remove that holding it for the very last stab.
Understood about the other stuff... I did try attaching in EM, using the single file attach, etc. Wouldn't have asked the question unless I tried all the normal routes and they failed. It was quite a pickle.
Again, thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Excellent reply, the topic saved my data recovery time a lot. thanks
-
outstanding - thank you for helping us to not have to reinvent the wheel on this.
-
Thanks for the feedback, folks. Glad it helped.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I used DBCC REBUILD_LOG command 5 times in 3 years and only twice database recovered without any consistency errors..
So becareful about it and withour running DBCC CHECKDB or CHECKTABLE do not grant the user access for production use...
MohammedU
Microsoft SQL Server MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply