Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

How to back up SQL Server database

Database backup is routine activity you can take backup using SSMS and using C# code.

Back up MSSQL database using c# code:

You can use following c# code example to take backup of your SQL server database,

 C# Code:

   //references
    Microsoft.SqlServer.ConnectionInfo
    Microsoft.SqlServer.Management.Sdk.Sfc
    Microsoft.SqlServer.Smo
    Microsoft.SqlServer.SmoExtended
    Microsoft.SqlServer.SqlEnum
 
//Method,pass your databaseName, username, password, server name and destination path to save backup file
    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]; (Reference Database As microsoft.sqlserver.management.smo.database,not as System.entity.database )
 
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);
    }
    }


This code will take backup of your all databases in SQL server & As you can see in the above method created using C#, you need to pass all the details of your database like Server Name, User Name, Password, Destination path etc.

There are more options to take back-up of sql server database and is the original source of the above code.