Re-establishing Transaction Log URGENT 1

[ncscott]

OK, so here's the story. I administer an e-commerce database. Recently my backup tape drive started to go on the fritz, ejecting every tape about 2 minutes after insertion and throwing cyclic redundancy errors. I figure the tape drive is on its last legs. So I ask the network administrator to make the necessary firewall changes so that this database can be included in the main corporate backup schema, rather than depending on its own tape drive. Long story short, he never got around to it. I went on vacation, and came back to discover that the TL file had grown to over 3 gigs, had filled up the partition, which of course seized the database.

In an effort to get it back up, a sales manager with access to this drive DELETED THE TRANSACTION LOG FILE. I mean permanently deleted it, since it was too large for the recycle bin.

So now this particular database is completely inaccessible, and shows as "Suspect" in Enterprise Manager. This is SQL 2000 by the way. And my most recent database backup is on a tape which is inaccessible because the AIT drive is fried.

I did have some older TL files backed up in another folder on the same machine, and tried placing copies of those files into the former location of the TL file and changing the filename to what SQL Server should be expecting. This gives me the following errors in the SQL Log:

"<Path>/<Filename> is not a primary database file."

"Device Activation Error. The physical filename <Path>/<Filename> may be incorrect."

Short of asking for a new tape drive to perform a restore (I'm using Veritas BackupExec), what can I do to get this database back up? Is there a way I can trick SQL into to recognize one of these older backup TL files as the proper file? Can I establish a new TL file from scratch?

I'm in trouble here, I really need to get this db back up.

 

[TonyKhela]

OK. What version is your SQL Server?
Have you tried detach and attach. Attach can 'sometimes' create the missing log file.

You can also put the database in 'emergency mode' to bring it back up without the TL (it will create a new TL).

http://www.devx.com/vb2themax/Tip/18624

Let me know how you get on.
regards
TK

 

[ncscott]

Thank you for your reply.

I was not able to use detach and re-attach to solve the problem, however I was able to place the DB in Emergency Mode and use the process described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=645143&SiteID=1

Using this method I was able to get the database back up with the data intact, and a fresh logfile.

Thanks again for your help.