"Sreejith G" wrote:

> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
>
> Below is the syntax and after detach you need not have to execute sp_attach
>
> EXEC sp_detach_db @dbname = 'pubs'
> EXEC sp_attach_single_file_db @dbname = 'pubs',
> @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
>
> Can you answer the below questions.
>
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
>
>
> "Oskar" wrote:
>
> > Sorry, but neither of your suggestions work:
> >
> > sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> > 'C:\TEMP\TestDB1_Log.LDF'
> >
> > returned this error
> >
> > Server: Msg 5004, Level 16, State 2, Line 1
> > To use ALTER DATABASE, the database must be in a writable state in which a
> > checkpoint can be executed.
> > ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
> > 'C:\TEMP\TestDB1_Log.LDF' )
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > and I can't backup the transaction log because it is not accessible:
> >
> > backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> >
> > returns
> >
> > Server: Msg 3447, Level 16, State 1, Line 1
> > Could not activate or scan all of the log files for database 'TestDB1'.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > BACKUP LOG is terminating abnormally.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> >
> > So it seems, that only option I have in this case is to restore from backup.
> >
> > -- Thanks, Oskar
> >
> > "Sreejith G" wrote:
> >
> > > Is the DB currenlty in Suspect state, have you tried...
> > >
> > > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > > regarding this.
> > >
> > > For Active transactions on a damaged / corrupted database you can do a
> > > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > > active transaction in the database. This command will be used only when your
> > > database is damaged.
> > >
> > > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> > >
> > > So once the DB is up..
> > > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > > mention 'RECOVERY' during RESTORE
> > >
> > > Please do share once you restore the DB or let us know if you have any other
> > > queries...
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Thanks,
> > > Sree
> > > [Please specify the version of Sql Server as we can save one thread and time
> > > asking back if its 2000 or 2005]
> > >
> > >
> > >
> > > "Oskar" wrote:
> > >
> > > > Hi,
> > > >
> > > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > > database transaction log becomes unavailable and when there are active
> > > > transactions in the database?
> > > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > > scenario, I have tried that.
> > > > Are there any other options and how much of data loss to expect in each case?
> > > >
> > > > -- Many thanks, Oskar