T-Sql 中断正在连接的数据库的客户端并移除数据库

最新推荐文章于 2024-07-19 00:12:38 发布

lee576

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量165

点赞数 1

文章标签：数据库 sql sql server

本文链接：https://blog.csdn.net/lee576/article/details/139240692

版权

USE master;

-- 查找连接到数据库 'OGS_PlugPower' 的会话ID（SPID）
DECLARE @SessionID INT;

SELECT @SessionID = session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('OGS_PlugPower');

-- 使用会话ID（SPID）中断连接
IF @SessionID IS NOT NULL
BEGIN
    DECLARE @KillStatement NVARCHAR(100) = N'KILL ' + CAST(@SessionID AS NVARCHAR(10));
    EXEC sp_executesql @KillStatement;
END

DECLARE @BackupPath VARCHAR(100) = 'H:\BACKUP\';

DECLARE @CurrentYearMonth VARCHAR(6) = FORMAT(GETDATE(), 'yyyyMMdd');

DECLARE @DatabaseName1 VARCHAR(100) = 'OGS_PlugPower';
DECLARE @BackupFileName1 VARCHAR(100) = @DatabaseName1 + '_' + @CurrentYearMonth;
DECLARE @BackupStatement1 NVARCHAR(1000) = N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName1) + N' TO DISK = N''' + @BackupPath + @BackupFileName1 + N'.bak'' WITH INIT;';
EXEC sp_executesql @BackupStatement1;

-- 等待备份完成
IF DB_ID('OGS_PlugPower') IS NOT NULL
BEGIN
    ALTER DATABASE OGS_PlugPower SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OGS_PlugPower;
END;