[Latest Edit Feb 14th] So, I go to edit a few Stored Procs on my MSSQL database and… errors – table missing – what? I seems over the weekend, all the databases were reset to some time in the past – this means 30 SP’s and 20 tables on our dev environment have – poof vanished – an honest mistake due to a communication misunderstanding. Not a big deal – everything is version controlled. It was the testing data that was lost and that people are semi-using at the moment that really gets my goat and makes me want to pull my hair out. I had a set of data that was perfect for making the next module.

So I searched for backup routines and since I use c# and think the SMO addition is so cool, I found some more great links – and the code worked in < 10 minutes each.

• true backup in VB – in c# below (my hacked up code below #2 except)
• scripting the data from the tables (this is so SUPER easy) (code copied below #1 excerpt [Feb14th modified to reduce out of mem error]) **preferred for my purposes**

Next is to put this into Scheduled Task (you HAVE to read my blog to set this up quickly – or you could spend hours getting to the conclusion by the school of hard knocks.) I had to take a copy of the solution on my LOCAL drive- not a network copy or else the scheduled tasks does not work.

I have to admit, the using the SMO tool in c# or .net is SOOOO easy to use. I will admit, that I use c# to whip up a script here and there and keep fiddling with it forever. I also always keep code on a network drive AND use a repository – love gitHub.

Scripting the tables

(link for full code) … AND they included the USING parts of the code at the top. This is the important bit. How easy was that? Poof- done!

[csharp]
//Using statements in the blog down below
// Feb 14th – changed from a string builder in memory to a file writer as large tables produce an out of memory error (of course – hindsight 20/20)</pre>
//var output = new StringBuilder(); //1
if ((machineName = myargs.Item("scripttables")) != null)
{
string outputFileName = @"data\bley." + db.Name + "." + obj.Schema + "." + obj.Name.Replace("tbl", "data") + ".sql";//2
System.IO.StreamWriter file = new System.IO.StreamWriter(outputFileName);//2

var scripter = new Scripter(srv) { Options = { ScriptData = true } };
var script = scripter.EnumScript(new SqlSmoObject[] { obj });
foreach (var line in script)
file.WriteLine(line);
//output.AppendLine(line);

//File.WriteAllText(outputFileName, output.ToString()); //1
file.Close(); //
Console.WriteLine(@"Data Scripted / Backed up Successfully to data directory for " + obj.Name);

&nbsp;

}
<pre>
[/csharp]

Full Backup Script

(was translated to c# and made into a sub routine/method – originally in VB at this link)

[csharp]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using System.Configuration;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using System.IO;
using System.IO.Compression;
using System.IO.Compression;
using GetArguments;

static void BackupTables(Server srv, Database db, string dBName)
{

//Connect to the local, default instance of SQL Server.
/// Server srv = new Server();
//Reference the AdventureWorks2012 database.
// Database db = srv.Databases[dBName];
//Store the current recovery model in a variable.
// int recoverymod = db.DatabaseOptions.RecoveryModel;

//Define a Backup object variable.
Backup bk = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = "Full backup of " + dBName;
bk.BackupSetName = dBName +"_Backup";
bk.Database = dBName;

//Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
BackupDeviceItem bdi = new BackupDeviceItem("Bleys_Automated_AutoPLANTInteraction_Full_Backup", DeviceType.File);
//Add the device to the Backup object.
bk.Devices.Add(bdi);
//Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = false;
//Set the expiration date.
DateTime backupdate = new DateTime(2006, 10, 5);
bk.ExpirationDate = backupdate;
//Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate;
//’Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv);
}

[/csharp]

Zip the files just created

And my favourite part – zipping the files one they have been created

[csharp]

DateTime filenameTimeStamp = DateTime.Now;
string fileTSstring = filenameTimeStamp.ToString("yyyyMMss_HHmmsstt");
string startPath = Directory.GetCurrentDirectory()+@"\data";// @".";
string zipPath = @"tablesScripted_" + db.Name + "_" + fileTSstring + ".zip";

Console.WriteLine("Completed ONLY scripting the tables and making the archive called ‘" + zipPath + "’");
System.IO.Compression.ZipFile.CreateFromDirectory(startPath, zipPath);

[/csharp]