Skip to main content

Automate SQL Dumps for SQLServer

The backup mechanism in SQL Server may be fine for many cases, but there are limitations with it (like restoring to a different server). I really like how I can just dump the database SQL with mySQL which can be easily processed with custom scripts.

I was looking around for a mysqldump equivalent for SQLServer. I know you can do this with the SQL Management Studio through GUI in 2008 version, but I need this to be automated. This lead me to discover the SQL Server Management API . With it, you can do a SQL Dump like:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;

namespace SQLGenerator
class Program
static void Main(string[] args)
//Connect to the local, default instance of SQL Server.
Server myServer = new Server(args[0]);

Database db = default(Database);
db = myServer.Databases["be"];

Scripter scrp = default(Scripter);
scrp = new Scripter(myServer);

//scrp.Options.ScriptDrops = true;
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = true;
scrp.Options.IncludeIfNotExists = true;

Urn[] smoObjects = new Urn[2];

foreach (Table tb in db.Tables) {

if (tb.IsSystemObject == false) {
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;

foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))



This will act just like mysqldump and now I can reuse my backup scripts written for mysql without much modification with SQL Server.


Ryan said…
How do you use this? Paste these into a .bat file? Is this VB code or something?

I'd love to have a way to do something like mysqldump against a MS SQL server, but I don't understand how to use the code you've posted.

samyem said…
This is C# code. You can compile it with a C# compiler and run the executable.
helvartis said…
This works great, except for constraints (indexes, foreign keys, checks, ...?). Do you know how to add these ?
helvartis said…
Never mind, I found how to include all of these: scrp.Options.DriAll = true
cowmix said…
Can this be made to run on a remote server?

What method would you use to restore the data?

Popular posts from this blog

Better Samsung DeX Experience

Samsung Dex is a pretty amazing technology that turns phone into a full fledged desktop PC. The original device costs about $150 but has some limitations such as not being able to use headphone jack and basically having the phone locked down.

A cheaper, and arguably a more functional device can be had for less than $40 with things like the Melopow Doc that leaves the device in a more usable state with full access to the devices ports at a much better price.

And while using DeX, make sure to use the fantastic Dex MaX app to add support for fullscreen to all your apps.

Happy DeXing!

Programming for Kids

I have started to see if I can get my 7 and half year old son interested in programming. This has been quite an educational experience for me. I have always had fond memories of myself self-learning GW-BASIC as a kid in the early 80s. So I was on the look-out for a modern way to teach programming for the young minds.

I started with the usual suspect of MIT's Scratch project. However for some reason, beyond structured learning through tutorials, my kids were not too attached to the setup there. I was finding that the environment was too abstract in some sense - for example it expects you to be familiar with messaging passing techniques to do any form of mildly interactive programming. Regardless, it is a good starting point.

Similar to Scratch, the GUI based programming available with Lego's Mindstorm EV3 got my attention. Unlike Scratch, this can be an expensive venture but the physicality of actually moving parts can help connect young minds with programming concepts. And it…