Backup TAIL LOG when the DB is OFFLINE

Question

Post reply

Backup TAIL LOG when the DB is OFFLINE

m--S3qU3L

Hall of Fame

Points: 3706
More actions
April 3, 2010 at 12:21 am

#138222

Hi all,
The MS article http://msdn.microsoft.com/en-us/library/ms179314.aspx states that it is possible to take the TAIL LOG backup when the database is OFFLINE and does not start.
I deliberately put a DB offline, but I cant take the LOG BACKUP.
Any thoughts?
Thank
big[M]
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 1

The NO_TRUNCATE option is also giving an error.

Example:

BACKUP LOG AdventureWorks

TO DISK ='C:\Program Files\Microsoft SQL Server\SQLWorks\Backup\adv3.bak'

WITH NO_TRUNCATE

RESULT:

Msg 942, Level 14, State 3, Line 1

Database 'AdventureWorks' cannot be opened because it is offline.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

John

Paul White SSC Guru Points: 150467 More actions · Answer 2

This only works if the database is offline because it failed to start, or is damaged in some way.

Setting an otherwise good database to OFFLINE explicitly does not work.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nills009 Grasshopper Points: 15 More actions · Answer 3

backup log....to disk='filename.trn'

fiel extention should be trn not bak

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

File extension can be .isanoddextension for all SQL cares. Extensions have no effect on behaviour

Please note: 4 year old thread

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass