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!

Microsoft Error Reporting crashed on Mac!

Just got this on my Mac:
Should I report the crash of the Microsoft Error Reporting to Apple??