Wednesday, June 12, 2002

Recovering from a deleted log file on SQL Server

Introduction
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:

  1. You're log file got too big so you decided to shutdown SQL Server,
    then delete the log file.
  2. 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

  1. Backup the data (.mdf) file! Just in case. We take no responsibility
    for anything that happens following this procedure.
  2. EXEC sp_detach_db 'dbname' -- this will detach the database from
    the server
  3. Restart SQL Server
    The database may still be seen in enterprise manager, but just ignore
    it.
  4. 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.
  5. Stop SQL Server.
  6. Rename the new data file that was created to something else (ex: add.bak
    to the end)
  7. 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)
  8. Start SQL Server
    Now the db will still be suspect but you now have a log file.
  9. Switch to emergency mode on the database. You do this by doing the
    following:

    1. Right click on the database root node in Enterprise manager and
      bring up the properties.
    2. Under the Server Settings tab, check of "Allow modifications
      to be made directly to the system catalogs".
    3. click ok
    4. Now go to the master database and open the sysdatabases
      table.
    5. Find the suspected database in here and modify the status column,
      setting it to: 32768. This will put it into emergency mode.
    6. stop then start sql server

  10. 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.

And voila, this should work smoothly. Let me emphasize should.

UPDATE: Jan. 6, 2003

I just received an email from someone who tried starting at step 9 and he said it worked. If I can get someone else to confirm that, then I'll get rid of 1-8.

UPDATE: June 18, 2003

It seems that some people can just do 9-10 and some have to do all the steps.

Emails Received in Response to this Article

There have been many emails we have received about this article both sharing their experience and praising the instructions.

Read the emails here

Thursday, April 04, 2002

Installing Tomcat 4 on Sun Cobalt (or probably any server for that matter)

This document will explain how to setup/install the Jakarta
Tomcat 4.x Java Application Server
. Because as we all know, Cobalt's
are kind of messed up and are even more messed up if you decide to install
the Cobalt JDK or whatever they call it. My recommendation... don't touch
the Cobalt supplied thing as this is somewhat similar to pulling out your
Desert Eagle and shooting yourself in the foot (am I playing too much
Tac Ops?). If you have installed it you can follow the directions below
with it still installed, but when you are done installing Tomcat 4, DISABLE
the JDK. The instructions on how to do this is in the Cobalt JDK manual/user
guide. Basically just comment out all the tomcat things that it put into
your httpd.conf file. Then setup the sites that require Java using the
Tomcat WARP protocol as explained below.


First get the latest stable release from the Jakarta site. Make sure
you get the tar.gz compressed binaries, not the zip (major problems).



Install as per instructions which are pretty basic. Check to make sure
it's running on port 8080.


Connecting Tomcat to Apache...


Now we'll need mod_webapp to use Warp, the new protocol. You can use
this precompiled file, mod_webapp.so (works
on raq4r and raqXTR) or you can build it pretty easily. To build it yourself,
download the mod_webapp source from http://jakarta.apache.org/builds/jakarta-tomcat-4.0/archives/v4.0/src/
. After extracting, run ./configure in the mod_webapp dir. Then mod_webapp
will end up in Apache 1.3 dir, so copy this to /etc/httpd/modules directory.


Then add this to httpd.conf:



LoadModule webapp_module modules/mod_webapp.so

AddModule mod_webapp.c


Somewhere above your virtual hosts, i put mine below all the other LoadModule
commands. Then put the following also above your virtual hosts.


<IfModule mod_webapp.c>

WebAppConnection conn warp localhost:8008

WebAppDeploy examples conn /examples


WebAppInfo /webapp-info

</IfModule>


Then from here you "should" be able to get at your examples
directory without putting the port in.


Then add the following inside each <VirtualHost ...> tag that you
want to be able to use Tomcat.



WebAppDeploy anyuniquenameyouwant
conn /


Notice this uses the same connection that we stated above in the WebAppConnection
statement. This is fine and good, you want to use the same connection
in most cases.


And now you need a matching Host in server.xml file in Tomcat/conf dir.
MAKE SURE you put this in the right section, not the Tomcat-Standalone,
but within:


<!-- Define an Apache-Connector
Service -->

<Service name="Tomcat-Apache">

Hosts go here


</Service>


Which should be at the bottom of your server.xml file.


Each site should have something like the following:

<Host name="www.spaceprogram.com"
debug="0" appBase="/home/sites/www.spaceprogram.com/web"
unpackWARs="true">


<Context path="" docBase="" debug="0"
reloadable="false" crossContext="false"></Context>

</Host>



This will load when user surfs to www.spaceprogram.com because we didn't
specify any path or docBase.


And that's about it I guess. If I missed something, let me know, I've
been pissing around with this for about 11 hours now and I was just about
to jump out my window, but then I finally got it working. And I am writing
down my recollections here so I hope I got it all.


Check out Bluerax
if you want a good Java host.