Click here to Skip to main content
11,719,973 members (80,419 online)
Click here to Skip to main content

Tagged as

SQL Server 2008 - Backup and Restore Databases using SMO

, 1 Nov 2010 CPOL 182.7K 21.9K 139
Rate this:
Please Sign up or sign in to vote.
In this article, I will show you how to backup and restore databases using Server Management Objects
Screen01.jpg

Introduction

Sometimes, developers need to backup and restore databases. This is not a problem when you have SQL Server Management Studio installed. But what if Management Studio is not installed? In this case, you can use sqlcmd utility. In some cases, you need to backup databases programmatically and this could be done via Server Management objects. In this article, I will shortly describe what SMO is and how to use it for database backup and restore.

Server Management Objects (SMO) is a collection of classes that represent the core SQL Server Database Engine objects. SMO allows you to programmatically manipulate SQL Server (2008, 2005, 2000 and 7.0). All functions available in SQL Server Management Studio are available in SMO but SMO includes several more features than Management Studio.

Background

Before writing any line of code, you must set the reference to the SMO assembly. In this article, you need add these components:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.ConnectionInfo

When the references are added, you must add 2 using statements for two namespaces:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Now you can start working with SMO. Before doing restore and backup tasks, you must connect to the server. This connection is provided by ServerConnection object. It lets you connect to the server and assign that connection to the Server object. The Server object represents an instance of SQL Server. In SMO programming, the Server object determines the connection to a physical SQL Server installation. When you are connected to the server, you can use 2 classes to perform backup and restore operations:

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

Using the Code

In this project, you can select whether you want to connect to the local instances of SQL Server or remote instances. In case you want to connect to the local instance, the fastest way to obtain a list of local instances is through registry.

The following code snippet shows you how to perform this:

RegistryKey rk = Registry.LocalMachine.OpenSubKey
		(@"SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
    foreach (String element in instances)
    {
        if (element == "MSSQLSERVER")
            lstLocalInstances.Items.Add(System.Environment.MachineName);
        else
            lstLocalInstances.Items.Add(System.Environment.MachineName + @"\" + element);
    }
}

For getting a list of remote instances, you have to use EnumAvailableSqlServers method of SmoApplication class. This method returns DataTable with a few columns. In this case, we will use Name column. This method has one parameter LocalOnly of boolean data type. When this parameter is true, then the method returns only local instances.

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);

if (dt.Rows.Count > 0)
{
    foreach (DataRow dr in dt.Rows)
    {
        AddNetworkInstance(dr["Name"].ToString());
    }
}

This part of code populates lstNetworkInstances with all instances of SQL Server asynchronously.

When instance is chosen, then you can connect to this instance by clicking on Connect button. After that, instance of ServerConnection is created with specified serverInstance property and authentication credentials. This project allows you to choose between 2 authentication modes (Windows authentication and SQL Server authentication). It's only up to you, which authentication mode you want to use. When you choose Windows Authentication, just check "Use Windows Authentication" checkbox. When you choose the second mode, then you must specify login and password.

When you are connected, a list of databases is got from Databases property of Server class. This property is a collection of Database objects. You can easily loop through this collection and get name of every database in collection by reading Name property.

Once you have selected server and database, you can perform backup operation. For backup operation, you must create instance of Backup class and then assign the Action property to BackupActionType.Database. Now you have to add device you want to backup to. In many cases, it is file. You can backup not only to file, but you can backup to tape, logical drive, pipe and virtual device. You can also set Incremental property indicating whether you want to perform incremental backup or not.

Backup bkp = new Backup();

this.Cursor = Cursors.WaitCursor;
this.dataGridView1.DataSource = string.Empty;
try
{
    string fileName = this.txtFileName.Text;
    string databaseName = this.ddlDatabase.SelectedItem.ToString();

    bkp.Action = BackupActionType.Database;
    bkp.Database = databaseName;
    bkp.Devices.AddDevice(fileName, DeviceType.File);
    bkp.Incremental = chkIncremental.Checked;
    this.progressBar1.Value = 0;
    this.progressBar1.Maximum = 100;
    this.progressBar1.Value = 10;

    bkp.PercentCompleteNotification = 10;
    bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

    bkp.SqlBackup(srv);
    MessageBox.Show("Database was successfully backed up to: " + fileName, "Info");
}

catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
finally
{
    this.Cursor = Cursors.Default;
    this.progressBar1.Value = 0;
}

This example allows you to select backup file but be careful, this is not a local path. It is path within a selected instance.

SMO allows you to monitor progress of backup operation being performed. You can easily implement this feature. The first thing you must do is to create an event handler with PercentCompleteEventArgs parameter. This parameter includes Percent property which contains percent complete value. This value is an integer between 0 and 100.

public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
    this.progressBar1.Value = e.Percent;
}

Performing log backup operation is similar to database backup. Just set Action property to Log instead of Database.

After you perform backup, it is good to verify that the backup was performed correctly and backup is not corrupted. You can easily do this by using SqlVerify method of instance of Restore class.

Restore rest = new Restore();
string fileName = this.txtFileName.Text;

this.Cursor = Cursors.WaitCursor;
this.dataGridView1.DataSource = string.Empty;

try
{
    rest.Devices.AddDevice(fileName, DeviceType.File);
    bool verifySuccessful = rest.SqlVerify(srv);

    if (verifySuccessful)
    {
        MessageBox.Show("Backup Verified!", "Info");
        DataTable dt = rest.ReadFileList(srv);
        this.dataGridView1.DataSource = dt;
    }
    else
    {
        MessageBox.Show("ERROR: Backup not verified!", "Error");
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
} 
finally
{
    this.Cursor = Cursors.Default;
}

Let’s consider you have your database successfully backed up. It would be great to restore database from this backup. SMO allows you to do this very easily. Database restore operation is performed by Restore class which is in Microsoft.SqlServer.Management.Smo.Restore namespace. Before running any restore, you must provide database name and valid backup file. Then you must set Action property. In case you want to restore database, set it to RestoreActionType.Database. In case you want to restore Log, just set it to RestoreActionType.Log. During restore, you can monitor progress of restoring operation. This could be done the same way as in case of database backup.

Restore res = new Restore();

this.Cursor = Cursors.WaitCursor;
this.dataGridView1.DataSource = string.Empty;

try
{
    string fileName = this.txtFileName.Text;
    string databaseName = this.ddlDatabase.SelectedItem.ToString();

    res.Database = databaseName;
    res.Action = RestoreActionType.Database;
    res.Devices.AddDevice(fileName, DeviceType.File);

    this.progressBar1.Value = 0;
    this.progressBar1.Maximum = 100;
    this.progressBar1.Value = 10;

    res.PercentCompleteNotification = 10;
    res.ReplaceDatabase = true;
    res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
    res.SqlRestore(srv);

    MessageBox.Show("Restore of " + databaseName + 
	" Complete!", "Restore",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
finally
{
    this.Cursor = Cursors.Default;
    this.progressBar1.Value = 0;
}

Conclusion

In this article, I have described how to use SMO to perform database backup and restore operations. Abilities of SMO are not limited to these 2 types of operations. SMO provides a lot of features to interact with SQL Server.

History

  • 31 Oct 2010 - Original version posted

License

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

Share

About the Author

Kanasz Robert
Architect The Staffing Edge & Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist

Open source projects: DBScripter - Library for scripting SQL Server database objects


Please, do not forget vote

You may also be interested in...

Comments and Discussions

 
QuestionRestore database doesn't work while i remote from server, help me. Pin
hongthai9111-Feb-15 17:09
memberhongthai9111-Feb-15 17:09 
Questionsql server 2008 not showing attached database Pin
Salmanqazi19-Nov-14 21:53
memberSalmanqazi19-Nov-14 21:53 
QuestionI tried to run but it is not working Pin
Member 1053818311-Jun-14 21:29
memberMember 1053818311-Jun-14 21:29 
QuestionGreat Work But... Pin
Uknownymous14-Jan-14 22:53
memberUknownymous14-Jan-14 22:53 
Questionhow can work with unatached database Pin
Member 34809762-Jan-14 10:16
memberMember 34809762-Jan-14 10:16 
QuestionVery Good Pin
Member 1547522-Dec-13 5:38
memberMember 1547522-Dec-13 5:38 
AnswerRe: Very Good Pin
Kanasz Robert7-Jan-14 21:36
mvpKanasz Robert7-Jan-14 21:36 
QuestionVery Good Pin
Eliass24-Sep-13 1:07
groupEliass24-Sep-13 1:07 
AnswerRe: Very Good Pin
Kanasz Robert21-Oct-13 2:16
mvpKanasz Robert21-Oct-13 2:16 
QuestionNo SMO needed Pin
RickZeeland7-Sep-13 22:01
professionalRickZeeland7-Sep-13 22:01 
AnswerRe: No SMO needed Pin
Kanasz Robert7-Jan-14 21:38
mvpKanasz Robert7-Jan-14 21:38 
GeneralMy vote of 3 Pin
Vincent Paukgyi10-Jun-13 20:34
memberVincent Paukgyi10-Jun-13 20:34 
GeneralMy vote of 5 Pin
Fazal Abu4-Jun-13 8:56
memberFazal Abu4-Jun-13 8:56 
GeneralRe: My vote of 5 Pin
Kanasz Robert4-Jun-13 9:44
mvpKanasz Robert4-Jun-13 9:44 
GeneralMy vote of 5 Pin
Bernard Chayer31-May-13 8:35
memberBernard Chayer31-May-13 8:35 
GeneralRe: My vote of 5 Pin
Kanasz Robert31-May-13 9:03
mvpKanasz Robert31-May-13 9:03 
GeneralMy vote of 5 Pin
TechnoGeek00120-Apr-13 3:13
memberTechnoGeek00120-Apr-13 3:13 
GeneralRe: My vote of 5 Pin
Kanasz Robert20-Apr-13 6:53
mvpKanasz Robert20-Apr-13 6:53 
QuestionIssue in restore of incremental backup Pin
Rajeev from Patna15-Apr-13 4:11
memberRajeev from Patna15-Apr-13 4:11 
QuestionWell done Pin
xmaster123_212-Mar-13 23:54
memberxmaster123_212-Mar-13 23:54 
AnswerRe: Well done Pin
Kanasz Robert13-Mar-13 3:14
mvpKanasz Robert13-Mar-13 3:14 
QuestionNice Pin
strucker_luc18-Nov-12 3:13
memberstrucker_luc18-Nov-12 3:13 
AnswerRe: Nice Pin
Kanasz Robert18-Nov-12 3:22
mvpKanasz Robert18-Nov-12 3:22 
QuestionPlease check Pin
vanmyvo9-Nov-12 22:33
membervanmyvo9-Nov-12 22:33 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:54
memberkr1234564-Nov-12 3:54 
well done
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:05
mvpKanasz Robert4-Nov-12 4:05 
Questionhelpful Pin
superdevX151-Nov-12 6:49
membersuperdevX151-Nov-12 6:49 
AnswerRe: helpful Pin
Kanasz Robert1-Nov-12 6:56
mvpKanasz Robert1-Nov-12 6:56 
Questiongot my 5 Pin
hakon12331-Oct-12 5:27
memberhakon12331-Oct-12 5:27 
AnswerRe: got my 5 Pin
Kanasz Robert31-Oct-12 5:38
mvpKanasz Robert31-Oct-12 5:38 
QuestionExcellent Pin
memlon mulas29-Oct-12 5:13
membermemlon mulas29-Oct-12 5:13 
AnswerRe: Excellent Pin
Kanasz Robert29-Oct-12 5:20
mvpKanasz Robert29-Oct-12 5:20 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:53
memberjackhoal27-Oct-12 3:53 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:01
mvpKanasz Robert27-Oct-12 4:01 
Questiongreat article Pin
robkaan27-Oct-12 3:24
memberrobkaan27-Oct-12 3:24 
AnswerRe: great article Pin
Kanasz Robert27-Oct-12 3:32
mvpKanasz Robert27-Oct-12 3:32 
Questionhelpful Pin
windevvv21-Oct-12 6:46
memberwindevvv21-Oct-12 6:46 
AnswerRe: helpful Pin
Kanasz Robert21-Oct-12 7:01
mvpKanasz Robert21-Oct-12 7:01 
QuestionGreat Pin
suresh_skt14-Oct-12 16:57
membersuresh_skt14-Oct-12 16:57 
AnswerRe: Great Pin
Kanasz Robert16-Oct-12 3:37
mvpKanasz Robert16-Oct-12 3:37 
GeneralMy vote of 5 Pin
Pankaj Nikam14-Oct-12 16:00
memberPankaj Nikam14-Oct-12 16:00 
GeneralRe: My vote of 5 Pin
Kanasz Robert16-Oct-12 3:38
mvpKanasz Robert16-Oct-12 3:38 
GeneralRe: My vote of 5 Pin
Pankaj Nikam16-Oct-12 4:51
memberPankaj Nikam16-Oct-12 4:51 
Questionperform a restore using SMO example that does a Full database restore of the SMO database from the file D:\SMOTest.bak Pin
Ms komika battranah6-Oct-12 17:49
memberMs komika battranah6-Oct-12 17:49 
AnswerRe: perform a restore using SMO example that does a Full database restore of the SMO database from the file D:\SMOTest.bak Pin
AFMatambo19-Oct-12 5:07
memberAFMatambo19-Oct-12 5:07 
Questiongood job Pin
kaslaninovic2-Oct-12 22:44
memberkaslaninovic2-Oct-12 22:44 
AnswerRe: good job Pin
Kanasz Robert3-Oct-12 6:52
mvpKanasz Robert3-Oct-12 6:52 
Questiongood article Pin
developer88123-Sep-12 2:58
memberdeveloper88123-Sep-12 2:58 
AnswerRe: good article Pin
Kanasz Robert23-Sep-12 23:08
mvpKanasz Robert23-Sep-12 23:08 
QuestionVery helpful Pin
bikerius19-Sep-12 2:00
memberbikerius19-Sep-12 2:00 

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 | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 1 Nov 2010
Article Copyright 2010 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid