Backing up an SQL Database in C#

One of the things that it seems far to hard to find is a reliable way of backing up a database, and restoring it again later. Particularly for development, where the SQL server instance and the database are likely to be on your development machine, and very likely to be damaged by code.

Introduction

Personally, I love backups. They save me time, lots of it. But there is always something where I go "no problem, I'll just restore from...my...backup... ...oops."

Databases are one of those things that I forget to backup. Not production ones, those are well covered, but the SQL Server instance I use for development isn't - mostly because it is on my dev machine, which has a regular hourly backup which doesn't work with databases which are in use - and they normally are.

And there are times when I want a snapshot of the DB as it is right now - either because I have a problem in it and want to test that I get rid of the problem without causing any others, or because I got rid of the damn problem and want to save a working database. So, some C# code would be useful to do it for me, when I press a button.

It's Never That Simple Though, Is It?

The code itself is pretty simple:

///<summary>/// Backup a whole database to the specified file.
///</summary>///<remarks>/// The database must not be in use when backing up
/// The folder holding the file must have appropriate permissions given
///</remarks>///<paramname="backUpFile">Full path to file to hold the backup</param>publicstaticvoid BackupDatabase(string backUpFile)
{
ServerConnection con = new ServerConnection(@"xxxxx\SQLEXPRESS");
Server server = new Server(con);
Backup source = new Backup();
source.Action = BackupActionType.Database;
source.Database = "MyDataBaseName";
BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
source.Devices.Add(destination);
source.SqlBackup(server);
con.Disconnect();
}
///<summary>/// Restore a whole database from a backup file.
///</summary>///<remarks>/// The database must not be in use when backing up
/// The folder holding the file must have appropriate permissions given
///</remarks>///<paramname="backUpFile">Full path to file to holding the backup</param>publicstaticvoid RestoreDatabase(string backUpFile)
{
ServerConnection con = new ServerConnection(@"xxxxx\SQLEXPRESS");
Server server = new Server(con);
Restore destination = new Restore();
destination.Action = RestoreActionType.Database;
destination.Database = "MyDataBaseName";
BackupDeviceItem source = new BackupDeviceItem(backUpFile, DeviceType.File);
destination.Devices.Add(source);
destination.ReplaceDatabase = true;
destination.SqlRestore(server);
}

And should be pretty self explanatory. You can do this by issuing SQL Commands instead of using ServerConnections, but that requires that you connect to the Master database instead of the "real" database, which will probably require heavy duty user credentials. If you connect to the database you are going to backup or restore, it is too easy to have the operation fail, because the database is in use - by you to issue the commands to do the operation in the first place...

But this requires a reference to the appropriate SQL assemblies:

Microsoft.SqlServer.ConnectionInfo in Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc in Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo in Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended in Microsoft.SqlServer.SmoExtended.dll

These files are installed with SQL Server, normally in the folder:

C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies

To add them,

Open your project in Visual Studio

Right click "References" in the Solution Explorer

Select "Add Reference..."

In the dialog box that comes up:

Press the "Browse" button, and navigate to the appropriate folder.

Select all four of the DLL files listed above (using CTRL+Click to add them rather than replace)

Press "Open"

Press "Add", then "Close"

The Next Complication!

Databases can only be restored if they are not in use (and it is better to back them up when they are not in use as well) - so the first thing to do is to make sure that you don't have any open connections in your software. If you do, you will get exceptions.

Then, check you don't have SSMS open - that can be an active connection.

Then, check that you don't have the database open in Visual Studio via the Server Explorer pane.

So, It'll Work Now?

No, probably not.

SQL server does not run under your user ID, so the chances are that you will get an error when you try to write the backup file, which will probably be "operating System error 5" - a useless message that covers a huge range of problems. The most likely one here is that the account SQL server is running under does not have permission to write files in your chosen backup directory (it never does for mine, so why should you be any different?)

SQL server normally runs under "Network Service" unless you changed it when you installed SQL - you can check with:

Share

About the Author

Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?