大量刪除資料庫的SQL語句,大量刪除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 ('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 MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer' OPEN db_cursor DECLARE @databasename VARCHAR(50) -- database name DECLARE @databaseCount int =0FETCH NEXT FROM db_cursor INTO @databasename WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @statement nvarchar(50)print 'dropping databse: '+@databaseNameexec('use masteralter database '+@databaseName+' set single_user with rollback immediatedrop 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)beginselect top 1 @databaseName=name from @dbToDeleteif exists(select * from sys.databases where name = @databaseName)beginprint 'dropping databse: '+@databaseNameexec('use masteralter database '+@databaseName+' set single_user with rollback immediatedrop database '+@databaseName)enddelete from @dbToDelete where name = @databaseNameend