Take SQL Database Backup using C#

Take SQL Database Backup using C#

Hi Dear,

I have studied it from one of the Blog. Where using SQL Server SMO (Server Management Object) in C# how to take a SQL Server Database Backup. There are other ways also to take database backup like through wizard in SQL Management Studio.

1.  Add following references to your project

a.  Microsoft.SqlServer.ConnectionInfo
b.  Microsoft.SqlServer.Management.Sdk.Sfc
c.  Microsoft.SqlServer.Smo
d.  Microsoft.SqlServer.SmoExtended
e.  Microsoft.SqlServer.SqlEnum

2.  In your CS file add folliwing 2 Usings statements

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


following classes objects used to take Backup

a.  Backup
b.  BackupDeviceItem
c.  ServerConnection
d.  Server
e.  Database

and following enums are used for assgning values

a.  BackupTruncateLogType ( it contains NoTruncate, Truncate and TruncateOnly)
b.  BackupActionType (it contains Database, Files and Log)


Steps to take Backup:
1.  Create object of Backup Class
2.  Assign Action, Database, BackupSetDescrption, Checksum, Intialize, ContinueAfterError, ExpirationDate etc properties.
3.  Create object of BackupDeviceItem class
4.  Create object of ServerConnection class
5.  Create object of Server class with ServerConnection object
6.  Add object of BackupDeviceItem class as Device to object of Backup Class
7.  Call SqlBackup method of object of Backup Class
8.  Remove Device from object of Backup Class

Code:

public void BackupDatabase(string databaseName, string userName, string password, string serverName, string destinationPath)
            {

                try
                {                  
                    Backup BKP = new Backup();

                    BKP.Action = BackupActionType.Database;
                    BKP.BackupSetDescription = "BackUp of: " + databaseName.ToUpper() + "on" + DateTime.Now.ToString();
                    BKP.BackupSetName = databaseName.ToUpper();
                    BKP.Database = databaseName.ToUpper();

                    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + databaseName.ToUpper() + "_" + DateTime.Now.ToString("ddMMyyyyHHmmss") + ".bak", DeviceType.File);

                    ServerConnection connection = new ServerConnection(serverName, userName, password);

                    Server sqlServer = new Server(connection);
                    sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
                    Database db = sqlServer.Databases[databaseName];

                    BKP.Initialize = true;
                    BKP.Checksum = true;
                    BKP.ContinueAfterError = true;
                    BKP.Devices.Add(deviceItem);
                    BKP.Incremental = false;
                    BKP.ExpirationDate = DateTime.Now.AddDays(3);
                    BKP.LogTruncation = BackupTruncateLogType.Truncate;
                    BKP.FormatMedia = false;
                    BKP.SqlBackup(sqlServer);
                    BKP.Devices.Remove(deviceItem);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                    Console.Read();
                }
            }
        }