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

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

By , 7 Jun 2008
Rate this:
Please Sign up or sign in to vote.

Introduction

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

Pre-Requisite

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:" + 
                                     DateTime.Now.ToShortDateString();
    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.Devices.Add(deviceItem);
    sqlBackup.Incremental = false;

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

    sqlBackup.FormatMedia = false;

    sqlBackup.SqlBackup(sqlServer);
}

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.Devices.Add(deviceItem);
    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);
            
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

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.

Conclusion

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.

License

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:
http://iambappi.wordpress.com
http://iambappi.spaces.live.com/

Comments and Discussions

 
QuestionMy vote of 5 Pinprofessionalabbaspirmoradi8-Sep-13 7:22 
GeneralMy vote of 3 Pinmemberbunzitop26-Mar-13 3:41 
GeneralMy vote of 5 PinmemberMember 966765523-Jan-13 18:39 
AnswerAbout restore and backup Pinmemberarunarw6-Aug-12 5:26 
GeneralRe: About restore and backup Pinmembersk. safaruddin17-Aug-12 21:27 
QuestionSQL Server 2005 Database Backup and Restore using C# and .NET 2.0 (View) Author Bappi M Ahmed Pinmembervinhhc17-Jul-12 15:33 
AnswerSQL Server 2005 Database Backup and Restore using C# and .NET 2.0 (View) Author Bappi M Ahmed Pinmembervinhhc17-Jul-12 15:18 
QuestionTesting Pinmemberblochjaved12322-May-12 3:14 
Questioni am using ms sql 2008 R2 version Pinmemberrajesh@198911-Apr-12 19:45 
how it works with (ms sql 2008 r2).
An inner exception has occur: "This SQL Server version (10.50) is not supported."
And displaying message: "Failed to connect to server INFOMATRIX-PC";
QuestionProgressbar for showing backup/restore progress Pinmembermilindgs203-Apr-12 7:14 
QuestionDB delay after restore Pinmemberquiet.step25-Jan-12 2:11 
QuestionAuthentican issue - easy solution Pinmembersaberint11-Oct-11 21:51 
AnswerRe: Authentican issue - easy solution Pinmemberksbaboo23-Feb-12 5:46 
GeneralMy vote of 5 PinmemberDaniel Vlasceanu19-May-11 9:20 
Generalbackup and restore database Pinmembersabelopumzile26-Aug-10 0:51 
Questionhi pls help me.. PinmemberVaghasiya Nilesh12-Jul-10 3:19 
GeneralError:Database is in use PinmemberHamid Darabi4-Jan-10 2:38 
GeneralRe: Error:Database is in use PinmemberMember 415789213-Mar-10 4:23 
GeneralError creating backup PinmemberAlCsharp17-Oct-09 20:57 
GeneralRe: Error creating backup PinmemberNouman_gcu21-Oct-09 0:58 
Generalvb.net Pinmemberananthkind30-Sep-09 10:18 
GeneralOperating System Error 5 (Access is Denied) PinmemberSandeep N. Pamnani7-Sep-09 2:01 
GeneralRe: Operating System Error 5 (Access is Denied) PinmemberNouman_gcu21-Oct-09 1:01 
GeneralRe: Operating System Error 5 (Access is Denied) PinmemberTedChangKorea26-Oct-09 22:06 
Generalexception on sqlRestore method using C#.net 3.5 and ms sql server 2005 [modified] PinmemberMember 381141030-Jun-09 2:32 
GeneralRuntime exception in sqlRestore method using C#.net 3.5 and sql server 2005 on a windows application PinmemberMember 381141029-Jun-09 21:42 
QuestionsqlRestore.SqlRestore(sqlServer) error PinmemberMaciej Gebski4-Jun-09 22:42 
AnswerRe: sqlRestore.SqlRestore(sqlServer) error PinmemberTheChange5-Aug-09 3:24 
GeneralRe: sqlRestore.SqlRestore(sqlServer) error Pinmemberkanchan.srajput2-Jan-13 0:12 
GeneralVery good. Pinmemberitayl27-May-09 12:14 
GeneralSMO vs SQLDMO Pinmemberqwe123420-Jan-09 3:40 
Generalerror Pinmemberpokiri4-Jan-09 20:12 
Questioni m getting error : Backup failed for Server 'myservername' PinmemberSandeep Ramani26-Nov-08 21:42 
QuestionRe: i m getting error : Backup failed for Server 'myservername' PinmemberDarwin Tenk12-May-09 2:11 
QuestionRestore failed for Server 'SHAM/EXPRESS'. PinmemberMember 380125920-Nov-08 23:19 
GeneralIssues with windows authentication Pinmemberbigsybiggins15-Oct-08 3:49 
AnswerRe: Issues with windows authentication Pinmembersrabik4-Feb-09 13:59 
GeneralRe: Issues with windows authentication PinmemberAhmed.lpo12-Mar-11 3:23 
Generalhelpful Pinmembershahed hasan16-Sep-08 23:42 
Questionproblem with logical file names. PinmemberMember 232138520-Aug-08 19:44 
AnswerRe: problem with logical file names. PinmemberBappi M Ahmed7-Oct-08 18:27 
GeneralRe: problem with logical file names. PinmemberMCAST7623-Mar-09 13:27 
Generalplease help me Pinmemberprabhakaraan.s31-Jul-08 0:22 
GeneralGreat work.... PinmemberMember 122844819-Jul-08 3:45 
QuestionNice article Pinmembersetu_raas12-Jun-08 5:04 
AnswerRe: Nice article PinmemberBappi M Ahmed12-Jun-08 22:47 
Generalhi bappi please help me Pinmemberprabhakaraan.s1-Aug-08 1:46 
Questionone error!!!!!!!! Pinmemberm0rteza8-Jun-08 4:21 
AnswerRe: one error!!!!!!!! PinmemberBappi M Ahmed9-Jun-08 19:08 
GeneralRe: one error!!!!!!!! PinmemberMember 232138520-Aug-08 6:43 

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.140415.2 | Last Updated 8 Jun 2008
Article Copyright 2008 by Bappi M Ahmed
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid