Click here to Skip to main content
11,920,772 members (57,151 online)
Click here to Skip to main content
Add your own
alternative version


87 bookmarked

SQL Server 2005 Database Backup and Restore using C# and .NET 2.0

, 7 Jun 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Server 2005 database backup and restore using C#, .NET 2.0, and SMO.


The following article describes accessing a SQL Server 2005 database backup and restoring it programmatically using C#.NET 2.0 and SMO. This article provides coding samples to perform the task.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following namespaces can be used to access SQL Server 2005 programmatically:

  • Microsoft.SqlServer.Management.NotificationServices
  • Microsoft.SqlServer.Management.Smo
  • Microsoft.SqlServer.Management.Smo.Agent
  • Microsoft.SqlServer.Management.Smo.Broker
  • Microsoft.SqlServer.Management.Smo.Mail
  • Microsoft.SqlServer.Management.Smo.RegisteredServers
  • Microsoft.SqlServer.Management.Smo.Wmi
  • Microsoft.SqlServer.Management.Trace


You need to reference the following namespaces before using this code:

  • Microsoft.SqlServer.Management.Smo;
  • Microsoft.SqlServer.Management.Common;

I used these two class to perform the backup and restore operations:

  • Microsoft.SqlServer.Management.Smo.Backup
  • Microsoft.SqlServer.Management.Smo.Restore

For more information, regarding these two class, check MSDN:

Backup database

public void BackupDatabase(String databaseName, String userName, 
            String password, String serverName, String destinationPath)
    Backup sqlBackup = new Backup();
    sqlBackup.Action = BackupActionType.Database;
    sqlBackup.BackupSetDescription = "ArchiveDataBase:" + 
    sqlBackup.BackupSetName = "Archive";

    sqlBackup.Database = databaseName;

    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    Database db = sqlServer.Databases[databaseName];
    sqlBackup.Initialize = true;
    sqlBackup.Checksum = true;
    sqlBackup.ContinueAfterError = true;
    sqlBackup.Incremental = false;

    sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
    sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

    sqlBackup.FormatMedia = false;


Restore Database

public void RestoreDatabase(String databaseName, String filePath, 
       String serverName, String userName, String password, 
       String dataFilePath, String logFilePath)
    Restore sqlRestore = new Restore();
    BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
    sqlRestore.Database = databaseName;

    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);

    Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = RestoreActionType.Database;
    String dataFileLocation = dataFilePath + databaseName + ".mdf";
    String logFileLocation = logFilePath + databaseName + "_Log.ldf";
    db = sqlServer.Databases[databaseName];
    RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.PercentCompleteNotification = 10;
    sqlRestore.PercentComplete += 
       new PercentCompleteEventHandler(sqlRestore_PercentComplete);
    db = sqlServer.Databases[databaseName];

The portion of code uses full backup features. If you want, you can perform incremental and differential backup as well.

Updates: June 8, 2008

In order to use this code, your SQL Server authentication mode needs to be configured as Mixed Mode authentication. If you use Windows Authentication, then you need to modify the ServerConnection:

SqlConnection sqlCon = new SqlConnection ("Data Source=Bappi; Integrated Security=True;");
ServerConnection connection = new ServerConnection(sqlCon);

Modify the ServerConnection portion of both code samples using this code in order to use Windows Security.


As this code uses the SQL Server 2005 backup/restore facility, the code follows the rules of SQL Server 2005 while backing up or restoring databases.


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


About the Author

Bappi M Ahmed
Software Developer SDL Tridion
Netherlands Netherlands
Software Developer with extensive experience in Software Development. Six years of Job experience in different positions. Eight years of practical experience in .net development. Experience in Distributed Application Development, Service Oriented Application Development and Smart Client Application development using .net framework.

More About Me:

You may also be interested in...

Comments and Discussions

QuestionRestore failed for Server '.\sql2008' Pin
amir farashah24-Jan-15 8:49
memberamir farashah24-Jan-15 8:49 
Questionlink download die Pin
Member 245846726-Jun-14 0:06
memberMember 245846726-Jun-14 0:06 
QuestionMy vote of 5 Pin
abbaspirmoradi8-Sep-13 8:22
professionalabbaspirmoradi8-Sep-13 8:22 
GeneralMy vote of 3 Pin
bunzitop26-Mar-13 4:41
memberbunzitop26-Mar-13 4:41 
GeneralMy vote of 5 Pin
Member 966765523-Jan-13 19:39
memberMember 966765523-Jan-13 19:39 
AnswerAbout restore and backup Pin
arunarw6-Aug-12 6:26
memberarunarw6-Aug-12 6:26 
Try this.This must be help you.Comment me if you have doubts.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo; //Project --> Add References --> Browse --> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\"Microsoft.SqlServer.Smo.dll"
using Microsoft.SqlServer.Management.Common; //Project --> Add References --> Browse --> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\"Microsoft.SqlServer.ConnectionInfo.dll"

