0
  1. I have a "log shipping" from server A(Database Name = main) to server B(Database Name = LS_main) .
  2. I want to take a Snapshot Database From the secondary Database (Database Name = LS_main), but he id in STANDBY mode (Read_only) and I can't do it.

How can I do it ?

(I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?)

2
  • You want to take copy of secondary database ?
    – Im88
    Mar 3, 2015 at 17:52
  • Yes.... But I know that I can't do it in the standby mode, as a secondary database.
    – itzik Paz
    Mar 4, 2015 at 18:35

1 Answer 1

1

I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?

What you are telling is not possible using logshipping.

There are limitations of database snapshots:

In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.

There are some options:

  • If your database is not that big, you can do a snapshot replication, if you really want a snapshot of your database as opposed to doing a backup/restore.
  • If you want a near-to-realtime data on some secondary server, then transactional replication would be a better option.
  • Alternatively, you can use database mirroring. It allows database snapshot on the mirrored server but the database must be in the synchronized mirroring state.
  • Now that SQL Server 2012 and 2014 are out with AlwaysON, you can consider that as well if you want a read-only copy of your database.

Note that database snapshots as well as AlwaysON are Enterprise features :-)

2
  • How Can I Disconnect the Secondery Database from logshipping(then I can take a snapshot of him) and then Reconnect him to the Logshipping ?
    – itzik Paz
    Mar 4, 2015 at 18:32
  • 1
    Once you disconnect - meaning break logshipping, bring the secondary ONLINE and take a snapshot, you have to reestablish your logshipping again.
    – Kin Shah
    Mar 4, 2015 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.