Log shipping restore

Boka

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.

Brian Donahue

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

Boka

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?

Brian Donahue

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.