在持续集成下删除和创建数据库的防弹方式 [英] Bulletproof way to DROP and CREATE a database under Continuous Integration

问题描述

我正在尝试从CI设置中删除并重新创建数据库。但是我发现很难自动化删除和创建数据库，鉴于使用中的数据库的复杂性，这是可以预料的。有时，该进程挂起，当前正在使用db的错误或花费的时间太长。我不在乎数据库是否正在使用，我想杀死它并重新创建它。有人有直接方法可以做到这一点吗？或者有人有经验删除数据库中的所有对象而不是删除数据库本身吗？

I am attempting to drop and recreate a database from my CI setup. But I'm finding it difficult to automate the dropping and creation of the database, which is to be expected given the complexities of the db being in use. Sometimes the process hangs, errors out with "db is currently in use" or just takes too long. I don't care if the db is in use, I want to kill it and create it again. Does some one have a straight shot method to do this? alternatively does anyone have experience dropping all objects in the db instead of dropping the db itself?

USE master

--Create a database
IF EXISTS(SELECT name FROM sys.databases
    WHERE name = 'mydb')
BEGIN
 ALTER DATABASE mydb
 SET SINGLE_USER --or RESTRICTED_USER
 --WITH ROLLBACK IMMEDIATE
    DROP DATABASE uAbraham_MapSifterAuthority
END

CREATE DATABASE mydb;

推荐答案

我们使用哈德森公司为质量检查小组重建临时站点每时每刻。我们会终止连接，删除数据库，然后还原/重建/迁移数据库。

We use Hudson to rebuild staging sites for our QA team all the time. We kill connections, drop the database, then restore/rebuild/remigrate a DB.

这是我用来终止连接以便删除数据库的方法。

This is what I use to kill connections so I can drop a DB.

USE MASTER
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_KillDatabaseProcesses]') AND type in (N'P', N'PC'))
   DROP PROCEDURE [dbo].[sp_KillDatabaseProcesses]
GO
CREATE PROCEDURE dbo.sp_KillDatabaseProcesses(@databaseName varchar(100))     
   AS
   DECLARE @databaseId int,
           @sysProcessId int,
           @cmd varchar(1000)
   EXEC ('USE MASTER')
   SELECT @databaseId = dbid FROM master..sysdatabases
      WHERE [name] = @databaseName
   DECLARE sysProcessIdCursor CURSOR FOR
      SELECT spid FROM [master]..[sysprocesses] WHERE [dbid] = @databaseId

   OPEN sysProcessIdCursor
   FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId WHILE @@fetch_status = 0
   BEGIN
      SET @cmd = 'KILL '+ convert(nvarchar(30),@sysProcessId)
      PRINT @cmd
      EXEC(@cmd)
      FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId
   END
   DEALLOCATE sysProcessIdCursor
GO

这篇关于在持续集成下删除和创建数据库的防弹方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！