Backup Transaction log without its db file !!

[ash2000in]

Hi,
I have lost my database somehow, and was set to "SUSPECT" status by the SQL2000 running on Windows2000Server. The recent db backup available is that of 2days back.
I found out somewhere that we can still try to backup the recent transaction log file (since it is on a different partition). I can restore the 'Full database' database from the last backup and then restore this transaction log - to get to the recent state.

The problem is, how should i backup the Transaction Log??
I am not so good at SQL, so detail steps might help me more.
All the help appreciated.
Thanks Ash

 

[MeanGreen]

Before trying any of this, have you tried to reset the suspect database flag to see if your database is indeed bad? There are several reasons a database might be marked suspect, but I have found that nine times out of ten after I reset the suspect database flag and run a check of the database, the database is fine.

Hope this helps.

 

[ash2000in]

I tried to reset the Suspect mode following some procedure from below :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

After this, i restarted the server, the database was not shown. As suggested in the above article, I tried to attach the database, but it failed! Hence,the option remaining is to restore from the last backup after which almost 2days work is not backed-up..which i guess can be retrieved if i can "BACKUP" its transsaction log file and apply it after i restore the databse-which i have read somewhere is possible.
What can i do to backup this transaction file?? Ash

 

[MeanGreen]

So after reseting the suspect status, your database disappeared? I must say that I have never had that happen to me. You have a backup from two days ago. To backup your transaction log, you normally need the database that goes with it. Normal command to backup transaction log is:

This example creates a transaction log backup for the MyNwind database to the previously created named backup device, MyNwind_log1:

BACKUP LOG MyNwind
TO MyNwind_log1
GO


But since you say you don't see your database, I don't think this will work. My guess is you are going to have to recover manually or lose your last two days data. Is there a MDF file still on your system for the database? You might try to use the sp_attach_DB and see if you can use that file. I really don't have any other suggestions that might help you.

Good Luck!

 

[tlbroadbent]

I don't know of any way to backup a transaction log of a database you cannot load in SQL Server. There may be 3rd party solutions to this problem. You could find a data recovery service to help this.

In the future, I recommend frequent (every hour or 30 minutes or wahtever meets your needs) transaction log backups. Review faq183-1784 - "How Do I Develop a Backup and Restore Strategy?"
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[ash2000in]

Hey Guys, I got the Solution!!! Just posting the steps below that workd for me.

(1) Save off the existing .MDF and .LDF files so that we can recover back to this point if we need to.

(2) Make sure the database in question is not seen in any Status in the Enterprise manager, if seen, drop it.

(3) Create a similar database. The new database does not have to be the same size; however, it must contain the same number of data and log files as the original.

(4) Stop SQL Server.

(5) Delete all the data files of the newly created database from step 3 so that it will fail recovery. Replace the log files of the new database with the original log file from step 1 so that you can backup the transaction log.

(6). Restart SQL Server. The database should come up in Suspect mode.

(7) Run this command from QA to back the transaction log:

Backup Log <Database-name> to Disk = <device_name> With NO_TRUNCATE

(8) Use the sp_dbremove stored procedure to remove the database.

(9) You should now be able to restore the Database from the back up (assuming u have a cop of full backup of some earlier time before db crashed) and then restore the transaction log from step (7) against it.

These steps worked for me,
Hope no one runs into this problem, and if they do, the above steps should be definitely of help.
Cheers. Ash