stored procedur to kill active connections to sql server 2005 2008

USE [master]
GO
/****** Object: StoredProcedure [dbo].[procUtils_KillConnectionsToDb] Script Date: 02/24/2009 12:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[procUtils_KillConnectionsToDb]
@databaseName varchar(100)
AS
BEGIN -- proc start
SET NOCOUNT ON;

BEGIN TRY --begin try


declare @backupFileName varchar(100), @restoreDirectory varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100), @execSql nvarchar(1000)

-- Set the name of the database to restore
set @databaseName = 'BE'
-- Set the path to the directory containing the database backup
set @restoreDirectory = 'D:\Data\DB_BACKUPS' -- such as 'c:\temp\'

-- Create the backup file name based on the restore directory, the database name and today's date

--@backupFileName = @restoreDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'


set @backupFileName = 'D:\Data\DB_BACKUPS\BE.bak'
-- Get the data file and its path
select @databaseDataFile = rtrim([Name]),
@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groups
on

files.groupID = groups.groupID
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)

-- Get the log file and its path
select @databaseLogFile = rtrim([Name]),
@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
and
groupID = 0

select 'Killing active connections to the "' + @databaseName + '" database'

-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec (@execSql)

select 'Restoring "' + @databaseName + '" database from "' + @backupFileName + '" with '
select ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
select ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'
end try --eof try

begin CATCH
select 'In CATCH block.
Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
Error message: ' + ERROR_MESSAGE() + '
Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
end catch --catch

end --proc

/*



USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[procUtils_KillConnectionsToDb]
@databaseName = N'be'

SELECT 'Return Value' = @return_value

GO



*/