namespace BackupRestore
public partial class DBBackup : Form
string path = Application.StartupPath; //take current running path
DataTable servers = SmoApplication.EnumAvailableSqlServers(true); //take all servers to 'servers'
private static Server csvr;

public DBBackup()

private void DBBackup_Load(object sender, EventArgs e)
if (servers.Rows.Count > 0)
foreach (DataRow srvrobj in servers.Rows)
cmbSN.Items.Add(srvrobj["Name"]);//add all servers to combobox named cmbSN.'Server Name'
cmbSN.Text = Convert.ToString(srvrobj["Name"]);//sets default name to cmbSN
ServerConnection srvConn = new ServerConnection(cmbSN.SelectedItem.ToString()); //take servername
srvConn.LoginSecure = true;
csvr = new Server(srvConn);
string npath = System.IO.Path.Combine(path, "backup"); // create a new path
System.IO.Directory.CreateDirectory(npath); // if not a backup folder create a folder named backup
path = Application.StartupPath+"\\backup";

foreach (Database dbServer in csvr.Databases)
dbs.Items.Add(dbServer.Name); //add database names to combobox named dbs 'Databses'
dbs.SelectedIndex = 0;

private void btnBackup_Click(object sender, EventArgs e) // backup
DateTime cdt = DateTime.Now; //take Today

string dt = '_'+cdt.Year.ToString() + '_' + cdt.Month.ToString() + '_' + cdt.Day.ToString(); //customize today

Backup bkpDatabase = new Backup(); // create backup object
bkpDatabase.Action = BackupActionType.Database; //action
bkpDatabase.Database = dbs.SelectedItem.ToString(); // take name that need backup
BackupDeviceItem bkpDevice = new BackupDeviceItem(path + "\\" + dbs.SelectedItem.ToString() + dt + ".bak", DeviceType.File); // creates a backup device
bkpDatabase.Devices.Add(bkpDevice); //add device
bkpDatabase.SqlBackup(csvr); //get backup to device
MessageBox.Show(dbs.SelectedItem.ToString() + " Database backup successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
catch (Exception x)
MessageBox.Show("Error " + x, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

private void btnRestore_Click(object sender, EventArgs e) //restore
browser.InitialDirectory = path; //sets current backup folder to browse path
browser.ShowDialog(); //opens browse window
if (browser.FileName != "*.bak") //checks if it has selected file
if (MessageBox.Show("Are you sure want to restore " + dbs.SelectedItem.ToString() + " database...?", "Are You Sure", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation).ToString() == "Yes")
Restore rstDatabase = new Restore(); //restore object
rstDatabase.Action = RestoreActionType.Database; //action
rstDatabase.Database = dbs.SelectedItem.ToString(); //take dbname
BackupDeviceItem bkpDevice = new BackupDeviceItem(browser.FileName.ToString(), DeviceType.File); //ready device
rstDatabase.Devices.Add(bkpDevice); //add device
rstDatabase.ReplaceDatabase = true; //sets properties
rstDatabase.SqlRestore(csvr); //restore db
MessageBox.Show("Database " + dbs.SelectedItem.ToString() + " restored succefully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
catch (Exception x)
MessageBox.Show("Error " + x, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
browser.FileName = "*.bak"; //again sets to show all backup files that have .bak extention

private void btnExit_Click(object sender, EventArgs e)
<pre lang="c#">

GeneralRe: About restore and backup Pin
sk. safaruddin17-Aug-12 22:27
membersk. safaruddin17-Aug-12 22:27 
QuestionSQL Server 2005 Database Backup and Restore using C# and .NET 2.0 (View) Author Bappi M Ahmed Pin
vinhhc17-Jul-12 16:33
membervinhhc17-Jul-12 16:33 
AnswerSQL Server 2005 Database Backup and Restore using C# and .NET 2.0 (View) Author Bappi M Ahmed Pin
vinhhc17-Jul-12 16:18
membervinhhc17-Jul-12 16:18 
QuestionTesting Pin
blochjaved12322-May-12 4:14
memberblochjaved12322-May-12 4:14 
Questioni am using ms sql 2008 R2 version Pin
rajesh@198911-Apr-12 20:45
memberrajesh@198911-Apr-12 20:45 
QuestionProgressbar for showing backup/restore progress Pin
milindgs203-Apr-12 8:14
membermilindgs203-Apr-12 8:14 
QuestionDB delay after restore Pin
quiet.step25-Jan-12 3:11
memberquiet.step25-Jan-12 3:11 
QuestionAuthentican issue - easy solution Pin
saberint11-Oct-11 22:51
membersaberint11-Oct-11 22:51 
AnswerRe: Authentican issue - easy solution Pin
ksbaboo23-Feb-12 6:46
memberksbaboo23-Feb-12 6:46 
GeneralMy vote of 5 Pin
Daniel Vlasceanu19-May-11 10:20
memberDaniel Vlasceanu19-May-11 10:20 
Generalbackup and restore database Pin
sabelopumzile26-Aug-10 1:51
membersabelopumzile26-Aug-10 1:51 
Questionhi pls help me.. Pin
Vaghasiya Nilesh12-Jul-10 4:19
memberVaghasiya Nilesh12-Jul-10 4:19 
GeneralError:Database is in use Pin
Hamid Darabi4-Jan-10 3:38
memberHamid Darabi4-Jan-10 3:38 
GeneralRe: Error:Database is in use Pin
Member 415789213-Mar-10 5:23
memberMember 415789213-Mar-10 5:23 
GeneralError creating backup Pin
AlCsharp17-Oct-09 21:57
memberAlCsharp17-Oct-09 21:57 
GeneralRe: Error creating backup Pin
Nouman_gcu21-Oct-09 1:58
memberNouman_gcu21-Oct-09 1:58 Pin
ananthkind30-Sep-09 11:18
memberananthkind30-Sep-09 11:18 
GeneralOperating System Error 5 (Access is Denied) Pin
Sandeep N. Pamnani7-Sep-09 3:01
memberSandeep N. Pamnani7-Sep-09 3:01 
GeneralRe: Operating System Error 5 (Access is Denied) Pin
Nouman_gcu21-Oct-09 2:01
memberNouman_gcu21-Oct-09 2:01 
GeneralRe: Operating System Error 5 (Access is Denied) Pin
TedChangKorea26-Oct-09 23:06
memberTedChangKorea26-Oct-09 23:06 
Generalexception on sqlRestore method using 3.5 and ms sql server 2005 [modified] Pin
Member 381141030-Jun-09 3:32
memberMember 381141030-Jun-09 3:32 
GeneralRuntime exception in sqlRestore method using 3.5 and sql server 2005 on a windows application Pin
Member 381141029-Jun-09 22:42
memberMember 381141029-Jun-09 22:42 
QuestionsqlRestore.SqlRestore(sqlServer) error Pin
Maciej Gebski4-Jun-09 23:42
memberMaciej Gebski4-Jun-09 23:42 
AnswerRe: sqlRestore.SqlRestore(sqlServer) error Pin
TheChange5-Aug-09 4:24
memberTheChange5-Aug-09 4:24 
GeneralRe: sqlRestore.SqlRestore(sqlServer) error Pin
kanchan.srajput2-Jan-13 1:12
memberkanchan.srajput2-Jan-13 1:12 
GeneralVery good. Pin
itayl27-May-09 13:14
memberitayl27-May-09 13:14 
GeneralSMO vs SQLDMO Pin
qwe123420-Jan-09 4:40
memberqwe123420-Jan-09 4:40 
Generalerror Pin
pokiri4-Jan-09 21:12
memberpokiri4-Jan-09 21:12 
Questioni m getting error : Backup failed for Server 'myservername' Pin
Sandeep Ramani26-Nov-08 22:42
memberSandeep Ramani26-Nov-08 22:42 
QuestionRe: i m getting error : Backup failed for Server 'myservername' Pin
Darwin Tenk12-May-09 3:11
memberDarwin Tenk12-May-09 3:11 
QuestionRestore failed for Server 'SHAM/EXPRESS'. Pin
Member 380125921-Nov-08 0:19
memberMember 380125921-Nov-08 0:19 
GeneralIssues with windows authentication Pin
bigsybiggins15-Oct-08 4:49
memberbigsybiggins15-Oct-08 4:49 
AnswerRe: Issues with windows authentication Pin
srabik4-Feb-09 14:59
membersrabik4-Feb-09 14:59 
GeneralRe: Issues with windows authentication Pin
Ahmed.lpo12-Mar-11 4:23
memberAhmed.lpo12-Mar-11 4:23 
Generalhelpful Pin
shahed hasan17-Sep-08 0:42
membershahed hasan17-Sep-08 0:42 
Questionproblem with logical file names. Pin
Member 232138520-Aug-08 20:44
memberMember 232138520-Aug-08 20:44 
AnswerRe: problem with logical file names. Pin
Bappi M Ahmed7-Oct-08 19:27
memberBappi M Ahmed7-Oct-08 19:27 
GeneralRe: problem with logical file names. Pin
MCAST7623-Mar-09 14:27
memberMCAST7623-Mar-09 14:27 
Generalplease help me Pin
prabhakaraan.s31-Jul-08 1:22
memberprabhakaraan.s31-Jul-08 1:22 
GeneralGreat work.... Pin
Member 122844819-Jul-08 4:45
memberMember 122844819-Jul-08 4:45 
QuestionNice article Pin
setu_raas12-Jun-08 6:04
membersetu_raas12-Jun-08 6:04 
AnswerRe: Nice article Pin
Bappi M Ahmed12-Jun-08 23:47
memberBappi M Ahmed12-Jun-08 23:47 
Generalhi bappi please help me Pin
prabhakaraan.s1-Aug-08 2:46
memberprabhakaraan.s1-Aug-08 2:46 
Questionone error!!!!!!!! Pin
m0rteza8-Jun-08 5:21
memberm0rteza8-Jun-08 5:21 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151120.1 | Last Updated 8 Jun 2008
Article Copyright 2008 by Bappi M Ahmed
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid