Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reliably Drop A Database With CLR Stored Procedure

 

After I read Greg Low’s post and my initial try, I kept on thinking that I need to come up with a solution that in theory is robust. The only way I can think of is to use a CLR stored procedure which will do the following three steps:

1. alter database <blah> set offline with rollback immediate;

2. find all the files that compose the <blah> database, using

select physical_name from master.sys.master_files where database_id = db_id(‘blah’)

3. delete each file found in step 2.

So here is C# code to create the CLR stored procedure:

   1: using System;

   2: using System.Collections;

   3: using System.Data;

   4: using System.Data.SqlClient;

   5: using System.Data.SqlTypes;

   6: using Microsoft.SqlServer.Server;

   7: using System.IO;

   8:  

   9: public partial class StoredProcedures

  10: {

  11:     [Microsoft.SqlServer.Server.SqlProcedure]

  12:     public static int usp_DropDB (SqlString db_name)

  13:     {

  14:  

  15:         try

  16:         {

  17:             using (SqlConnection conn = new SqlConnection("context connection=true"))

  18:             {

  19:                 string sql = "select cnt=count(*) from master.sys.databases where name=@dbname";

  20:                 SqlCommand scmd = new SqlCommand();

  21:                 SqlParameter parmDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);

  22:                 parmDBName.Value = db_name.Value;

  23:                 scmd.Parameters.Add(parmDBName);

  24:                 scmd.CommandText = sql;

  25:                 scmd.Connection = conn;

  26:                 conn.Open();

  27:                 Int32 i = (Int32)scmd.ExecuteScalar();

  28:                 conn.Close();

  29:                 scmd.Dispose();

  30:  

  31:                 if (i == 1)

  32:                 {

  33:                     

  34:                     sql = "if exists (select * from sys.databases where name=@dbname)";

  35:                     sql = sql + "\r\n alter database [" + db_name.Value + "] set offline with rollback immediate;";

  36:  

  37:                     SqlCommand cmd = new SqlCommand();

  38:                     SqlParameter pDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);

  39:                     pDBName.Value = db_name.Value;

  40:  

  41:  

  42:                     cmd.Parameters.Add(pDBName);

  43:  

  44:                     cmd.CommandText = sql;

  45:                     cmd.Connection = conn;

  46:  

  47:                     conn.Open();

  48:                     cmd.ExecuteNonQuery();

  49:                     cmd.CommandText = "select physical_name from master.sys.master_files where database_id=db_id(@dbname)";

  50:                     SqlDataReader rd = cmd.ExecuteReader();

  51:                     ArrayList alFilePath = new ArrayList();

  52:                     while (rd.Read())

  53:                     {

  54:                         alFilePath.Add((string)rd.GetString(0));

  55:                     }

  56:                     conn.Close();

  57:  

  58:                     cmd.CommandText = "drop database [" + db_name.Value + "];";

  59:  

  60:                     conn.Open();

  61:                     cmd.ExecuteNonQuery();

  62:                     conn.Close();

  63:  

  64:                     for (i = 0; i <= alFilePath.Count - 1; i++)

  65:                     {

  66:                         File.Delete((string)alFilePath[i]);

  67:                     }

  68:                 }

  69:                 else

  70:                 {

  71:                     return 1; // no db found

  72:                 }

  73:             }

  74:             return 0; // succeed

  75:         }

  76:         catch

  77:         {

  78:             return (-1); // -1 meaning error occurred

  79:         }

  80:     }

  81: }

 

Using Visual Studio to compile this to DLL file, let’s assume the file is called/located at c:\temp\CLR_SP.dll, and then using the following sql statement to create the CLR procedure:

   1: use master

   2: create assembly clr_dropDB from 'C:\temp\CLR_SP.dll' 

   3: with permission_set = External_ACCESS;

   4: go

   5:  

   6: create proc dbo.usp_DropDB @db_name nvarchar(128)

   7: as external name clr_dropDB.StoredProcedures.usp_DropDB;

   8: go

   9:  

  10: -- example 

  11: use master;

  12: declare @id int;

  13: exec dbo.usp_dropDB @db_name=N'testdb';

  14: print @id;

 

My dev/test environment is Visual Studio 2012 Professional and SQL Server 2012 Developer Edition.


Comments

Leave a comment on the original post [dbaphilosophy.wordpress.com, opens in a new window]

Loading comments...