Scribd
Upload
90 views

C# BK SQL

This document describes how to programmatically back up a SQL Server database using C# and SQL Server Management Objects (SMO). It provides code to define a Backup object, specify the database to back up, declare a BackupDeviceItem to store the backup file, connect to the SQL Server instance, and run the SqlBackup method to perform a full database backup. The code handles initialization, checksums, error handling, expiration dates, and transaction log truncation. Familiarity with C#, namespaces like Microsoft.SqlServer.Smo, and SQL Server objects such as Server, Database, and Backup are required to implement database backups using this approach.

Uploaded by

HieuDev
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
90 views

C# BK SQL

This document describes how to programmatically back up a SQL Server database using C# and SQL Server Management Objects (SMO). It provides code to define a Backup object, specify the database to back up, declare a BackupDeviceItem to store the backup file, connect to the SQL Server instance, and run the SqlBackup method to perform a full database backup. The code handles initialization, checksums, error handling, expiration dates, and transaction log truncation. Familiarity with C#, namespaces like Microsoft.SqlServer.Smo, and SQL Server objects such as Server, Database, and Backup are required to implement database backups using this approach.

Uploaded by

HieuDev
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

SQL Server Database BackUp using C#

There are various ways to take the SQL Server database backup. You can take the database
backup using SQL Server backup wizard or using SQL Server BackUp Database
statement. Here I am going to describe how to take the SQL Server database backup
programatically using C# and SQL Server Management Objects (SMO).

In my previous posts, I explained Partial Methods,Contextual Keyword, C# Static Methods
and some other articles related to C#, ASP.Net and SQL Server .

SQL Server Management Objects (SMO) is a collection of objects that are designed for
programming all aspects of managing Microsoft SQL Server.


For taking the database backup using C#, you have to add the following references in your
application-
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.SqlEnum

In your .CS file you will have to use the following namespaces-
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

After using above namespaces, write the following code to take the database backup-
public void BackupDatabase(string databaseName, string userName, string password, string
serverName, string destinationPath)
{
//Define a Backup object variable.
Backup sqlBackup = new Backup();

//Specify the type of backup, the description, the name, and the database to
be backed up.
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" +
DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = "FullBackUp";
sqlBackup.Database = databaseName;

//Declare a BackupDeviceItem
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath +
"FullBackUp.bak", DeviceType.File);
//Define Server connection
ServerConnection connection = new ServerConnection(serverName, userName,
password);
//To Avoid TimeOut Exception
Server sqlServer = new Server(connection);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];

sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;

//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);
//Set the Incremental property to False to specify that this is a full
database backup.
sqlBackup.Incremental = false;

sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
//Specify that the log must be truncated after the backup is complete.
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

sqlBackup.FormatMedia = false;
//Run SqlBackup to perform the full database backup on the instance of SQL
Server.
sqlBackup.SqlBackup(sqlServer);
//Remove the backup device from the Backup object.
sqlBackup.Devices.Remove(deviceItem);
}

Happy coding!!

You might also like