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 }))
{
Console.WriteLine(s);
}

}
}
}

}
}
}



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

Comments

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.

-thanks
Unknown 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!

System Overload!