Backing up an SQL Database in C#

• Download source - 873 B

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>
/// <param name="backUpFile">Full path to file to hold the backup</param>
public static void 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>
/// <param name="backUpFile">Full path to file to holding the backup</param>
public static void 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:

Start Button..."Administrative Tools"...Services...SQL SERVER(your instance name) 

If you look under the "Log On As" column, you will see the name.

• Open Windows Explorer, and navigate to the folder above where you want to keep backups.
• Right click the folder you want to use for backups, and select "Properties."
• In the dialog, select the "Security" Tab, and press "Edit".
• In the new dialog, look at the "Group or User Name" list
• If the name you want ("NETWORK SERVICE") is there, high light it. If it isn't, add it via the "Add" button and then make sure it is highlighted.
• Click on "Full Control" under the "Allow" column.
• Press "OK" and you are done.

Points of Interest

Sometimes, I think Microsoft makes things like this difficult just to annoy us...

History

• First version