A few days ago I was searching for a way to export MS SQL schema of all databases of a server for backup and migration purposes. Although I found some tools, what I needed was a way to get stored procedures, functions, views, triggers and everything even if it was encrypted.

Since I couldn’t find a way to automate this (e.g. run it every night to backup schema of my server), I thought it would be a nice weekend project.

You can read more about it, or download project from https://github.com/georgekosmidis/mssql-schema-dump!

Problem 1, automate script export:
After some google search, I found out that 4 Microsoft SqlServer DLLs expose collections of all available objects in db: Microsoft.SqlServer.ConnectionInfo.dll, Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.SqlEnum.dll.
Now it was easy to get a server object and iterate every collection:

var cn = new SqlConnection( "...." );
var sc = new ServerConnection( cn );
Server server = new Server( sc );
foreach (var db in server.Databases.Cast<database>().AsQueryable().Where( o => o.IsSystemObject == false )) {
   ...
   foreach (Table o in db.Tables.Cast().AsQueryable().Where( o => o.IsSystemObject == false )) { 
      _SAVE ( o.script() );
   }
}

Problem 2, encrypted objects:
Again, after some google search I found that you can actually decrypt most of the info these object hold with a Dedicated Administrator Connection, also known as DAC. That means, that for sa logins only, you could enable DAC on a server, reconnect, get your encrypted objects and disable it again!

//enable it
cmd.CommandText = "exec sp_configure 'show advanced options', 1" + Environment.NewLine;
cmd.CommandText += "RECONFIGURE WITH OVERRIDE" + Environment.NewLine;
cmd.CommandText += "exec sp_configure 'remote admin connections', 1" + Environment.NewLine;
cmd.CommandText += "RECONFIGURE WITH OVERRIDE" + Environment.NewLine;
cmd.ExecuteNonQuery();
//disable it
cmd.CommandText = "exec sp_configure 'show advanced options', 0" + Environment.NewLine;
cmd.CommandText += "RECONFIGURE WITH OVERRIDE" + Environment.NewLine;
cmd.CommandText += "exec sp_configure 'remote admin connections', 0" + Environment.NewLine;
cmd.CommandText += "RECONFIGURE WITH OVERRIDE" + Environment.NewLine;
cmd.ExecuteNonQuery();

That was it! The full working code that retrieves any encrypted object (as far as I know), along with the entire solution is on github: https://github.com/georgekosmidis/mssql-schema-dump