locked
Can I take a backup of a database in standby mode? RRS feed

  • Question

  • Can I run backup on a database that is in standby mode because it is the DR box receiving log shipping?

    Does that work in SQL 2005?  How about SQL 2000?

    Thanks.

    Josh

    ps - I know I should just try it, I've already Googled it - with mixed results.
    Tuesday, June 9, 2009 9:16 PM

Answers

  • No you can't backup Standby database.


    Thanks
    Nimit
    • Marked as answer by JRStern Tuesday, June 9, 2009 10:46 PM
    Tuesday, June 9, 2009 9:51 PM

All replies

  • No you can't backup Standby database.


    Thanks
    Nimit
    • Marked as answer by JRStern Tuesday, June 9, 2009 10:46 PM
    Tuesday, June 9, 2009 9:51 PM
  • Well, that's simple enough, thanks.

    I tried (on SQL2005) and got:

    Msg 3036, Level 16, State 4, Line 1 The database "zzz_test2" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

    Pity, though - seems a common enough desire, you'd think it would be a read-only operation, and even if it wasn't, it shouldn't be rocket science to put the standby on standby long enough to do a backup then put it back on regular standby.

    And the scenario is very common - for DR planning, you ship a bunch of logs over days and weeks and months, then one day need to do some work on the DR side, you have limited bandwidth, and there's your entire database, in standby - and you can't touch it to take a quick backup without breaking the log chain?  Come on, Microsoft!

    Josh

    Tuesday, June 9, 2009 10:51 PM
  • Josh,

    You might want to search a bit at connect.micosoft.com. I'm pretty certain there's an entry for this (or something very similar) there. Make your voice heard, increses the chance it'll happen.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 10, 2009 4:58 AM
  • Well OK, I forget all about connect, I filed something on there over a year ago!

    ... and just entered this one, could not find it by searching STANDBY.

    Thanks.

    Josh
    Thursday, June 11, 2009 12:07 AM
  • Did you file a new one, Josh? If you give us the id, we can vote...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 11, 2009 6:55 AM
  • https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=466121

    Please allow backup of database in standby mode!

    This comes up as a very common requirement in HA/DR planning. Logs are shipped over a (relatively) small pipe to a DR site, for days or weeks or months. As part of a (partial) failover plan, we need a full backup of a current version of the database to restore on another server on the DR side - but we cannot take the main DR (secondary) copy out of standby without breaking the log chain. Yes, we might have to pause the application of logs - or possibly not - in order to take the backup of a large-ish database. But the alternative is shipping a large backup all the way from production over that small pipe, which can be prohibitive.

    --

    Thanks for the reminder, Tibor.

    Josh
    Thursday, June 11, 2009 9:15 PM
  • Got a response from Microsoft that ... they're working on it.

    J.
    Thursday, June 18, 2009 12:09 AM
  • Got a response from Microsoft that ... they're working on it.

    J.

    Thanks for the feedback!
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 18, 2009 6:11 AM
  • Even it is very old post but still i would llike to reply as it seems that Backup of database which is in Standby mode is not yet possible.

    I have tested this in SQL Server 2012 Enterprise Edition and got below error:

    Msg 3036, Level 16, State 4, Line 1
    The database "CIPageTest_STBY" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Please share your thoughts.

    Thanks for the feedback!

    Thursday, January 16, 2014 7:21 AM