I am going to create a backup using a C# Windows forms project. The project is simple. You will have a button to create a database backup in the windows form. Every time that you click the button, a new backup is created.

This project includes the following components:

• A database backup: test.zip
• The Windows form project: backup.zip
• A script with the backup stored procedure: createBackup.sql

Requirements

The requirements to build this are:

• Visual Studio 2008 or later
• SQL Server 2005 or later

Getting started

Let’s start. We are going to create a stored procedure first that creates a backup of the test database (you can create a test database or restore from the test.bak attached manually).

The following T-SQL will do this:

create procedure [dbo].[backupdb]
as
BACKUP DATABASE [test] TO  DISK = N'C:\backup\test.bak'
  WITH NOFORMAT
     , NOINIT
     , NAME = N'test copy'
     , SKIP
     , NOREWIND
     , NOUNLOAD
     , STATS = 10

Peace of cake, isn’t it? We are creating the test database backup in the c:\backup folder and the backup name is test.bak. Subsequent backups are all written to the same file. Everything to do this is in the stored procedure named dbo.backupdb.

To execute the stored procedure use the following command:

exec [dbo].[backupdb]

The stored procedure will create a backup inside the test.bak file. 

Now, let’s start with the Visual Studio application. Open the backup.sln in the backup.zip file. In the Solution Explorer, double click in the app.config item.  The content of the file should be the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="connectionStringName"
         connectionString="Data Source=.\SQLEXPRESS
         ;Initial Catalog=test;Integrated Security=True;async=true "/>
  </connectionStrings>
</configuration>

Let me explain this part of the app.config:

connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=True;async=true "/>

This file contains the connection to the SQL Server. In this case, it is a local SQL Server Express Edition. The initial catalog is the database test and it is using integrated security (Windows authentication).

To use the app.config file it is necessary to add the System.configuration:

The system configuration references needs to be added. To do this, in the Solution Explorer, in references right click the button mouse and select add references.

In the references window, select the System Configuration.

In the design pane, double click the backup button to see the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace backup
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string ConnectionString = ConfigurationManager.ConnectionStrings["connectionStringName"].ToString();
                SqlConnection cnn = new SqlConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand("backupdb", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cnn.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Backup completed successfully");
                Cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

Note that the following lines are not created by default. These lines of code need to be added in order to connect to SQL Server and to use the App.config file.

using System.Data.SqlClient;
using System.Configuration;

Let me explain these lines of code:

string ConnectionString = ConfigurationManager.ConnectionStrings["connectionStringName"].ToString();

This line of code will save the connection data created in the app.config file in the connectionString.

The following lines of code are used to call the stored procedure backupdb and execute the stored procedure using Visual Studio.

SqlCommand cmd = new SqlCommand("backupdb", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cnn.Open();
cmd.ExecuteNonQuery();

These lines of code will call the stored procedure created and execute it.

That’s it !. You have a button to create a backup.

In the project, just press F5 in order to start the project. In the Windows form press the backup button. You will create the database backup in the c:\backup\test.bak. If the file was created, you successfully created a Project in Visual Studio to generate SQL Server backups !.

Conclusion

In this article, we created a stored procedure to generate database backups and then we learned how to call the stored procedure in a C# Windows Form application in order to backup a database..

References

http://msdn.microsoft.com/en-us/library/ms186865.aspx