USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [BackupDatabase](@FolderPath varchar(500))

as

DECLARE @FullPath varchar(1000)

set @FullPath = @FolderPath+ 'TestDB_'+convert(VARCHAR(4),year(getdate()))+right( ''+convert(VARCHAR(2),MONTH(getdate())),2)+right(''+convert(VARCHAR(2),DAY(getdate())),2)+ '.bak'

backup database [TestDB] to disk=@FullPath WITH INIT

return

USE master

DECLARE @dbname VARCHAR(20)

SET @dbname = 'Test'

DECLARE @sql NVARCHAR(500)

DECLARE @spid INT

 --SPID 值是当用户进行连接时指派给该连接的一个唯一的整数

SET @sql = 'declare getspid cursor for

select spid from sysprocesses where dbid=db_id(''' + @dbname + ''')'

EXEC ( @sql )

OPEN getspid

FETCH NEXT FROM getspid INTO @spid

WHILE @@fetch_status <> -1-- 如果 FETCH 语句没有执行失败或此行不在结果集中。

    BEGIN

        EXEC ( 'kill ' + @spid ) -- 终止正常连接

        FETCH NEXT FROM getspid INTO @spid

    END

CLOSE getspid

DEALLOCATE getspid