November 28, 2010 at 2:07 am
Hi Experts,
I am trying to restore a database which is in suspect mode .On daily basis overnight full backup and every hour transaction log backup is scheduled for the db but when I tried taking tail log backup putting the db in emergency/offline mode it's failing with following error.
Msg 942, Level 14, State 3, Line 2
Database 'Testdb' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
BACKUP LOG Testdb
TO DISK = 'X:\Backup\Transaction_Log_backup\Testdb\TestdbTailLog.trn'
WITH NO_TRUNCATE
Any suggestions?
Thanks
November 28, 2010 at 2:54 am
make sure you know why the database is suspect first by checking the errorlog.
USe with norecovery NOT no_truncate to do a tail log backup. norecovery only works if the log file is available.
This command does not put the database in emergency mode, I think you are on the wrong track there.
---------------------------------------------------------------------
November 28, 2010 at 6:04 am
george sibbald (11/28/2010)
USe with norecovery NOT no_truncate to do a tail log backup.
Err....
WITH NORECOVERY is for when the database is online and available, it's for taking that last log backup before starting a restore.
WITH NO_TRUNCATE is for when the database is damaged and a normal log backup may fail. It's for trying to get that last set of transactions out of a damaged database before dropping or restoring.
Both are referred to as 'tail-log backups', but they're used for different purposes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2010 at 6:06 am
Sqlsavy (11/28/2010)
On daily basis overnight full backup and every hour transaction log backup is scheduled for the db but when I tried taking tail log backup putting the db in emergency/offline mode it's failing with following error.
Offline or emergency? There's a very big difference between the two states.
What have you done up until this point?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2010 at 7:18 am
sorry, stupid, stupid post. :blush:
---------------------------------------------------------------------
November 28, 2010 at 8:15 am
Actually on UAT server one db was offline and other was suspect while posting I didn't mention about two db's just mentioned about the status sorry about that .
Thanks for the advice Gail!
November 29, 2010 at 7:56 am
INVOLUNTARY DBA ... i guess..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply