Backup and Restore database in ASP.NET

SheoNarayan
Posted by in ASP.NET category on for Intermediate level | Views : 65450 red flag
Rating: 5 out of 5  
 2 vote(s)

This article describes an approach to take backup and restore any database that is supported by .NET. In this article, I have taken SQL Server as an example however similar approach can be followed to backup and restore MySql, Oracle and any other databases.


 Download source code for 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.

Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Poster on: 1/19/2010
Nice article Sir.

Thank you and keep it up!


Posted by: Mykeblack on: 3/18/2011 | Points: 25
Amazing!
I've used this code and added a few extra bits - ie list all the backed up xml files on the page to allow you to download them and added a file upload form so that you can upload any xml backups (as long as you don't change the table structure between backup and restore).
I also changed:
using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
to:
using (SqlDataAdapter dAd = new SqlDataAdapter("delete from ' + tablename + "select * from " + tableName, conn))
to prevent duplicate entries when you restore the database.

My main problem though is this: nearly all my tables have an identity field, and when I restore the data, the identity field ID changes. Is there a way to make it so that the identity field remains the same when you restore the database?

thanks,
myke black
Posted by: SheoNarayan on: 3/18/2011 | Points: 25
Thanks Myke,

Did you try truncate table instead of delete. Delete doesn't reset the identity (auto increment) field. Truncate does.

Thanks
Posted by: Mykeblack on: 3/21/2011 | Points: 25
Thanks, I just tried that, so the line now says:

using (SqlDataAdapter dAd = new SqlDataAdapter("truncate table " + tableName + ";select * from " + tableName, conn))

that worked better, but it creates a new autonumber field, so the IDs are created anew on restore. Thats fine if the table you backed up starts at ID1 and has no missing IDs, but if you have rows with id 1,2,4,5 (if ID 3 was previously deleted) then all rows after row 2 have the wrong id when restored.

I also tried turning IDENTITY_INSERT ON in the table like this:
using (SqlDataAdapter dAd = new SqlDataAdapter("truncate table " + tableName + ";select * from " + tableName + ";SET IDENTITY_INSERT " + tableName + " ON", conn)) then turn it back off later in the code, but still the ID fields are not correct.

If you turn off the identity field using SQL manager, the the restore works ok so the problem is with the XML data, but in the destination database schema.

The way I thought of is to:

1. delete the data in the destination database
2. drop the identity field from the destination table
3. add a replacement int field (without identity) with the same name as the ID field
4. copy the data into the table from the xml file
5. alter the table to set the new int field into an identity field.

Here's my code below:

protected void RestoreNow(object sender, EventArgs e) {
string connstring = "";
string tableName = ListBox1.SelectedValue;

if (File.Exists(Server.MapPath("/backups/" + tableName + ".xml"))) {
using (SqlConnection conn = new SqlConnection(connstring)) {

using (DataSet dSetBackup = new DataSet()) {

// get the schema of the selected table from the database to restore - modified to remove all records before we read the data in
using (SqlDataAdapter dAd = new SqlDataAdapter("delete from " + tableName + "; select * from " + tableName, conn)) {

// Following line will get the schema of the selected table in the dSetBackup DataSet
dAd.Fill(dSetBackup, tableName);

conn.Open();

// find out if the table has an ID field - note for non SQL databases this query will need to be modified...
string idField = "";
SqlCommand comm = new SqlCommand("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' AND TABLE_NAME='" + tableName + "' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 order by TABLE_NAME", conn);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read()) { idField = reader["column_name"].ToString(); }
reader.Close();

if (idField != "") {
// drop current ID field and add new int field without autonumber to allow insertion of ID values
comm.CommandText = "Alter table " + tableName + " DROP column " + idField + "; Alter table " + tableName + " ADD " + idField + " int not null default 1";
comm.ExecuteNonQuery();
}

// get the data for selected table from backed up XML file
using (DataSet dSet = new DataSet()) {

dSetBackup.ReadXml(Server.MapPath("/backups/" + tableName + ".xml"));
// 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);
}


if (idField != "") {
// change copy of id field back into autonumber
comm.CommandText = "ALTER TABLE " + tableName + " ALTER COLUMN " + idField + " IDENTITY(1,1)";
comm.ExecuteNonQuery();
}
conn.Close();

lblMessage.Text = "Restore of table <b>" + tableName + "</b> successful!<br />";
}
}
}
} else {
lblMessage.Text = "Table <b>" + tableName + "</b> has not been backed up yet, so cannot be restored.<br />";
}
}

The only problem now is that the ID field is not reset to auto incremement. The code is a little bit messy too, so there might be a better way of doing this possibly. What do you think? Do you think it would be safer to restore the data into a new database called tableName + "_restore" then if there are no errors, drop the original table and rename the restored table? Would still need to solve the identity field problem though.

regards,

myke.
Posted by: Mykeblack on: 3/21/2011 | Points: 25
Getting a bit closer now. I've replaced the messy code by using the SQLbuklcopy class which automatically adds the "indentity_insert on/off" commands. Code is as follows:

protected void RestoreNow(object sender, EventArgs e) {
string tableName = ListBox1.SelectedValue;
string xmlFile = Server.MapPath("/backups/" + tableName + ".xml");
string connstring = "[INSERT YOUR CONNECTION STRING HERE]";

if (File.Exists(xmlFile)) {

DataSet ds = new DataSet();
ds.ReadXml(xmlFile);
DataTable dt = new DataTable();
dt = ds.Tables[0];

SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlCommand comm = new SqlCommand("truncate table " + tableName,conn);
comm.ExecuteNonQuery();
conn.Close();

SqlBulkCopy bulkCopy = new SqlBulkCopy(connstring, SqlBulkCopyOptions.KeepIdentity);
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dt);

lblMessage.Text += "Restore of table <b>" + tableName + "</b> successful!<br />";
} else {
lblMessage.Text += "Table <b>" + tableName + "</b> has not been backed up yet, so cannot be restored.<br />";
}
}



However it's still not inserting the correct ID into the id field.

Grrrrrr

Posted by: Mykeblack on: 3/21/2011 | Points: 25
Figured it out in the end.

What I did was add the database schema into the xml file by adding the XmlWriteMode option into the WriteXml call like this:

dSetBackup.WriteXml(Server.MapPath(backupfolder + "/" + tableName + ".xml"),XmlWriteMode.WriteSchema);


then you can use the simple SqlBulkCopy to restore the database and it will preserve the Identity field.

See the full code of my implimentation here: http://mykeblack.com/scripts/asp.net/database/asp-net-backup-restore-database.

Posted by: Saqib on: 9/14/2011 | Points: 25
Hey,
I am a junior programmer working on my 1st project. I would like to know if the above code would work if i would want to backup a sql server database on server from a local system. If not, how would I be able to complete such a task. And instead of writing the backup in an XML file, how would I write the backup on the servers disk drive in a .bak file. Hope to hear from you soon.
Posted by: Tejas3646 on: 12/8/2011 | Points: 25
Hey...
How can we create table automatically by XML file so we don't have to worry about table structure and creation.

Please reply soon...

Posted by: Krunal1654 on: 8/7/2013 | Points: 25
i use this code but error at run time Object reference not set to an instance of an object.

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 13: public partial class backup : System.Web.UI.Page
Line 14: {
Line 15: string connStr = ConfigurationManager.AppSettings["DatabaseToBackup"].ToString();
Line 16:
Line 17: protected void Page_Load(object sender, EventArgs e)

Login to post response

Comment using Facebook(Author doesn't get notification)