Restores failing

doughydier

sql backup 10.0.10.1123
sql server 2012
I am doing a backup set restore every night to a report server. It fails about 2 times a week. The logs say the failure is because it cannot disconnect the users from the database. I run 2 restore jobs 10 minutes apart in case the first one fails. once or twice a week they both fail.

petey2

In the restore job, before the job step that runs the SQL Backup restore command, try adding a job step to forcibly kill all connections to the database in question.  You could use something like this:

USE master

DECLARE @execSql varchar(1000), @databaseName varchar(100)

-- Set the database name for which to kill the connections

SET @databaseName = '<your database name>'

SET @execSql = '' 

SELECT  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '

FROM master.dbo.sysprocesses

WHERE db_name(dbid) = @databaseName

     AND

     DBID <> 0

     AND

@spid

EXEC (@execSql)