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
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
>
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
Friday, March 30, 2012
Recovery options when the transaction log is unavailable
Labels:
becomes,
database,
log,
microsoft,
mysql,
oracle,
recovery,
server,
sp3,
sql,
thedatabase,
transaction,
unavailable
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment