How can we help you today? How can we help you today?

Restores failing

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.
Doug Hydier
0

Comments

1 comment

Sort by
  • 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)

    petey2
    0

Add comment

Please sign in to leave a comment.