Thanks, just in case somebody needs to do the same...this is the method that I wrote...
private static void BackupDatabase()
{
//Create the output backup file
String todayDate = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
String backupFileName = backupDirectory + todayDate + ".sql";
if (File.Exists(backupFileName))
{
File.Delete(backupFileName);
}
StreamWriter sw = File.CreateText(backupFileName);
//Connect to the local, default instance of SQL Server.
Server srv = new Server(sqlInstanceName);
Database db = srv.Databases[localDatabaseName];
//Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptData = true;
scrp.Options.ScriptSchema = false;
scrp.Options.ScriptDataCompression = false;
scrp.Options.Statistics = false;
sw.WriteLine("USE [" + localDatabaseName + "]");
//Disable Constraints
sw.WriteLine("EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"");
//Iterate through the tables in database and script each one.
foreach (Table tb in db.Tables)
{
if (!tb.IsSystemObject)
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
sw.WriteLine(s);
sw.Flush();
}
}
}
//Enable Constraints
sw.WriteLine("EXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"");
sw.Flush();
}
Regards,
Paola
