ASP.Net Backup and Restore Database Tool

Latest Scripts Added

jQuery image swap on mouseover (jQuery)
04 January 2012
This simple bit of code allows you to quickly create image rollovers. The code was modified from a version by Sam Colletts on http://webdevel.blogspot.com/2008/04/rollover-images-with-jquery.html ...

replace XML characters in a string using ASP.Net (ASP.Net)
12 December 2011
Sometimes you need to insert a string into an xml document. Before doing this, you need to ensure that your string does not contain any special xml characters. It is possible to use a CDATA section ...

How to remove title tag in ASP.Net (ASP.Net)
14 October 2011
This is not really a script, but a little trick that you can use to remove the title tag in ASP.net. In ASP.net, when the head tag is rendered, the title tag is automatically added. One of the real ...

ASP sort numerical array (ASP)
12 September 2011
This code is similar to the sort array script which sorts an array alphabetical, however it also sorts arrays based on numberical ordering, for example: dim numbers(4) numbers(0) = ...

Force file download in ASP.Net / c# (ASP.Net)
02 September 2011
this script allows you to force browsers to display a file save dialogue for any file type. It contains a string of allowed file types so users cannot change the querystring to downoad your ...

Script version:	2
Language:	ASP.Net
Rating:	6.4 (11 votes cast)
Category:	No Category
Description:	This script is a slight extension to Sheo Narayan's excellent <b>Backup and Restore Database in ASP.Net</b>(http://www.dotnetfunda.com/articles/article489-backup-and-restore-database-in-aspnet-.aspx) code. In the code below, I've added some tweaks to the xml file to include the database schema, and also an xml file download and upload facility so you can save your backups and store them somewhere else, and then you can upload any previously downloaded backup in case the most recent backup set has corrupted or missing data. This code is written in c# and targets SQL server, but in theory it should work fine when targeting mySQL or any other database type (I haven't tested it with other database types though)
Instructions:	Create two files, one called dbbackup.aspx and dbbackup.aspx.cs. Copy the code from the script above into each of the files. Make sure you enter your connection string in the top of the .cs file (replace the bit in the code which says [ENTER YOUR CONNECTION STRING HERE]. You can also change the backupfolder to target another folder. Your backup folder must exist within your web directory so that you can download the files once you have created them (and you must make sure that the server has read/write permission in this folder). For extra security, you can create a secret folder to store them in, or change the filenames according to a secret key if desired. Many thanks go to Sheo Narayan, without whom this code would never have been written. Probably.
Code:
In the .aspx file, copy and paste the following: <% @ Page Language="C#" AutoEventWireup="true" CodeFile="dbbackup.aspx.cs" Inherits="dbbackup" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Database backup and restore tool</title> <style> td,p{font:normal 11px arial;} </style> </head> <body> <form id="form1" runat="server"> <asp:Label ID="lblMessage" runat="Server" EnableViewState="False" ForeColor="Red"></asp:Label> <table style="border:1px solid #cccccc;border-collapse:collapse"> <tr><td style="vertical-align:top;border-right:1px solid #cccccc;padding-left:15px;padding-top:10px"><b>Database Tables</b></td> <td style="vertical-align:top;border-right:1px solid #cccccc;padding-left:15px;padding-top:10px"><b>Download Backups</b></td> <td style="vertical-align:top;padding-left:15px;padding-top:10px"><b>Upload Backup</b></td></tr> <tr><td style="vertical-align:top;border-right:1px solid #cccccc;padding:15px;"> <table> <tr><td><asp:ListBox ID="ListBox1" runat="Server" DataTextField="table_name" DataValueField="table_name" style="width:200px;" rows="10"></asp:ListBox></td></tr> <tr><td><span onclick="return confirm('Are you sure to backup selected table?')"><asp:Button ID="btnBackup" runat="Server" Text="Backup Table" OnClick="BackUpNow" CssClass="smallbutton2" /></span> <span onclick="return confirm('Are you sure to restore selected table?')"><asp:Button ID="btnRestore" runat="Server" Text="Restore Table" OnClick="RestoreNow" CssClass="smallbutton2" /></span></td></tr> </table> </td> <td style="vertical-align:top;border-right:1px solid #cccccc;padding:15px;"><asp:Literal ID="backupList" runat="server"></asp:Literal></td> <td style="vertical-align:top;padding:15px;"><asp:literal ID="uploadMessage" runat="server"></asp:literal> <br /> <asp:FileUpload ID="FileUpload1" runat="server" /><br /> <asp:Button ID="btnUpload" runat="server" onclick="uploadBackup" Text="Upload Backup File" /> </td> </tr> </table> </form> </body> </html> And in the code behind page copy and paste the following: using System; using System.IO; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Data; using System.Data.SqlClient; public partial class dbbackup : System.Web.UI.Page { string connectionstring = "[ENTER YOUR CONNECTION STRING HERE]"; string backupfolder = "/mybackups"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack){ PopulateDatabaseTables(); } listBackups(); } 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(connectionstring)) { using (DataTable table = new DataTable()) { using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn)) {dAd.Fill(table);} ListBox1.DataSource = table; ListBox1.DataBind(); } } } protected void BackUpNow(object sender, EventArgs e){ string tableName = ListBox1.SelectedValue; using (DataSet dSetBackup = new DataSet()){ using (SqlConnection conn = new SqlConnection(connectionstring)){ using (SqlDataAdapter dAd = new SqlDataAdapter("select from " + tableName, conn)){ dAd.Fill(dSetBackup, tableName); } } if (!Directory.Exists(Server.MapPath(backupfolder))){Directory.CreateDirectory(Server.MapPath(backupfolder));} dSetBackup.WriteXml(Server.MapPath(backupfolder + "/" + tableName + ".xml"),XmlWriteMode.WriteSchema); lblMessage.Text = "Backup for table <b>" + tableName + "</b> successful!<br />"; } listBackups(); } protected void RestoreNow(object sender, EventArgs e) { string tableName = ListBox1.SelectedValue; if (File.Exists(Server.MapPath(backupfolder + "/" + tableName + ".xml"))) { string xmlFile = Server.MapPath(backupfolder + "/" + tableName + ".xml"); DataSet ds = new DataSet(); ds.ReadXml(xmlFile); DataTable dt = new DataTable(); dt = ds.Tables[0]; SqlConnection conn = new SqlConnection(connectionstring); conn.Open(); SqlCommand comm = new SqlCommand("truncate table " + tableName, conn); comm.ExecuteNonQuery(); conn.Close(); SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionstring, 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 />"; } } protected void listBackups() { string fPath = Server.MapPath(backupfolder); if (Directory.Exists(fPath)) { string filelinks = ""; DirectoryInfo dir = new DirectoryInfo(fPath); FileInfo[] files = dir.GetFiles("*.xml"); foreach (FileInfo file in files) { filelinks += "<tr><td style=\"border-top:1px solid #cccccc;padding:5px;\"><a href=\"" + backupfolder + "/" + file.Name + "\">" + file.Name + "</a></td><td style=\"padding:5px;border-left:1px solid #cccccc;border-top:1px solid #cccccc;\">" + file.LastWriteTime + "</td></tr>"; } if (filelinks == "") { backupList.Text = "No backups have been made yet."; } else { backupList.Text = "Click to download a backupfile below <br>(right click and select 'save target as')<br /><br /><table style=\"border:1px solid #cccccc\"><tr><td style=\"padding:5px;\"><b>Backup File</b></td><td style=\"padding:5px;border-left:1px solid #cccccc;border-top:1px solid #cccccc;\"><b>Date Crated</b></td></tr>" + filelinks + "</table>"; } } else { backupList.Text = "No backups have been made yet."; } } protected void uploadBackup(object sender, EventArgs e) { HttpPostedFile myFile = FileUpload1.PostedFile; if (myFile != null) { string backupPath = Server.MapPath(backupfolder) + "\\" + Path.GetFileName(myFile.FileName); myFile.SaveAs(backupPath); lblMessage.Text = "Backup file " + Path.GetFileName(myFile.FileName) + " uploaded successfully"; listBackups(); } else { lblMessage.Text = "No File was uploaded"; } } }

ASP.Net Backup and Restore Database Tool

Comments

Related Scripts:

Tags

Latest Scripts Added