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

Log shipping restore

BokaBoka Posts: 32 Silver 3
edited August 21, 2007 4:04AM in SQL Backup Previous Versions
SQL Backup Log Shipping Job will fail if exclusive access could not be obtained. Is it reasonable to expect from sql backup to kill all connections and do the job?
Any experience about using log shipping as report database would be welcome.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Boka,

    Whether or not you want to boot everyone out of the database while you restore should be up to you -- if you have a senior manager or something trying to generate a report from the standby server, I'd imagine he or she would be quite irate at being kicked out of the server.

    You would probably want to schedule your restore operations around people's usage of the server.

    But if you want to kill sessions from the database there are scripts that you can run as part of the backup job, addign a step before the actual backup to run some SQL to kill any sessions in the database being restored to.

    Here is one example of such a script:

    http://www.red-gate.com/messageboard/vi ... php?t=2952
  • Options
    BokaBoka Posts: 32 Silver 3
    Hi Brian,

    I'll keep in mind what you said but...

    Choosing between 'users kicked out of the server' and 'stale data in database' is pretty difficult. The best solution is if we have restore operation working online. Any thoughts about this?
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    You would have to get Microsoft to change the way SQL Server works.

    Basically, they supply a backup API that SQL Backup leverages. All of the rules imposed by Microsoft SQL Server backup and restore still apply. And one of those rules is that you cannot restore a database if there are users logged in to it.
Sign In or Register to comment.