Tuesday, January 5, 2010

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.

5 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

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?