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 12»»

The first thing to do with a damaged off-line database Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 8:20 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:24 AM
Points: 588, Visits: 2,559
Comments posted to this topic are about the item The first thing to do with a damaged off-line database


Best wishes,

Phil Factor
Simple Talk
Post #1593893
Posted Friday, July 18, 2014 1:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 724, Visits: 948
Good Question....
Post #1593926
Posted Friday, July 18, 2014 1:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 789, Visits: 1,404
Excellent question, Thanks for sharing
Post #1593933
Posted Friday, July 18, 2014 1:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 650, Visits: 952
obviously the first thing should be to backup the log, but what is the point when the database is offline and you will get an error?

BACKUP LOG [QODTest] TO DISK = 'c:\QODTest_20140718.trn' WITH CONTINUE_AFTER_ERROR

returns
Msg 942, Level 14, State 3, Line 2
Database 'QODTest' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.

From BOL
But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE.


Best practice is to backup the log, but in this case the first thing you should do (imho) is to restore the last full backup (WITH REPLACE), otherwise you're wasting your time executing something that will fail.
Post #1593934
Posted Friday, July 18, 2014 1:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 1,332, Visits: 637
raulggonzalez (7/18/2014)
obviously the first thing should be to backup the log, but what is the point when the database is offline and you will get an error?

BACKUP LOG [QODTest] TO DISK = 'c:\QODTest_20140718.trn' WITH CONTINUE_AFTER_ERROR

returns
Msg 942, Level 14, State 3, Line 2
Database 'QODTest' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.

From BOL
But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE.


Best practice is to backup the log, but in this case the first thing you should do (imho) is to restore the last full backup (WITH REPLACE), otherwise you're wasting your time executing something that will fail.

That`s why it was mentioned "Try to take a tail-log backup", anyway you made your point clear enough
Post #1593937
Posted Friday, July 18, 2014 1:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 789, Visits: 1,404
Tail log backup WITH CONTINUE_AFTER_ERROR works only if the database goes offline in case during the start up of the database is fail to come online due to some reasons or damaged due to some reason, in case if we did purpose fully then that will not work (i,e the database didnt have any issues at all)
Post #1593939
Posted Friday, July 18, 2014 3:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 650, Visits: 952
twin.devil (7/18/2014)
Tail log backup WITH CONTINUE_AFTER_ERROR works only if the database goes offline in case during the start up of the database is fail to come online due to some reasons or damaged due to some reason, in case if we did purpose fully then that will not work (i,e the database didnt have any issues at all)


True, now I took the time to manually "corrupt" one of my test databases (not only take it offline) and I was able to do a tail-log backup WITH CONTINUE_AFTER_ERROR and also to restore the database to that transaction log backup.

Definitely I learned something today. Good question!
Post #1593955
Posted Friday, July 18, 2014 4:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 13,362, Visits: 11,142
Nice question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1593984
Posted Friday, July 18, 2014 5:38 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 4,275, Visits: 3,716
Thanks for the question.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1593994
Posted Friday, July 18, 2014 5:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 4,041, Visits: 5,354
Good question, thanks
Have had to, unfortunately, do this a few times in my tenure as a DBA


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1594000
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse