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 !.
Reference:
http://www.sqlservercentral.com/articles/C%23/88007/
