Windows Forms: Generate backup script SQL Server in C#

This post shows you How to Generate backup script SQL Server in C# .NET Windows Forms Application.

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

Next, You need to drag 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.

c# generate backup script sql server

After completing the interface design, you need to add the following libraries to your project.




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...

visual studio add reference manager

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

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

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)
                        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)));

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

Related Posts