Click here to Skip to main content
13,056,238 members (79,890 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


35 bookmarked
Posted 18 Sep 2012

Backing up an SQL Database in C#

, 18 Sep 2012
Rate this:
Please Sign up or sign in to vote.
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.


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 ...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);
/// <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.ReplaceDatabase = true;

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...


  • First version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Wales Wales
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?

You may also be interested in...

Comments and Discussions

QuestionShowing Error back Up failed for server Shiv-pc\SqlExpress Pin
Meenakshi Moolani26-May-16 23:56
memberMeenakshi Moolani26-May-16 23:56 
AnswerRe: Showing Error back Up failed for server Shiv-pc\SqlExpress Pin
OriginalGriff27-May-16 0:14
protectorOriginalGriff27-May-16 0:14 
QuestionProblem for me Pin
zhitu30-Nov-15 0:15
memberzhitu30-Nov-15 0:15 
QuestionWhat about remote server??? Pin
MrNoWords21-Apr-15 4:40
memberMrNoWords21-Apr-15 4:40 
AnswerRe: What about remote server??? Pin
OriginalGriff21-Apr-15 4:52
protectorOriginalGriff21-Apr-15 4:52 
GeneralRe: What about remote server??? Pin
MrNoWords21-Apr-15 5:25
memberMrNoWords21-Apr-15 5:25 
GeneralRe: What about remote server??? Pin
MrNoWords21-Apr-15 23:50
memberMrNoWords21-Apr-15 23:50 
BugError Pin
prasanth pps10-Jul-14 23:30
groupprasanth pps10-Jul-14 23:30 
GeneralRe: Error Pin
OriginalGriff10-Jul-14 23:41
protectorOriginalGriff10-Jul-14 23:41 
GeneralRe: Error Pin
prasanth pps9-Jun-15 0:11
groupprasanth pps9-Jun-15 0:11 
GeneralMy vote of 5 Pin
VitorHugoGarcia7-Apr-13 22:34
memberVitorHugoGarcia7-Apr-13 22:34 
GeneralRe: My vote of 5 Pin
OriginalGriff7-Apr-13 22:39
mvpOriginalGriff7-Apr-13 22:39 
GeneralRe: My vote of 5 Pin
VitorHugoGarcia8-Apr-13 3:53
memberVitorHugoGarcia8-Apr-13 3:53 
QuestionI'm getting an error :( Pin
gaga blues2-Dec-12 3:43
membergaga blues2-Dec-12 3:43 
AnswerRe: I'm getting an error :( Pin
OriginalGriff2-Dec-12 3:47
mvpOriginalGriff2-Dec-12 3:47 
GeneralRe: I'm getting an error :( Pin
gaga blues2-Dec-12 3:56
membergaga blues2-Dec-12 3:56 
GeneralRe: I'm getting an error :( Pin
OriginalGriff2-Dec-12 4:05
mvpOriginalGriff2-Dec-12 4:05 
QuestionI want to relocate the files after restore Pin
footballpardeep4-Oct-12 8:17
memberfootballpardeep4-Oct-12 8:17 
Server s = new Server(@"PARDEEP-PC\SQLEXPRESS");

                Database myDatabase = new Database(s, "MyNewDatabase");
                Restore restoreDB = new Restore();
                restoreDB.Database = myDatabase.Name + "New";
                /* Specify whether you want to restore database or files or log etc */
                restoreDB.Action = RestoreActionType.Database;
                restoreDB.Devices.AddDevice(@"D:\newDatabaseBackup.bak", DeviceType.File);
                /* You can specify ReplaceDatabase = false (default) to not create a new
                 * database, the specified database must exist on SQL Server instance.
                 * You can specify ReplaceDatabase = true to create new database
                 * regardless of the existence of specified database */
                restoreDB.ReplaceDatabase = true;
                /* If you have a differential or log restore to be followed, you would
                 * specify NoRecovery = true, this will ensure no recovery is done
                 * after the restore and subsequent restores are completed. The database
                 * would be in a recovered state. */
                restoreDB.NoRecovery = false;
                /* RelocateFiles collection allows you to specify the logical file names
                 * and physical file names (new locations) if you want to restore to a
                 * different location.*/
                restoreDB.RelocateFiles.Add(new RelocateFile("MyNewDatabase", @"E:\MyNewDatabaseNew_Data.mdf"));
                restoreDB.RelocateFiles.Add(new RelocateFile("MyNewDatabase", @"E:\MyNewDatabaseNew_Log.ldf"));
                /* Wiring up events for progress monitoring */
                //restoreDB.PercentComplete += CompletionStatusInPercent;
                //restoreDB.Complete += Restore_Completed;
                /* SqlRestore method starts to restore database
                 * You can also use SqlRestoreAsync method to perform restore
                 * operation asynchronously */

restoreDB.RelocateFiles.Add(new RelocateFile("MyNewDatabase", @"E:\MyNewDatabaseNew_Data.mdf"));
             restoreDB.RelocateFiles.Add(new RelocateFile("MyNewDatabase", @"E:\MyNewDatabaseNew_Log.ldf"));

These two lines having some problem, beacause if remove these two line , it works properly.
AnswerRe: I want to relocate the files after restore Pin
OriginalGriff4-Oct-12 22:21
mvpOriginalGriff4-Oct-12 22:21 
GeneralMy vote of 5 Pin
Thornik20-Sep-12 4:20
memberThornik20-Sep-12 4:20 
GeneralMy vote of 5 Pin
Kanasz Robert19-Sep-12 4:46
mvpKanasz Robert19-Sep-12 4:46 
QuestionNice Tip. Pin
_Amy18-Sep-12 5:42
member_Amy18-Sep-12 5:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170728.5 | Last Updated 18 Sep 2012
Article Copyright 2012 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid