Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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.

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

License

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

Share

About the Author

OriginalGriff
CEO
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?
Follow on   Google+

Comments and Discussions

 
BugError [modified] Pingroupprasanth pps10-Jul-14 23:30 
GeneralRe: Error PinprotectorOriginalGriff10-Jul-14 23:41 
GeneralMy vote of 5 PinmemberVitorHugoGarcia7-Apr-13 22:34 
you rule
GeneralRe: My vote of 5 PinmvpOriginalGriff7-Apr-13 22:39 
GeneralRe: My vote of 5 PinmemberVitorHugoGarcia8-Apr-13 3:53 
QuestionI'm getting an error :( Pinmembergaga blues2-Dec-12 3:43 
AnswerRe: I'm getting an error :( PinmvpOriginalGriff2-Dec-12 3:47 
GeneralRe: I'm getting an error :( Pinmembergaga blues2-Dec-12 3:56 
GeneralRe: I'm getting an error :( PinmvpOriginalGriff2-Dec-12 4:05 
QuestionI want to relocate the files after restore Pinmemberfootballpardeep4-Oct-12 8:17 
AnswerRe: I want to relocate the files after restore PinmvpOriginalGriff4-Oct-12 22:21 
GeneralMy vote of 5 PinmemberThornik20-Sep-12 4:20 
GeneralMy vote of 5 PinmvpKanasz Robert19-Sep-12 4:46 
QuestionNice Tip. Pinmember_Amy18-Sep-12 5:42 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 18 Sep 2012
Article Copyright 2012 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid