Reliably Drop A Database With CLR Stored Procedure

Jeffrey Yao, 2014-01-20

 

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);

  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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads