How to Generate a backup script for SQL Server in C#

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

Creating a new Windows Forms Application, then open your form designer.

Drag and drop the Label, Button and ProgressBar controls form the Visual Studio Toolbox to your form designer, then create a simple UI that allows you to connect to the SQL Server, then generate sql backup script in c# windows forms application as shown below.

You need to add the reference to the assemblies below in your C# project.

Microsoft.SqlServer.Smo

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Management.Sdk.Sfc

You can find it in C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\Smo directory or you can right click on References => then select Add Reference...

Opening your app.config file, then add a connection string to the app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7" />
  </startup>
  <connectionStrings>
    <add name="cn" connectionString="Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=abc;" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

You should change your connection string information to your SQL Server, and don't forget to add a reference to the System.Configuration.dll

Adding a click event handler to the GenerateScript button allows you to generate sql backup script including sql data as the following c# code.

private void btnGenerateScript_Click(object sender, EventArgs e)
{
    using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "SQL Script|*.sql" })
    {
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            string script = "";
            progressBar.Value = 0;
            progressBar.Minimum = 0;
            progressBar.Maximum = 100;
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
            {
                ServerConnection serverConnection = new ServerConnection(connection);
                var server = new Server(serverConnection);
                var database = server.Databases["Northwind"];
                var scripter = new Scripter(server);
                scripter.Options.IncludeDatabaseContext = true;
                scripter.Options.Indexes = true;
                scripter.Options.Triggers = true;
                scripter.Options.IncludeIfNotExists = true;
                scripter.Options.ScriptDrops = false;
                scripter.Options.ScriptSchema = true;
                scripter.Options.ScriptData = true;
                float process = database.Tables.Count;
                float index = 1;
                float percentage = 0;
                var task = Task.Run(() =>
                {
                    foreach (Table table in database.Tables)
                    {
                        if (table.IsSystemObject == true)
                            continue;
                        foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
                            script += s + "\n\n";
                        percentage = (index++ / process) * 100;
                        lblStatus.Invoke((MethodInvoker)(() => lblStatus.Text = $"{(int)percentage}%"));
                        progressBar.Invoke((MethodInvoker)(() => progressBar.Value = ((int)percentage)));
                    }
                    File.WriteAllText(sfd.FileName, script + "\n\n", Encoding.UTF8);
                    percentage = (index++ / process) * 100;
                    lblStatus.Invoke((MethodInvoker)(() => lblStatus.Text = $"{(int)percentage}%"));
                    progressBar.Invoke((MethodInvoker)(() => progressBar.Value = ((int)percentage)));
                });
            }
        }
    }
}

ServerConnection: Establishes a connection to the SQL Server instance using the server name provided.

Server: Represents the SQL Server instance.

Database: Represents the database on which the backup operation will be performed.

Backup: Configures the backup operation and performs the actual backup.

This is a simple way to generate a backup script programmatically within a C# Windows Forms Application using SQL Server Management Objects (SMO).

Generating backup script sql server allows you to restore your sql database to any version SQL Server.