由于项目需要，每次运行case的时候都需要创建数据库。虽然每次执行结束都会删除，但是不保证每次都能删除成功（这里有许多原因，我就不列举了）。所以我写了个脚本去批量删除数据库。首先为确保我们的数据库是有用的，不被错删除的。所以就必须先备份在删除。
备份数据库：
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\ABC\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM
WHERE name IN ('E1DB_ABC','Scheduler')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor方法1：
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer'
OPEN db_cursor
DECLARE @databasename VARCHAR(50) -- database name
DECLARE @databaseCount int =0
FETCH NEXT FROM db_cursor INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @statement nvarchar(50)
print 'dropping databse: '+@databaseName
exec('
use master
alter database '+@databaseName+' set single_user with rollback immediate
drop database '+@databaseName)
set @databaseCount=@databaseCount+1
FETCH NEXT FROM db_cursor INTO @databasename
END
print 'dropped databse count: '+CONVERT(VARCHAR(12),@databaseCount )
CLOSE db_cursor
DEALLOCATE db_cursor方法2：
declare @dbToDelete table(name varchar(50))
declare @databaseName varchar(100)
insert into @dbToDelete values('ABC')
insert into @dbToDelete values('DEF')
insert into @dbToDelete values('GH')
insert into @dbToDelete select name from Master..SysDatabases where name like 'ABC%'
while exists(select name from @dbToDelete)
begin
select top 1 @databaseName=name from @dbToDelete
if exists(select * from where name = @databaseName)
begin
print 'dropping databse: '+@databaseName
exec('
use master
alter database '+@databaseName+' set single_user with rollback immediate
drop database '+@databaseName)
end
delete from @dbToDelete where name = @databaseName
end