Home Dashboard Directory Help
Search

Allow backup of database in standby mode by JRStern


Status: 

Closed
 as Won't Fix Help for as Won't Fix


22
0
Sign in
to vote
Type: Suggestion
ID: 466121
Opened: 6/10/2009 4:56:09 PM
Access Restriction: Public
2
Workaround(s)
view

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 10/23/2012 at 9:45 AM
Thank you for your suggestion, however this is not on our roadmap for any near-term releases.
I am accordingly closing this DCR.
Posted by Isaac McN on 1/18/2011 at 7:07 PM
Great workaround Saggi - thanks!
Posted by Saggi Neumann on 6/30/2010 at 1:07 AM
I think that the same feature should also apply to databases in norecovery mode. Thanks!

S. Neumann
Posted by Microsoft on 6/15/2009 at 10:19 AM
Thank you for your suggestion.
Indeed, this request has been discussed many times, both in the form of backing up a Standby database (in the case of Log Shipping) or of backing up a mirror database in the case of Database Mirroring.

We are working on a feature for a future version of SQL Server which will allow you to accomplish this.
Sign in to post a workaround.
Posted by Kevin Chant on 6/14/2011 at 6:00 AM
If you don't want to take your log shipped secondary offline another option is to backup the secondary database files using a third party backup application using their Open File agent and restore them over the top of the existing files in the new DB, however I would recommend doing the backup whilst no restores are been done.

It's worth noting that in SQL 2000 you can restore the files elsewhere and simply attach the files as a new database to a SQL 2000 instance without having to create a new DB...
Posted by Saggi Neumann on 6/30/2010 at 1:05 AM
Until the feature is added, you can use the following method to backup a log shipped secondary:
1. Create a new DB.
2. Take the new DB and the log shipped secondary offline.
3. Copy log shipped secondary's files over the new DB's files.
4. Bring both online.
5. The new DB is consistent and operational and can be backed up, etc.

The same method is used to attach a damaged database (http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx)

Cheers,
S. Neumann