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!

GWT: Emulate tab on Enter

Sometimes users expect text boxes to move focus on to the next element after pressing Enter, similar to most spreadsheets. If you are using GWT and jQuery, here is a little snippet to help you along: public class AutoTabbedTextBox extends TextBox implements KeyUpHandler {     public AutoTabbedTextBox() {         super ();         addKeyUpHandler( this );     }     @Override     public void onKeyUp(KeyUpEvent event) {         // emulate tab on key         if (event.getNativeKeyCode() == KeyCodes. KEY_ENTER )             focusNext(event.getNativeEvent());     }     public native void focusNext(NativeEvent event)/*-{             var inputs = $wnd.$( ':input:visible' );             inputs.eq(inputs.index(event.target) + 1).focus();     }-* / ; }