I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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:
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:
My dev/test environment is Visual Studio 2012 Professional and SQL Server 2012 Developer Edition.