I have a job that copies the production database backup to a reporting server every night, and then restores over the reporting database. Before the restore runs, there is a process to kill any connections. Rarely, but annoyingly, the restore fails because it could not get an exclusive lock on the database. I assume something is grabbing a lock right after the "kill spid" step runs and before the restore starts.
Is there a better way to make sure my restore does not fail ?
If I drop the database first, I may still have to kill connections. BOL says:
".. You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER. .."