Export MS SQL Database schema with C#

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:

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 =&gt; o.IsSystemObject == false )) {
   ...
   foreach (Table o in db.Tables.Cast().AsQueryable().Where( o =&gt; o.IsSystemObject == false )) { 
      _SAVE ( o.script() );
   }
}

Problem 2, encrypted objecs:
Again, after some google seach I found that you can actually decrypt most of the info these object hold with a Dedicated

Administrator Connection, also known as DAC.
So, for sa loggins 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();

The script that gets any decrypted object (as far as I tested), along with the entire solution is on github:https://github.com/georgekosmidis/mssql-schema-dump

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s