使 Microsoft Sql 数据库联机或脱机的 SQL 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/847076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Script to take a Microsoft Sql database online or offline?
提问by Pure.Krome
If I wish to take an MS Sql 2008 offline or online, I need to use the GUI -> DB-Tasks-Take Online or Take Offline.
如果我希望让 MS Sql 2008 离线或在线，我需要使用 GUI -> DB-Tasks-Take Online 或 Take Offline。
Can this be done with some sql script?
这可以用一些sql脚本来完成吗？
回答by Mitch Wheat
ALTER DATABASE database-name SET OFFLINE
If you run the ALTER DATABASEcommand whilst users or processes are connected, but you do not wish the command to be blocked, you can execute the statement with the NO_WAIT option. This causes the command to fail with an error.
如果您在连接用户或进程时运行ALTER DATABASE命令，但您不希望该命令被阻止，则可以使用 NO_WAIT 选项执行该语句。这会导致命令失败并出现错误。
ALTER DATABASE database-name SET OFFLINE WITH NO_WAIT
Corresponding online:
在线对应：
ALTER DATABASE database-name SET ONLINE
回答by DarkLight
-- Take all user databases offline
CREATE PROCEDURE SP_TakeOfflineAllDatabase AS
BEGIN
DECLARE @db sysname, @q varchar(max);
DECLARE cur_db CURSOR FOR
SELECT name FROM sys.databases WHERE owner_sid<>0x01;
OPEN cur_db;
WHILE 1=1
BEGIN
FETCH NEXT FROM cur_db INTO @db;
IF @@FETCH_STATUS <> 0
BREAK;
SET @q = N'ALTER DATABASE [' + @db + N'] SET OFFLINE WITH NO_WAIT';
EXEC(@q);
END;
CLOSE cur_db;
DEALLOCATE cur_db;
END;
Restart the server before run the procedure. It will close the existed connections to the databases.
在运行该过程之前重新启动服务器。它将关闭与数据库的现有连接。
回答by Jerloldrartolf
I know this is an old post but, just in case someone comes across this solution and would prefer a non cursor method which does not execute but returns the scripts. I have just taken the previous solution and converted it into a select that builds based on results.
我知道这是一篇旧帖子，但以防万一有人遇到这个解决方案并且更喜欢不执行但返回脚本的非游标方法。我刚刚采用了之前的解决方案并将其转换为基于结果构建的选择。
DECLARE @SQL VARCHAR(8000)
SELECT @SQL=COALESCE(@SQL,'')+'ALTER DATABASE '+name+ N' SET OFFLINE WITH NO_WAIT;
'
FROM sys.databases
WHERE owner_sid<>0x01
PRINT @SQL
回答by Peter
Here's a note that just might be very usefull to you : It's almost always possible to see what the GUI is doing TSQLwise behind the scenes.
这里有一条可能对您非常有用的说明：几乎总是可以看到 GUI 在幕后执行 TSQLwise 的操作。
