SCRIPT TO KILL ALL ACTIVE CONNECTIONS BEFORE A BACKUP OR RESTORE

Use Master

Go

Declare @dbname sysname

Set @dbname = 'IEMS'

Declare @spid int

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null

Begin

        Execute ('Kill ' + @spid)

        Select @spid = min(spid) from master.dbo.sysprocesses

        where dbid = db_id(@dbname) and spid > @spid

End

-- RESTORE DATABASE

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

GO

-- IF THE DATABASE IS TO BE RE-WRITTEN OR REPLACED

RESTORE DATABASE XXXX_DB FROM DISK = 'D:\Backups\XXXX_DB-20221031-190000.BAK'

WITH REPLACE

GO