Recovery options when the transaction log is unavailable

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, OskarIs 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
>|||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
> >|||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
> > >|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"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
> > > >