Managing SQL Server in Code: A Brief Walkthrough

by David Bolton Feb 5, 2018 9 min read

Microsoft's flagship relational database, SQL Server, is now 28 years old and still going strong. It comes with a decent set of management tools, ranging from SQL Management Studio to SQLCMD, a command line tool. It can also be managed completely in code using SQL Server Management Objects (SMO for short); as it's .NET, C# is the most likely language to code SMO, but any .NET language will do. Let’s walk through some actual coding. Our example program is a small command line utility that backs up a specified database then restores into a different one (i.e., it makes a copy). The code for this project is on Github. If you don’t have SQL Server, you can install the free SQL Server 2017 Express edition, which lets you utilize 10GB databases in your applications (it's licenced for use in production, as well). Or you may prefer the full-featured Developer edition, which is for development only. As you install either version, make sure to select the Client Tools SDK so the SMO assemblies are installed, as well. If you don't, you'll need to install them separately via NuGet afterwards. After you've installed SQL Server, download and install the SQL Server Management Studio (SSMS). Unless you are an absolute master at creating databases and tables in SQL, Management Studio is the way to go. You'll need it for performing backups manually. Finally, you need to create an empty C# project in Visual Studio and add the SQL Server Management Objects as a reference. If you have it installed, you'll find the references under the Reference Manager Extensions, under Assemblies. If not, use NuGet; the web page for SMO has several versions, so click on the most recent link. It will take you a page where the NuGet command can be copied and pasted into Visual Studio's Package Manager Console.

The one I used was "Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 140.17218.0," and nine seconds later, my project References had increased by about 29. SMO comes with a lot of baggage, all of it SQL Server-related. When you compile your empty project, you'll find that your project's Bin\Debug folder has added 32 additional DLLs and 24 XML config files. SMO is backwards-compatible, so even if you have an existing older SQL Server installation, it will still work.

Using SQL Server

Backup and restore are two processes that SMO makes very easy, but even so, there are a few gotchas. If you've never manually backed up and restored a SQL Server database, I suggest you give it a go, just for practice. SQL Server puts its backups in a specific folder, which you can change if you wish. To view the current location, right-click on the top line of the object Explorer in SSMS; you should see the Databases tree beneath that line. Then click Properties, then Database Settings, and at the bottom you'll see three default locations for Data, Log and Backup files. Right-click on a database in SSMS, then click Tasks, then click Backup. On the Backup Database screen, tick Copy-Only and press OK. Now view that backup folder in File Explorer, and you'll see a file with the database name and a .bak extension.

Potential Gotcha 1: Databases have an internal name, along with both a data file and log file, which you can see in the Data folder. You can rename the database files (after detaching them from SQL Server), but the internal name remains the same unless you edit it in SSMS. This name is important for restoring in code, as we’ll see later. Potential Gotcha 2: Remember that Copy-Only box I checked before performing the manual backup? With copy-only backups, it's a standalone backup, independent of normal SQL Server backup sequences. Here's more information on that. I've created an SMO class in SMO.cs. The constructor is provided the name of the SQL Server and a database on there. On my PC, SQL Server Express is PC-9\SQLEXPRESS; although I used the Airports database initially, it was so big that I later switched to a smaller database called Titchy. This is the main part of the Backup code. It exists inside a Try Catch (not shown):

Public EventHandler UpdateCallback { get; set; }
..
        public Boolean BackUpSQlDatabase(string databasename, string BackupFilename, EventHandler handler)
		{
			Db = InitServer(databasename);
			UpdateCallback = handler;
			var bk = new Backup();
			bk.PercentComplete += CompletionStatusInPercent;
			bk.Initialize = true;
			bk.CopyOnly = true;
			bk.Incremental = false;
			bk.Action = BackupActionType.Database;
			bk.BackupSetDescription = $"Full backup of {databasename}";
			bk.BackupSetName = $"Backup {DateTime.Now.ToShortDateString()}";
			bk.Database = databasename;
var bdi = new BackupDeviceItem(BackupFilename, DeviceType.File);
bk.Devices.Add(bdi);
bk.ExpirationDate = DateTime.Now.AddSeconds(15);
bk.LogTruncation = BackupTruncateLogType.Truncate;
bk.SqlBackup(server);
bk.Devices.Remove(bdi);
}

private void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)
{
var s = $"{args.Percent}%";
UpdateCallback?.Invoke(s, new EventArgs());
}

