Pages

Thursday, July 30, 2009

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;

9 comments:

Sridhar Babu Kolapalli said...

In the article, backup file will be residing on the server. If you want to get the backup file to your local machine provide a local share folder with a right permission as shown below:

objBkpDeviceItem.Name = "\\\\machinename\\sharefoldername\\sample.bkp";

Unknown said...

Why does it have a tag of Sql2008? I believe this is Sql2005 stuff...

Regards, Jowen

Sridhar Babu Kolapalli said...

Hi Jowen,

This sample work for all the SQL Server versions from SQL Server2000 onwards. Thats the reason I added the Tag for both SQL Server 2005 as well SQL Server 2008.

Please let me know if you have any additional info on the same to share with me.

Unknown said...

I am trying your code for back up operations.. Microsoft.SqlServer.SmoExtended.dll
is that namespace is compulsory.
to get back up operations.
Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();
but you tried your code with out i specified dll.how is it possible?
give your suggestions.

Sridhar Babu Kolapalli said...

Hi Sai,

Yes, namespace is very much required because all the classes refered in the article is from the refered namespace and the dll reference is Microsoft.SqlServer.Management.Smo.dll

You can get all the list of classes available in this dll file @ http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(v=SQL.90).aspx

Unknown said...

if you want to get folder in network what is the code want to refer?please give network code
your code specified is local folder only:
objBkpDeviceItem.Name = "\\\\machinename\\sharefoldername\\sample.bkp";

Unknown said...

I am so impressed by your article. Actually I want that information. Its so much helpful for my project. Thanks for sharing the topic.I am also using the information of the comments.Thanks
digital signature Microsoft

Unknown said...
This comment has been removed by the author.
Julie taylor said...

Your article was very impressive. I really appreciate the research you’ve done and that helps make your article rich. Overall you’ve done great work.
top search engine optimization companies | professional web development company