Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to create a database backup using visual studio

By Daniel Calbimonte,

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

   

Resources:

backup.zip | createBackup.sql | test.zip
Total article views: 7991 | Views in the last 30 days: 8
 
Related Articles
FORUM

Creating view from stored procedure results

Creating view from stored procedure results

FORUM

Server Permissions in a Stored Procedure with a Certificate

Granting a stored procedure the permissions to create database logins

FORUM

stored procedures in a database

stored procedures in a database

FORUM

Stored Procedure & 'other' database

How to have a Stored Procedure work on another database

Tags
backup / restore    
c#    
sql server    
visual studio    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones