lost disk for transaction log - suspect DB

[csh]

We lost the disk that hosted the transaction log. The database is now marked as suspect. Is there anyway to get out of this situation other than recreating the database and recover from the last restore?

[Lodie]

I don't know which version you have but with sql7 is it possible to rebuild the log maybe will that help you.

the procedure to rebuild a transaction log for a SQL Server 7.0 database using
the DBCC REBUILD_LOG command:

procedure
1. Allow updates to system tables:

sp_configure allow, 1
reconfigure with override
go

2. Set the database in emergency mode (note it is not negative in SQL Server
7.0):

update sysdatabases set status = 32768 where name = &#39;<db_name>&#39;

3. Stop and restart SQL Server.

4. Rebuild the log:

dbcc traceon (3604)
dbcc rebuild_log(&#39;<db_name>&#39;,&#39;<log_name>&#3 9
go

There are two things to note concerning the <log_name> parameter:

- The <log_name> to be specified is the physical path to the new log
file, not a logical file name. If you do not specify a full path, the new log
will be created in the Windows NT system root directory (by default, this is
the WinntSystem32 directory).

- If the physical file name already exists, REBUILD_LOG will fail with the
following error message:

Server: Msg 5025, Level 16, State 1, Line 2
The file &#39;D:mssqldatapubs_log.ldf&#39; already exists. It should be renamed or
deleted so that a new log file can be created.

You will need to specify a different file name or rename or delete the
existing one.

------------
csh at 1/2/2002 5:31:40 PM

We lost the disk that hosted the transaction log. The database is now marked as suspect. Is there anyway to get out of this situation other than recreating the database and recover from the last restore?

[CH]

This is a 6.5 database which does not support the sp_detach_db or attach. I am not sure that we
have a backup that reflected the current database. This is a development database that was shelved
long ago and as luck would have it, now there is an interest in it. Since the database is marked
suspect I can not do a backup or transfer. Is there a work around for this?


------------
csh at 1/2/2002 5:31:40 PM

We lost the disk that hosted the transaction log. The database is now marked as suspect. Is there anyway to get out of this situation other than recreating the database and recover from the last restore?

[Anu]

Hi,

This may be a weird.
But now being the worst situation, use sp_configure to allow updates,
go to master..sysdatabases,change the status of the affected database to 16 or 8 and try doing object transfer to a different new database.

May be this helps,

-Anu



------------
csh at 1/2/2002 5:31:40 PM

We lost the disk that hosted the transaction log. The database is now marked as suspect. Is there anyway to get out of this situation other than recreating the database and recover from the last restore?