Drop all databases SQL Server

Rédigé par M Sozezzo - - Aucun commentaire

We have many ways to drop a database, but if you want to drop many databases.

You can have some problems with opened connections, but this script kill all connection and drop the database.


-- ***************************************** --
--       SQL Script to drop databases
-- ***************************************** --
--
PRINT @@servername;
PRINT Getdate();
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;

--#region Select databases to DROP

SELECT [name] INTO #dbs
FROM sys.databases
WHERE
    database_id > 4
    AND name NOT IN ('ReportServer','ReportServerTempDB')

--#endregion Select databases to DROP

--#region Drop databases

USE [MASTER];
SET NOCOUNT ON;
DECLARE @name nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

WHILE (EXISTS(SELECT *
    FROM #dbs))
BEGIN

    SELECT TOP 1 @name = name
    FROM #dbs;
    DELETE FROM #dbs
    WHERE
        @name = name;

    DECLARE @msg nvarchar(200) = '-- DROP Database : ' + @name;
    RAISERROR (@msg, 10, 1) WITH NOWAIT;

    SET @ToKill = '';
    SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
    FROM master..sysprocesses
    WHERE dbid = DB_ID(@name);

    RAISERROR (@tokill, 10, 1) WITH NOWAIT;
    EXEC (@tokill);

    SET @sql = 'ALTER DATABASE [' + @name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;'
    RAISERROR (@sql, 10, 1) WITH NOWAIT;
    EXEC (@sql);

    SET @sql = 'DROP DATABASE [' + @name + ']';
    RAISERROR (@sql, 10, 1) WITH NOWAIT;
    EXEC (@sql);

END

--#endregion Drop databases

 

sources:

http://stackoverflow.com/questions/349358/sql-server-print-output-doesnt-appear-immediately

http://stackoverflow.com/questions/5777483/drop-all-databases-from-server

 

Fil RSS des articles de ce mot clé