How to taking backup a database using (c#) DotNet framework 3.5? Or using SMO?

How to taking backup a database using (c#) DotNet framework 3.5? Or using SMO?

Add the following reference in your project for using SMO through “Add Reference”.

- Microsoft.SqlServer.ConnectionInfo

- Microsoft.SqlServer.Management.Sdk.Sfc

- Microsoft.SqlServer.Smo

- Microsoft.SqlServer.SmoExtended

- Microsoft.SqlServer.SqlEnum


Now, you can use the following code for taking backup of given database using SMO.


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Management.Smo;

string MachineName="Your MachineName";

string DBName="Your DBName";

string backupName = "tempBackup";

Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(MachineName);

Database db = server.Databases[DBName];

//To Avoid TimeOut Exception

server.ConnectionContext.StatementTimeout = 60 * 60;

RecoveryModel recoverymodel = db.DatabaseOptions.RecoveryModel;

//Define a Backup object variable.

Backup backup = new Backup();

backup.CompressionOption = BackupCompressionOptions.On;

//Specify the type of backup, the description, the name, and the database to be backed up.

backup.Action = BackupActionType.Database;

backup.BackupSetDescription = "Full backup of " + DBName + ".";

backup.BackupSetName = backupName;

backup.Database = DBName;

//Declare a BackupDeviceItem

BackupDeviceItem bdi = default(BackupDeviceItem);

bdi = new BackupDeviceItem(@"C:\ " + backupName + ".bak", DeviceType.File);

//Add the device to the Backup object.

backup.Devices.Add(bdi);

//Set the Incremental property to False to specify that this is a full database backup.

backup.Incremental = false;

//Set the expiration date.

System.DateTime backupdate = new System.DateTime();

backupdate = new System.DateTime(2008, 10, 5);

backup.ExpirationDate = backupdate;

//Specify that the log must be truncated after the backup is complete.

backup.LogTruncation = BackupTruncateLogType.Truncate;

//Run SqlBackup to perform the full database backup on the instance of SQL Server.

backup.SqlBackup(server);

//Remove the backup device from the Backup object.

backup.Devices.Remove(bdi);

That's it...


...S.VinothkumaR.

4 comments:

Unni said...

Hi , Vinoth.

That was a simple and effective.
I have a doubt here.

Why we don't specify any username and password to get connected to server?

On " backup.SqlBackup(server);" am getting "Backup failed for Server 'ANEESH\SQLEXPRESS'.".

CAn you help me with this?

Janaka said...

Hi,

Not sure whether this is too late to leave a comment.

The situation you are having probably because you are trying to back up multiple databases? If that's case then you'll have to discard the "Backup" object after each backup is done. Then create a new "Backup" object for 2nd database.

Hope this makes sense.

Good luck.

Pavan said...

Hello Vinoth please can you help me out. I have to backup mdf and ldf files to a pendrive with a single button click using visual c sharp. I am using SQL Server 2005 Standard Edition and visual studio 2008. I need the source code to detach database and copy those mdf and ldf files to the external media i.e pendrive. I dont want to create.bak file.

nag said...

hi vinod,

thank you for your code. can you give me suggetions on how to do the same on a scheduled basis from asp.net