如何指定“关闭现有连接"?在 sql 脚本中 [英] How do I specify "close existing connections" in sql script

问题描述

我正在 SQL Server 2008 中对我的架构进行积极的开发，并且经常想要重新运行我的删除/创建数据库脚本.当我跑

I'm doing active development on my schema in SQL Server 2008 and frequently want to rerun my drop/create database script. When I run

USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO

我经常收到这个错误

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "MyDatabase" because it is currently in use.

如果您在对象资源管理器窗格中右键单击数据库并从上下文菜单中选择删除任务，则会出现一个关闭现有连接"复选框

If you right click on the database in the object explorer pane and select the Delete task from the context menu, there is a checkbox which to "close existing connections"

有没有办法在我的脚本中指定这个选项?

Is there a way to specify this option in my script?

推荐答案

您可以通过以下方式断开每个人的连接并回滚他们的事务:

You can disconnect everyone and roll back their transactions with:

alter database [MyDatbase] set single_user with rollback immediate

之后，您可以安全地删除数据库:)

After that, you can safely drop the database :)

这篇关于如何指定“关闭现有连接"?在 sql 脚本中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！