Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup TAIL LOG when the DB is OFFLINE Expand / Collapse
Author
Message
Posted Saturday, April 3, 2010 12:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 10:53 PM
Points: 290, Visits: 714

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
Post #896146
Posted Saturday, April 3, 2010 12:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 10:53 PM
Points: 290, Visits: 714
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
Post #896154
Posted Saturday, April 3, 2010 1:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 11,192, Visits: 11,086
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896160
Posted Wednesday, March 26, 2014 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 1, Visits: 50
backup log....to disk='filename.trn'

fiel extention should be trn not bak
Post #1555027
Posted Wednesday, March 26, 2014 9:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 42,419, Visits: 35,482
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 2008, MVP
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

Post #1555035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse