What are the challenges you face when working across database platforms? Take the survey

use log shipping to populate a 'data mart'?

randyvrandyv Posts: 166
edited October 15, 2009 2:05PM in SQL Backup Previous Versions
Hello -

I'm wondering about whether anyone uses SQL Backup Log Shipping to populate a 'data mart' and whether you find this advisable.

In reading about log shipping and putting the 'standby database' into read only mode, it occured to me that this would be an ideal way to have a 'data mart' copy of my production databases offloaded to another server for reporting purposes; as well as ensuring when we purge a year off the production system we have a backup of it.

Have you seen this done elsewhere, if not, what do you think of the idea?
What we do in life echoes in eternity <><
Randy Volters


  • Options
    Eddie DEddie D Posts: 1,793 Rose Gold 5
    Thank you for your post into the forum.

    One possible use of the standby or destination database in a Log Shipping setup, is to have the database used for reporting purposes configured using the WITH STANDBY recovery model.

    You will need to be aware, depending on how frequent you wish to restore additional log files to the destination database. If you have a user connected to the database, the restore process will fail due to the well known Exclusive Access could not be obtained, SQL Error 3101.

    The restore process requires exclusive access to the database, so to restore additional files to the database you must wait for all users connected have finished using the database.

    Alternatively you can use the 'Kill any existing connections to the database' option when performing a restore. However, you may annoy connected users who are running long queries to obtain the data they are seeking for their report.

    Are there any Red Gate SQL Backup users that have 'real world' examples or experiences to share using a Log Shipped Read-Only database?

    Many Thanks
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    What we do in life echoes in eternity <><
    Randy Volters
Sign In or Register to comment.