How to Create a backup SQL Server in C#

By FoxLearn 7/16/2024 8:51:10 AM   8.25K
Creating a full database backup in a C# Windows Forms application involves using SQL Server Management Objects (SMO) from the Microsoft.SqlServer.Smo assembly.

Here’s a step-by-step guide on how to backup SQL Server in C#.

Open your Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "BackupDatabase" and then click OK

Drag and drop the TextBox, Label, Button, Progress Bar controls from the Visual Studio toolbox onto your form designer, then design your form as shown below.

sql database c# backup

Right-click on your project in Solution Explorer, then add references to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.ConnectionInfoExtended, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended

c# backup sql database

Add code to handle your form

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

Adding a click event handler to the Backup button allows you to backup sql database in c#.

// sql database c# backup
private void btnBackup_Click(object sender, EventArgs e)
{
    progressBar.Value = 0;
    try
    {
        //Init connect to sql database
        Server dbServer = new Server(new ServerConnection(txtServer.Text, txtUsername.Text, txtPassword.Text));
        // Define a new backup operation
        Backup dbBackup = new Backup() { Action = BackupActionType.Database, Database = txtDatabase.Text };
        // Set the backup device to a file
        dbBackup.Devices.AddDevice(@"C:\Data\Northwind.bak", DeviceType.File);
        dbBackup.Initialize = true;
        dbBackup.PercentComplete += DbBackup_PercentComplete;
        dbBackup.Complete += DbBackup_Complete;
        // Perform the backup
        dbBackup.SqlBackupAsync(dbServer);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Adding code to handle Complete event handler allows you to update the backup status.

private void DbBackup_Complete(object sender, ServerMessageEventArgs e)
{
    if (e.Error != null)
    {
        //Update status with multiple threads
        lblStatus.Invoke((MethodInvoker)delegate
        {
            lblStatus.Text = e.Error.Message;
        });
    }
}

Adding code to handle PercentComplete event handler allows you to update the progress bar with the percent complete.

private void DbBackup_PercentComplete(object sender, PercentCompleteEventArgs e)
{
    //Update percentage, progressbar
    progressBar.Invoke((MethodInvoker)delegate
    {
        progressBar.Value = e.Percent;
        progressBar.Update();
    });
    lblPercent.Invoke((MethodInvoker)delegate
    {
        lblPercent.Text = $"{e.Percent}%";
    });
}

Through this example, you can easily create a Windows Forms application that backs up a SQL Server database using C#.

VIDEO TUTORIAL