Backup and Restore database in ASP.NET

Introduction

Sometimes we need to provide backup and restore facility in ASP.NET application in application's control panel. In this article, I am going to explain how to develop a database backup and restore application in ASP.NET. I have taken example of SQL Server in this article however, this application can be used to backup and restore any database that .NET supports. You just need to change their provider instead of System.Data.SqlClient and a single SQL Statement to list the table names of the database. I will explain them in details later in this article.

My Backup and Restore web application looks like below:

Let us develop this application in few easy steps.

Step 1 - Create a UI for database backup and restore

In this step, let us create a simple UI in ASP.NET to list all the database tables in the ListBox control that let the end user select the table to take backup and restore. Also put two buttons named Backup and Restore to do its respective functionality. My code for above UI looks like below:

<form id="form1" runat="server">
<div>
<table cellpadding="2" cellspacing="1" border="1" style="border-collapse: collapse;">
<tr>
<th style="background-color:#e0e0e0;">
Backup and Restore SQL Server
<br />
database using ASP.NET</th>
</tr>
 
<tr>
<td align="Center">
List of Tables</td>
</tr>
 
<tr>
<td align="Center">
<asp:ListBox ID="ListBox1" Rows="10" runat="Server" DataTextField="table_name" DataValueField="table_name">
</asp:ListBox>
</td>
</tr>
 
<tr>
<td align="Center">
<span onclick="return confirm('Are you sure to backup selected table?')">
<asp:Button ID="btnBackup" runat="Server" Text="Backup" OnClick="BackUpNow" />
</span>
<span onclick="return confirm('Are you sure to restore selected table?')">
<asp:Button ID="btnRestore" runat="Server" Text="Restore" OnClick="RestoreNow" />
</span>
</td>
</tr>
</table>
 
<asp:Label ID="lblMessage" runat="Server" EnableViewState="False" ForeColor="Blue"></asp:Label>
 
</div>
</form>

Step 2 - Populate the database table in the ListBox control

In this step, we will write method that will populate all database tables in the ListBox control that will let the end user select the table to perform Backup or Restore. My code looks like below:

string connStr = ConfigurationManager.AppSettings["DatabaseToBackup"].ToString();
 
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDatabaseTables();
}
}
/// <summary>
/// Populate database tables first
/// </summary>
private void PopulateDatabaseTables()
{
string tableName = string.Empty;
string sql = "SELECT *, name AS table_name " +
" FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (DataTable table = new DataTable())
{
conn.Open();
using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
{
dAd.Fill(table);
}
ListBox1.DataSource = table;
ListBox1.DataBind();
}
}
}

You can notice in the above code snippet, I am calling a method named PopulateDatabaseTables() in the Not IsPostBack condition under Page_Load event that will populate all the tables of the database in the ListBox control. Notice the select statement that has been stored into sql variable. This select statement varies between databases to databases. To list all the tables in SQL Server database, you need to use above select statement. To list all the tables of the MySQL database, you need to write "show tables" in place of above select statement. Of course, you need to change the provider as well to connect to the MySQL database.

Get solutions of .NET problems with video explanations, .pdf and source code in .NET How to's.

Step 3 - Code for taking backup of the selected table

In this step, we shall write code to take backup of the selected table when Backup button will be clicked. You can see that in OnClick event of the Backup button, I have specified BackupNow method. Let us see the code for this method.

/// <summary>
/// Backup the selected table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BackUpNow(object sender, EventArgs e)
{
string tableName = ListBox1.SelectedValue;
using (DataSet dSetBackup = new DataSet())
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
{
dAd.Fill(dSetBackup, tableName);
}
}
dSetBackup.WriteXml(Server.MapPath("~/backup/" + tableName + ".xml"));
lblMessage.Text = "Backup for table <b>" + tableName + "</b> successful!";
}
}
 

In the above code snippet, I have got the selected table name form ListBox in the tableName variable. Selected all records from that table and filled into a DataSet named dSetBackup. Later on I used WriteXml method of DataSet to write the contents of the selected table in an xml file. The name of the xml file will be the table name in my case and will be placed in the backup folder. Then I have written a success message. This way your end user will be able to take backup of all the tables in the database.

Step 4 - Code for restoring selected table

In this step, we shall see how to restore a selected table into the database once the Restore button will be clicked. Notice the Restore button where I have specified RestoreNow method in the OnClick event. The code for this method looks like below:

/// <summary>
/// Restore the selected table 
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void RestoreNow(object sender, EventArgs e)
{
string restoreConnStr = ConfigurationManager.AppSettings["RestoreConnStr"].ToString();
string tableName = ListBox1.SelectedValue;
using (SqlConnection conn = new SqlConnection(restoreConnStr))
{
using (DataSet dSetBackup = new DataSet())
{
// get the schema of the selected table from the database to restore
using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
{
     // Following line will get the schema of the selected table in the dSetBackup DataSet
     dAd.Fill(dSetBackup, tableName);
 
// get the data for selected table from backed up XML file
using (DataSet dSet = new DataSet())
{
dSet.ReadXml(Server.MapPath("~/backup/" + tableName + ".xml"));
// Loop through all rows of the selected table and add into dSetBackup DataSet
foreach (DataRow row in dSet.Tables[0].Rows)
{
dSetBackup.Tables[0].NewRow();
dSetBackup.Tables[0].Rows.Add(row.ItemArray);
}
 
// Create a command builder to update dSetBackup DataSet
SqlCommandBuilder cmd = new SqlCommandBuilder(dAd);
 
// Following update command will push all added rows of dSetBackup DataSet into the database
dAd.Update(dSetBackup, tableName); // We are done !!!
}
}
lblMessage.Text = "Restore of table <b>" + tableName + "</b> successful!";
}
}
}

Please note that in order to above code snippet work, you must have the schema in place into the Restore database. This schema must match the original database schema from which you had taken backup otherwise your restore will not work.

In the above code snippet, I am first getting the connection string of the database where we need to restore the data. Then I am getting the schema of the selected table into the dSetBackup DataSet using dAd (SqlDataAdapter), as there will not be any row into the restore database, so it will only give the schema of the table. This will help us to push the backed up data into this DataSet. In the next line, I have read the backed up xml of the table using ReadXml method of the DataSet that will get all the backed up data for that table into the dSet DataSet. In next line, I am looping through every rows of the dSet DataSet table and adding them into our dSetBackup DataSet. Next, I have created a SqlCommandBuilder object for the SqlDataAdapter. This will internally build insert, update, delete statement for the dSetBackup DataTable. In the following line, I have used dAd.Update (SqlDataAdapter update method) to push the data into the restore database. At last I am writing the success message. Simple! Isn't it?

Conclusion

In this article, we learnt how to take backup and restore SQL Server datbase tables in very simple 4 steps. As said earlier, the same approach can be followed for any other database that is supported by .NET to backup and restore database tables. Hope you enjoyed this article, please subscribe for RSS feed to get subsequent article alert in your email directly.

Feel free to download source code of this article from top and play with it. 

Thanks and happy coding !

Updated on 22-Mar-2010: This solution has been further extended (to make it a complete ASP.Net Backup and Restore Database Tool)  by Myke Black, refer to this link.