SQL Server Database Backups using SMO in C#

In the previous post we looked at the What is SMO and where we can use SMO. Now we will see how we can use SMO for taking SQL Server Database backups in C# code.

The first step in our code is to estblish the connection to the database. In our regular .net applications we will use SqlConnection for connecting to the database server. In SMO APIs we will be using the class called "Server" as shown below:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name");

if we are planning to connect to any particular instance, we can even pass the instance name as part of the database server name as:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name\\Instance Name");

With this line we will be establishing the connectivity to the database server. To get any information about the server, we can use this object. For example to get the SQL Server edition like Enterprise/Standard/Developer edition, we can use Information property within the Server object as shown below:

String sEdition = objSrv.Information.Edition;

Using this Information class we can get lots of information about the SQL Server like OSVersion, Collation, CaseSensitive, Clustered, Physical Memory and Version etc.

Next step for us is to create the instance of the Backup class which is core part for taking the backup of the database:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();

There is should some option to provide details like Database Name, Backup Options (Database/Log File/Data File). The option is nothing but the Backup class as shown below:

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

And also we have to provide the info about the Device (File/Tape/Pipe) for taking the backup. For this we will be using the BackupDeviceItem class.

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "C:\\filename.bak";

Once device is created we have to add it to the Backup instance as:

objBkpUp.Devices.Add(objBkpDeviceItem);

Finally we are ready to initiate a call for the backup operation by passing the Server instance:

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Here is the complete code:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();
Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("DBName");


Console.WriteLine("Server Edition: " + objSrv.Information.Edition);

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "h:\\zzTest.bak";

objBkpUp.Devices.Add(objBkpDeviceItem);

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Please note that we have to add the following assembly references:

Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo

and in the code we have to refer following namespace:

using Microsoft.SqlServer.Management.Smo;