This document will give instructions on how to recover from a deleted database log file on Microsoft SQL Server. The database this was done on was SQL Server 7 with SQL Server 2000. You will get a suspect beside the name of the database when this happens.
You may be interested in the document if one of the following happened:
You're log file got too big so you decided to shutdown SQL Server,
then delete the log file.
Salvage data from a damaged SQL data (.MDF) file. ( Someone emailed about this ).
That's the only reason I can think of right now and that is the reason I have come to figuring this out, so here goes.
Instructions
If you have a recent backup of the database, USE IT! Forget about this article and do a normal restore procedure. Otherwise read on. First of all, you are SOL if you want a full recovery. You just can'tget all the data back because the log file itself contained a lot of transactions that may never have made it to the data file.
So anyways, I read just about everything possible on this topic and nothing worked, I mean nothing. I tried going into emergency mode, running stored procedures that did squat, using sp_detach_db, then sp_attach_db, etc, even trying db_rebuild_log() (the rebuild_log thing seems to be an undocumented feature that someone must have figured out... easter egg?? not really since there are some serious warnings against using it, but hey, when you're in dire straits, you'll try anything once, right?).
This restore procedure doesn't seem to be formally documented anywhere. I guess you're just not supposed to be this stupid, but everybody makes mistakes don't they? And hey, I'm no DBA or anything! I just use the damn things. ;-)
Anyways, on to the guts of the article. I am trying to make this as simple
as possible and pulling this from memory so if there is something I am
missing, please post a comment.
There's one thing to note here and if someone could verify it, that would be great. First thing I'd like to verify is if you can just skip to step 9 right off the bat? So as soon as you get a suspect database, can you just start at step 9? Can someone please try that and let me know. SEE
UPDATE: JAN. 6, 2003 BELOW
Backup the data (.mdf) file! Just in case. We take no responsibility
for anything that happens following this procedure.
EXEC sp_detach_db 'dbname' -- this will detach the database from
the server
Restart SQL Server
The database may still be seen in enterprise manager, but just ignore
it.
Create a new database with the same name or a different name. You
will have to use a different physical file name, which is fine.
Stop SQL Server.
Rename the new data file that was created to something else (ex: add.bak
to the end)
Rename the old data file that you want to restore to the name of the
newly created file (the same name as the file you changed in the step
above)
Start SQL Server
Now the db will still be suspect but you now have a log file.
Switch to emergency mode on the database. You do this by doing the
following:
Right click on the database root node in Enterprise manager and
bring up the properties.
Under the Server Settings tab, check of "Allow modifications
to be made directly to the system catalogs".
click ok
Now go to the master database and open the sysdatabases
table.
Find the suspected database in here and modify the status column,
setting it to: 32768. This will put it into emergency mode.
stop then start sql server
Now here's the tricky part and I'm not sure how this will work on
a single install, i was lucky enough to have SQL Server 2000 installed.
But anyways, open up the Import and Export Data (DTS) program from the
start menu. And you want to copy data from the old database to a brand
new one. Just copy tables and views.
Friday, November 2, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment