Export MS SQL Database schema with C#

0 0 votes
Article Rating

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:

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!

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

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments