Backup and Restore database in .Net

| July 21, 2010 | 0 Comments

You wish to backup and restore SQL database from Window application. So taking point in context we had developed VSSC.  VSSC acronym for Vikram SQL Server Controller is a small application which connects to SQL Server. It let you see all database(s) associated with particular instance or Server dynamically along with tables. Even you can take backup & restore database on the fly.

Pre-Requisites

  1. OS – Windows XP SP2 or greater
  2. Visual Studio 2005 or greater
  3. SQL Server 2005 or greater
  4. .NET Framework 2.0 or greater
  5. You need to modify App.config  file to the backup folder of SQL Server.

Concepts Covered

Following are the topics covered in the application:

  • Interacting with SQL Server using Microsoft.SqlServer.Management to retrieve database and their respective tables using DatabaseCollection & Database classes.
  • Backup and Restore using Backup & Restore classes respectively.
  • Opening popup window on clicking link.
  • Changing Opacity of form.
  • Passing previous window form values to next form.
  • Using BackgroundWorker component to run another thread.
  • Using RegularExpression for matching date format.
  • Creating controls, Increasing & Decreasing size of the Form dynamically.

Main Form

SQL Server Controller snapshot

Vikram SQL Server Controller

Code Snippet (Form1.cs) for Main Form 
On Form Load:-
(1) Declaring instance of Server & retrieving Server name in Server TextBox(txtServer)
(2) Initialize BackgroundWorker component(named hiddenRunner)
(3) Setting Control.CheckForIllegalCrossThreadCalls = false for avoiding InvalidOperationException in case we are trying to access database combobox (cboDB).

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management; //Namespace added
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;<
/// Author: Vikram Singh Saini
/// Date: 15th July 2010
/// Time: 10:46 PM
///
/// REFERENCE ADDED &amp; EXPLANATION
/// 1. Microsoft.SqlServer.ConnectionInfo for ConnectionContext
/// 2. Microsoft.SqlServer.Smo for Server, Database, BackupDeviceItem, Backup
/// 3. Microsoft.SqlServer.SqlEnum for Database Options
///</summary>

namespace MySqlServerControl
{
public partial class Form1 : Form
{
Server server;
public static string[] stringDb;

public Form1()
{
InitializeComponent();
InitializeBackgoundWorker();
Control.CheckForIllegalCrossThreadCalls = false;
}

private void InitializeBackgoundWorker()
{
hiddenRunner.DoWork += new DoWorkEventHandler(hiddenRunner_DoWork);
hiddenRunner.RunWorkerCompleted += new RunWorkerCompletedEventHandler(hiddenRunner_RunWorkerCompleted);
}

private void Form1_Load(object sender, EventArgs e)
{
txtServer.Text = System.Environment.MachineName;
}

In Connect_Click we are calling BackgroundWorker’s function RunWorkerAsync() so that connection to server can be done within another thread without hanging the application. And displaying Label lblStatus’s image to user to let him know that connection process is going on.RunWorkerAsync()calls DoWork method of hiddenRunner.

private void btnConnect_Click(object sender, EventArgs e)
        {
            // Calling BackgroundWorker
            hiddenRunner.RunWorkerAsync();
            lblStatus.Image = Image.FromFile(Environment.CurrentDirectory +
                              "\\Image\\progress.gif");
        }

DoWork() uses ConnectionContext to get details of Sql Server instance and then uses ConnectionString property to set ConnectionString and then Connect() method used for connection.
Microsoft.SqlServer.Management.Smo.DatabaseCollection is used for retrieving all databases of server associated with instance.

private void hiddenRunner_DoWork(object sender, DoWorkEventArgs e)
        {
            lblStatus.Text = "";
            cboDB.Items.Clear();
#region Creating instance of SqlServer
server = new Server();
try
{
server.ConnectionContext.ConnectionString = "Server=" + txtServer.Text +
"; User ID="+ txtUserId.Text + ";Password=" + txtPassword.Text;
server.ConnectionContext.Connect();
}
catch (Exception ex)
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: Server " + txtServer.Text + " not found."+
"Make sure server is running. Please check all value entered are correct.!";
}
#endregion

if (server.ConnectionContext.IsOpen)
{
lblStatus.ForeColor = Color.Blue;
lblStatus.Text = "INFO: VSSC connected to " + txtServer.Text + " successfully!";

#region Retrieving all database associated with server instance

DatabaseCollection dbCollection = server.Databases;
stringDb = new string[dbCollection.Count];
int iCounter = 0;

foreach (Database db in dbCollection)
{
stringDb[iCounter] = db.ToString();
iCounter++;
}

if (stringDb.Length &gt; 0)
{
cboDB.Items.AddRange(stringDb);
cboDB.SelectedIndex = 0;
}

#endregion
}
}

In cboDB SelectedIndexChanged event we are getting all tables in that database by using TableCollection

private void cboDB_SelectedIndexChanged(object sender, EventArgs e)
        {
            listTables.Items.Clear();</pre>
Database db = server.Databases[cboDB.SelectedIndex];
TableCollection tableCollection = db.Tables;

string[] strTables=null;
if (tableCollection.Count &gt; 0)
{
strTables = new string[tableCollection.Count];
}
int iCount = 0;

foreach (Table table in tableCollection)
{
strTables[iCount] = table.ToString();
iCount++;
}

if (strTables != null)
{
listTables.Items.AddRange(strTables);
}
}

private void btnCancel_Click(object sender, EventArgs e)
{
Application.Exit();
}

We set Opacity of the form to 0.5 on opening Backup & Restore Window Form as popup. Again opacity is reset in Form1 Activated event which is called whenever Form gets activated.
RunWorkerCompleted event handler of RunWorkerCompleted event of hiddenRunner is used to set image property of Label lblStatus to null.

private void backupLink_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            this.Opacity = 0.5;
            using (MyBackup bkup = new MyBackup())
            {
                bkup.ShowDialog();
            }
        }
private void Form1_Activated(object sender, EventArgs e)
{
// Opacity reset
this.Opacity = 1.0;
}

private void hiddenRunner_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
lblStatus.Image = null;
}

}
}

Backup & Restore Form

Backup and Restore Form snapshot

Backup and Restore Form

Code Snippet (MyBackup.cs) for Backup & Restore Form
I am not covering all minute details again here. Please download code for more. As I am lazy person to write all thing again. You will find dynamically adding controls to the form. We have used RegularExpression for matching date format. Below is the code for Backup() action.

private void Backup()
        {
            server = new Server();
            if (cboDatabase.Items.Count &gt; 1 &amp;&amp; myCheck)
            {
                lblStatus.Text = "";
                int iChars = cboDatabase.SelectedItem.ToString().Length;
                string database = cboDatabase.SelectedItem.ToString().Substring(1, iChars - 2);
#region Reference the database to backup.

db = default(Database);
db = server.Databases[database];

//Recovery model is the controlling of transaction logs so that in future database can be backup
//recoverymod = (int)db.DatabaseOptions.RecoveryModel;

//Define a Backup object variable.
Backup bk = new Backup();

//Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database;

bk.BackupSetDescription = "Full backup of" + tb.Text;
bk.BackupSetName = tb.Text;
bk.Database = tb3.Text;
#endregion

#region Set the expiration date.

int[] ticks = new int[3];
Regex phoneRegex = new Regex("^[\\d]{4}[,][\\d]{2}[,][\\d]{2}$");
Match getMatch = phoneRegex.Match(tb2.Text);
if (getMatch.Success)
{
string[] dateParts = tb2.Text.Split(',');
for (int i = 0; i &lt; 3; i++)
{
ticks[i] = Convert.ToInt32(dateParts[i]);
}
System.DateTime backupdate = new System.DateTime(ticks[0], ticks[1], ticks[2]);
bk.ExpirationDate = backupdate;

#region BackupDeviceItem

//Declare a BackupDeviceItem by supplying the backup device file name in the constructor,
// and the type of device is a file.
bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(tb.Text, DeviceType.File);

//Add the device to the Backup object.
bk.Devices.Add(bdi);

//Set the incremental property to False to specify that this is a full database backup.
bk.Incremental = false;

//Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate;

try
{
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(server);
}
catch (Exception exc)
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: Backup failed for Server " + server.ToString();
}

#endregion

//Remove the backup device from the Backup object.
bk.Devices.Remove(bdi);

//Inform the user that the backup has been completed.
lblStatus.ForeColor = Color.Blue;
lblStatus.Text = "INFO: Backup of database " + tb3.Text + " completed successfully!";
}
else
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: Format of date should be as 2010,07,31!";
}
#endregion
}
else
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: No database found for backup. Go Back &amp; Connect to Server.Then select database to backup.";
}
}

Below  is the code for Restore action. BackupDeviceItem is the class to specify the type of device where backup or restore would be done. It can be logical hard disk file or virtual drive or some pipeline.

if (cboBackupFiles.SelectedItem.ToString() != "No BackupFile found" &amp;&amp; cboBackupFiles.Items.Count &gt; 0)
            {
                lblStatus.Text = "";
                try
                {
                    server = new Server();
bdi = new BackupDeviceItem();
bdi.Name = cboBackupFiles.SelectedItem.ToString();
bdi.DeviceType = DeviceType.File;

//Define a Restore object variable.
Restore rs = new Restore();

//Set the NoRecovery property to false, so the transactions are not recovered.
rs.Action = RestoreActionType.Database;
rs.NoRecovery = false;

//Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi);

//Specify the database name.
rs.Database = tb.Text;

//Restore the full database backup with no recovery.
rs.SqlRestore(server);

//Remove the device from the Restore object.
rs.Devices.Remove(bdi);

// If restore done display message to the user
lblStatus.ForeColor = Color.Blue;
lblStatus.Text = "INFO: Database " + tb.Text + " restored successfully!";
}
catch (Exception ex)
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: Restore failed for " + cboBackupFiles.SelectedItem.ToString()
+ ".Reasons: Database already exists or Database Name provided wrong.";
}

}
else
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = "ERROR: No BackupFile selected. Select backup file.";
}

For more about SQL Server Management Object visit SMO.

Download Source Code

Tags: , , , ,

Category: .NET

Leave a Reply

%d bloggers like this: