Restores failing

doughydier · May 1, 2019 10:54AM

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 · May 3, 2019 2:06AM

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)

Restores failing

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk