SQL statement for bulk Delete database

Because of the needs of the project, the database needs to be created each time the case is run. Although it is deleted every time the execution ends, there is no guarantee that the success will be removed every time (there are many reasons why I don't list them). So I wrote a script to delete the database in bulk. First of all to ensure that our database is useful and not to be deleted by mistake. Therefore, you must first back up in the delete.

To back up the database:

DECLARE @name varchar--database name  DECLARE @path VARCHAR--Path for backup files  DECLARE @fileName V Archar--filename for backup  DECLARE @fileDate VARCHAR – Used for file name SET @path = ' D:\ABC\Backup\ ' 
   select @fileDate = CONVERT (VARCHAR), GETDATE (), 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_curso R   deallocate db_cursor

To delete a database:

Method 1:

DECLARE @path varchar--Path for backup files DECLARE @fileName VARCHAR--FileName for backup DECLARE @fileD Ate VARCHAR--Used for file name SET @path = ' D:\Backup\ ' SELECT @fileDate = CONVERT (VARCHAR), GETDATE (),) DECL is db_cursor cursor for SELECT name from MASTER.dbo.sysdatabases WHERE name isn't in (' MASTER ', ' model ', ' msdb ', ' tempdb ', ' Re PortServer ' OPEN db_cursor DECLARE @databasename VARCHAR--database name DECLARE @databaseCount int =0fetch NEXT From Db_cursor to @databasename while @ @FETCH_STATUS = 0 BEGIN DECLARE @statement nvarchar () print ' Dropping databs E: ' [email protected]exec (' Use masteralter database ' [email protected]+ ' Set Single_user with rollback Immediatedrop datab ASE ' [email protected] set@[email protected]+1 FETCH NEXT from db_cursor to @databasename END print ' dropped Datab Se count: ' +convert (VARCHAR), @databaseCount) CLOSE db_cursor deallocate db_cursor

Method 2:

Declare @dbToDelete table (name varchar) DECLARE @databaseName varchar 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% ' and exists (select name from @dbToDelete) beginselect top 1 @databaseName =name from @db Todeleteif exists (SELECT * from sys.databases where name = @databaseName) BeginPrint ' dropping databse: ' [email protected]e Xec (' Use Masteralter database ' [e-mail protected]+ ' set Single_user with rollback immediatedrop database ' [email protected] ) Enddelete from @dbToDelete where name = @databaseNameend

SQL statement for bulk Delete database