The SMO Backup class manages the process. Note that the incremental flag is set false, while initialize is true. The incremental flag controls whether a differential or full backup will be included. The Initialize flag means that the backup overwrites an existing backup; however, it can only overwrite an existing backup if it has expired, hence the short ExpirationDate. If the LogTruncation is not set to Truncate, then the transaction log is saved; when the database is restored, it will try and recover from the transaction log.

USE Airports;  
GO  
BACKUP DATABASE Airports  
TO DISK = 'E:\New folder\MSSQL14.SQLEXPRESS\MSSQL\Backup\Airports2.bak'  
   WITH FORMAT,  
      MEDIANAME = 'Full backup of Airports',  
      NAME = 'Backup 25/01/2018';  
GO

You could do the backup in this SQL above, but I prefer the control that C# code gives (such as a progress callback). I've created a method, CompletionStatusInPercent(), which gets called periodically (roughly every 10 percent). This refers to a Delegate routine as Update.

        public static void Update(object sender, EventArgs e)
        {
            WriteLine(sender as string);
        }

And a reference to Update is passed in the BackupSQlDatabase call:

        if (smo.BackUpSQlDatabase(Database, "NewAirports.bak",Update))

Restoring

Backups are OK, so long as they actually restore! The SMO.RestoreDatabase method is pretty straightforward, apart from this section of code below. Its purpose is to get the logical name from the database, then use it to rename the database and log files. In this case, it backed up a database Titchy into Titchy.bak, then restored the two database files using the new name (Titchy2) and location in the same place as the Master database files. The Master database is part of all SQL Servers; the SQL Server name and database and backup file names are defined as four constant strings in Program.cs:

		var fileList = new DataTable();
		try
		{
			fileList = rs.ReadFileList(server);
		}
		catch (Exception tex)
		{
			Error=$"{ tex.Message}";
		}
		string dataLogicalName = fileList.Rows[0][0].ToString();
		// string dataPhysicalName = fileList.Rows[0][1].ToString();
		string logLogicalName = fileList.Rows[1][0].ToString();
		// string logPhysicalName = fileList.Rows[1][1].ToString();

		var path = server.MasterDBPath; 
		var DataFile = new RelocateFile
		{
			LogicalFileName = dataLogicalName,
			PhysicalFileName = Path.Combine(path, databaseRestoreName) + ".mdf"
		};

		var LogFile = new RelocateFile()
		{
			LogicalFileName = logLogicalName,
			PhysicalFileName = Path.Combine(path, databaseRestoreName) + "_log.ldf"
		};
		
		rs.RelocateFiles.Clear();
		rs.RelocateFiles.Add(DataFile);
		rs.RelocateFiles.Add(LogFile);

Running the program produced this output, which has been snipped for brevity.

Doing Backup
10%
..
100%
Backup of Titchy to Titchy.bak succeeded
Doing Restore
10%
..
100%
Restore of Titchy2 from Titchy.bak succeeded

Conclusion

I've barely touched on what SMO can do, but Backup and Restore are two of the most popular uses. The SMO class also includes two methods, ExecSQL(string sql) and Query(string sql), which can be used to issue SQL commands without needing separate connections. Again, the code for this project is on Github.

Public EventHandler UpdateCallback { get; set; } .. public Boolean BackUpSQlDatabase(string databasename, string BackupFilename, EventHandler handler) { Db = InitServer(databasename); UpdateCallback = handler; var bk = new Backup(); bk.PercentComplete += CompletionStatusInPercent; bk.Initialize = true; bk.CopyOnly = true; bk.Incremental = false; bk.Action = BackupActionType.Database; bk.BackupSetDescription = $"Full backup of {databasename}"; bk.BackupSetName = $"Backup {DateTime.Now.ToShortDateString()}"; bk.Database = databasename; var bdi = new BackupDeviceItem(BackupFilename, DeviceType.File); bk.Devices.Add(bdi); bk.ExpirationDate = DateTime.Now.AddSeconds(15); bk.LogTruncation = BackupTruncateLogType.Truncate; bk.SqlBackup(server); bk.Devices.Remove(bdi); } private void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) { var s = $"{args.Percent}%"; UpdateCallback?.Invoke(s, new EventArgs()); }

USE Airports; GO BACKUP DATABASE Airports TO DISK = 'E:\New folder\MSSQL14.SQLEXPRESS\MSSQL\Backup\Airports2.bak' WITH FORMAT, MEDIANAME = 'Full backup of Airports', NAME = 'Backup 25/01/2018'; GO