批量删除数据库的SQL语句
由于项目需要,每次运行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 MASTER.dbo.sysdatabases WHERE name IN ('E2DB_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 MASTER.dbo.sysdatabases 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 sys.databases 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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。