Home: How to create backup of database in C# ASP.NET 2 another way

Stored procedure

USE [ESDM_NIELIT_NEW]

/****** Object: StoredProcedure [dbo].[USP_DB_BACKUP1] Script Date: 20-06-2022 16:03:46 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[USP_DB_BACKUP1]

begin

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory

SET @path = 'D:\ESDM_SW_BACKUP\'

-- specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','ESDM_Demo','ESDM_NIELIT_NEW1','ESDMDemo','star') -- exclude these databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

end

----------------------aspx.cs code

using System;

using System.Collections.Generic;

using Luminious.Connection;

using Luminious.DataAcessLayer;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Threading;

using Ionic.Zip;

using System.IO;

using System.Xml;

public partial class View_Admin_Master_BackupMaster : System.Web.UI.Page

{

SqlConnection con = new SqlConnection();

SqlCommand sqlcmd = new SqlCommand();

SqlDataAdapter da = new SqlDataAdapter();

DataTable dt = new DataTable();

SqlConnection sqlcon = new SqlConnection();

protected void Page_Init(object sender, EventArgs e)

{

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.Cache.SetExpires(DateTime.Now.AddSeconds(-1));

Response.Cache.SetNoStore();

Browser.ClearBrowserData();

Page.ViewStateUserKey = Session.SessionID;

Response.Cache.SetValidUntilExpires(true);

}

string rno;

protected void Page_Load(object sender, EventArgs e)

{

//if (!Page.IsPostBack)

//{

// return;

//}

if (this.Page.IsPostBack)

return;

this.CreateRandomHashing();

this.CrossSiteRequestForgery();

this.DisplayBackup();

this.checkdrivedetails();

}

private void checkdrivedetails()

{

try

{

DataTable dataTable = new DataTable();

DataColumn column1 = new DataColumn("fi", typeof(string));

DataColumn column2 = new DataColumn("si", typeof(string));

dataTable.Columns.Add(column1);

dataTable.Columns.Add(column2);

List<string> stringList = new List<string>();

foreach (FileInfo file in new DirectoryInfo(this.MapPath("~/dbstore")).GetFiles())

dataTable.Rows.Add((object)file.FullName, (object)Path.GetFileName(file.FullName));

dataTable.AcceptChanges();

this.grd_test.DataSource = (object)dataTable;

this.grd_test.DataBind();

}

catch (Exception ex)

{

ExceptionHandler.WriteException(ex.Message);

}

//private void checkdrivedetails()

//{

// try

// {

// DataTable dt = new DataTable();

// DataColumn dc = new DataColumn("fi", typeof(string));

// DataColumn dc2 = new DataColumn("si", typeof(string));

// dt.Columns.Add(dc);

// dt.Columns.Add(dc2);

// List<string> fil = new List<string>();

// DirectoryInfo info = new DirectoryInfo(MapPath("~/dbstore"));

// System.IO.FileInfo[] fileNames = info.GetFiles();

// foreach (System.IO.FileInfo fi in fileNames)

// {

// // Response.Write(fi.FullName);

// // fil.Add(fi.FullName);

// dt.Rows.Add(fi.FullName, Path.GetFileName(fi.FullName));

// }

// dt.AcceptChanges();

// grd_test.DataSource = dt;

// grd_test.DataBind();

// }

// catch (Exception ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

//}

protected void btnBackup_Click(object sender, EventArgs e)

{

#region begin

//if (ViewState["CSRF"] != null && Convert.ToString(ViewState["CSRF"]) == Convert.ToString(Session["randomno"]))

//{

// SqlConnection cc = new SqlConnection(Luminious.Connection.Configuration.ConnectionString);

// string DbName = cc.Database;

// try

// {

// object ob = null;

// ob = Luminious.DataAcessLayer.SqlHelper.ExecuteScalar(Luminious.Connection.Configuration.ConnectionString, CommandType.StoredProcedure, "CheckDbExist", new SqlParameter[] { new SqlParameter("@dbname", DbName) });

// if (ob != null)

// {

// try

// {

// int result = 0;

// string filpath = System.Configuration.ConfigurationManager.AppSettings["backup"].ToString();

// if (!System.IO.Directory.Exists(filpath))

// {

// Directory.CreateDirectory(filpath);

// ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Back up folder created sucessfully');", true);

// }

// else

// {

// result = Luminious.DataAcessLayer.SqlHelper.ExecuteNonQuery(Luminious.Connection.Configuration.ConnectionString,

// CommandType.StoredProcedure, "USP_DB_BACKUP",

// new SqlParameter[] { new SqlParameter("@DB_NM", DbName != "" ? DbName : string.Empty), new SqlParameter("@PATH", filpath) });

// if (result > 0)

// {

// ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Backup Created Sucessfully');", true);

// // DisplayBackup();

// checkdrivedetails();

// }

// else

// {

// }

// catch (IOException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (SqlException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// finally

// {

// }

// else

// {

// }

// catch (SqlException ex)

// {

// ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('" + ex.Message + "');", true);

// }

// catch (Exception ex)

// {

// ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('" + ex.Message + "');", true);

// }

// finally

// {

// CrossSiteRequestForgery();

// CreateRandomHashing();

// }

//}

//else

//{

//}

#endregion

try

{

SqlConnection cc = new SqlConnection(Luminious.Connection.Configuration.ConnectionString);

using (cc)

{

using (SqlCommand cmd = new SqlCommand("USP_DB_BACKUP1", cc))

{

cc.Open();

cmd.CommandTimeout = 0;

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

ExceptionHandler.WriteException(ex.Message);

}

private void DisplayBackup()

{

try

{

DataTable dataTable1 = new DataTable();

dataTable1 = (DataTable)null;

DataTable dataTable2 = SqlHelper.ExecuteDataTable(Configuration.ConnectionString, CommandType.Text, "select top 10 * from MST_DB_BACKUP order by BACKUP_DT desc");

if (dataTable2 != null && dataTable2.Rows.Count > 0)

{

this.grdDbBackShow.DataSource = (object)dataTable2;

this.grdDbBackShow.DataBind();

}

else

{

this.grdDbBackShow.DataSource = (object)null;

this.grdDbBackShow.DataBind();

}

catch (SqlException ex)

{

ExceptionHandler.WriteException(ex.Message);

}

catch (Exception ex)

{

ExceptionHandler.WriteException(ex.Message);

}

//private void DisplayBackup()

//{

// try

// {

// DataTable dt = new DataTable();

// dt = null;

// dt = Luminious.DataAcessLayer.SqlHelper.ExecuteDataTable(Luminious.Connection.Configuration.ConnectionString, CommandType.Text, "select top 10 * from MST_DB_BACKUP order by BACKUP_DT desc");

// if (dt != null && dt.Rows.Count > 0)

// {

// grdDbBackShow.DataSource = dt;

// grdDbBackShow.DataBind();

// }

// else

// {

// grdDbBackShow.DataSource = null;

// grdDbBackShow.DataBind();

// }

// catch (SqlException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (Exception ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

//}

protected void grdDbBackShow_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName.Equals("download"))

{

// string[] arg = new string[2];

//arg = e.CommandArgument.ToString().Split(';');

////var fil = arg[1] + arg[0];

//var fil = MapPath(System.IO.Path.GetFileName(arg[1]));

// try

// {

// //The Content-Disposition header tells the browser to display the Save As dialog box for this response

// //The Content-Type header tells the browser the data being returned is a ZIP file

// using (var zip = new ZipFile())

// {

// if (!string.IsNullOrEmpty(""))

// {

// zip.Password = "";

// zip.Encryption = EncryptionAlgorithm.PkzipWeak;

// }

// var readMeContent = string.Format("This ZIP file was created by ESDM portal at {0} and contains the following files:{1}{1}", DateTime.Now, Environment.NewLine);

// if (fil.Length > 0)

// {

// zip.AddFile(fil.ToString(), "NIELITBACKUP");

// readMeContent += string.Format("\t* {0} - {1}{2}", "Backu Database", Convert.ToString(fil[0]), Environment.NewLine);

// System.Threading.Thread.Sleep(500);

// // var downloadFileName = string.Format("Backup" + "-{0}.zip", DateTime.Now.ToString("yyyy-MM-dd-HH_mm_ss"));

// var downloadFileName = string.Format(arg[0]);

// Response.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName);

// Response.ContentType = "application/zip";

// zip.Password = null;

// zip.AddEntry("README.txt", readMeContent);

// zip.Save(Response.OutputStream);

// zip.Dispose();

// }

// catch (FileNotFoundException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (System.Threading.ThreadAbortException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (OperationAbortedException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (Exception ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// finally

// {

// GC.Collect();

// GC.SuppressFinalize(this);

// }

}

protected void CrossSiteRequestForgery()

{

//Code start for Cross Site Request Forgery

Random randomobj = new Random();

Session["randomno"] = randomobj.Next();

ViewState["CSRF"] = Session["randomno"].ToString();

//End Code

}

private void CreateRandomHashing()

{

Random rd = new Random();

Session["rnumb"] = rd.Next();

rno = Convert.ToString(Session["rnumb"]);

btnBackup.Attributes.Add("onClick", "return chkfrm('" + rno + "');");

}

protected void grdDbBackShow_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grdDbBackShow.PageIndex = e.NewPageIndex;

DisplayBackup();

}

protected void grd_test_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName.Equals("del"))

{

try

{

File.Delete(e.CommandArgument.ToString());

checkdrivedetails();

}

catch (Exception ex)

{

}

if (e.CommandName == "cmd")

{

string filename = e.CommandArgument.ToString();

if (filename != "")

{

string path = filename;

byte[] bts = System.IO.File.ReadAllBytes(path);

Response.Clear();

Response.ClearHeaders();

Response.AddHeader("Content-Type", "Application/octet-stream");

Response.AddHeader("Content-Length", bts.Length.ToString());

Response.AddHeader("Content-Disposition", "attachment; filename=" + filename+DateTime.Now.ToLongDateString()+".bak");

Response.BinaryWrite(bts);

Response.Flush();

Response.End();

}

protected void btn_run_Click(object sender, EventArgs e)

{

bool flag = false;

string[] files = Directory.GetFiles(this.MapPath("~/dbstore/"));

try

{

foreach (string str in files)

{

if (Path.GetExtension(str).ToUpper().Equals(".bak") || Path.GetExtension(str).ToUpper().Equals(".BAK"))

this.recordinsert(Path.GetFileName(str), DateTime.Now, str);

}

ScriptManager.RegisterStartupScript((Page)this, this.GetType(), "message", "alert('Mapping Completed');", true);

}

catch (DirectoryNotFoundException ex)

{

ExceptionHandler.WriteException(ex.Message);

}

catch (ArgumentException ex)

{

ExceptionHandler.WriteException(ex.Message);

}

catch (IOException ex)

{

ExceptionHandler.WriteException(ex.Message);

}

catch (Exception ex)

{

ExceptionHandler.WriteException(ex.Message);

}

finally

{

if (flag)

ScriptManager.RegisterStartupScript((Page)this, this.GetType(), "message", "alert('Duplicate Record Deleted Sucessfully');", true);

GC.Collect();

GC.SuppressFinalize((object)this);

}

//protected void btn_run_Click(object sender, EventArgs e)

//{

// // System.Diagnostics.Process.Start(MapPath(@"~/test.bat"));

// bool b = false;

// string[] filePaths = Directory.GetFiles(MapPath("~/dbstore/"));

// try

// {

// foreach (string s in filePaths)

// {

// if (Path.GetExtension(s).ToUpper().Equals(".bak") || Path.GetExtension(s).ToUpper().Equals(".BAK"))

// {

// recordinsert(Path.GetFileName(s), DateTime.Now, s);

// }

// ScriptManager.RegisterStartupScript(this, this.GetType(), "message", "alert('Mapping Completed');", true);

// }

// catch (DirectoryNotFoundException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (ArgumentException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (IOException ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// catch (Exception ex)

// {

// ExceptionHandler.WriteException(ex.Message);

// }

// finally

// {

// if (b)

// {

// ScriptManager.RegisterStartupScript(this, this.GetType(), "message", "alert('Duplicate Record Deleted Sucessfully');", true);

// }

// GC.Collect();

// GC.SuppressFinalize(this);

// }

//}

public void recordinsert(string backupname, DateTime backuptime, string pathe)

{

try

{

string Q = @"insert into MST_DB_BACKUP values(@backupname,@backuptime,@bpath)";

Luminious.DataAcessLayer.SqlHelper.ExecuteNonQuery(Luminious.Connection.Configuration.ConnectionString, CommandType.Text, Q,

new SqlParameter[] { new SqlParameter("@backupname", backupname), new SqlParameter("@backuptime", System.DateTime.Now), new SqlParameter("@bpath", pathe) });

}

catch (Exception ex)

{

}

finally

{

}

protected void grd_test_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

}

////Mentioned Connection string make sure that user id and password sufficient previlages

// sqlcon.ConnectionString = @"Server=RAVI-PC\SQLEXPRESS;database=" + dbname + ";uid=ravindran;pwd=srirangam;";

// //Enter destination directory where backup file stored

// string destdir = "D:\\backupdb";

// //Check that directory already there otherwise create

// if (!System.IO.Directory.Exists(destdir))

// {

// System.IO.Directory.CreateDirectory("D:\\backupdb");

// }

// try

// {

// //Open connection

// sqlcon.Open();

// //query to take backup database

// sqlcmd = new SqlCommand("backup database test to disk='" + destdir + "\\" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".Bak'", sqlcon);

// sqlcmd.ExecuteNonQuery();

// //Close connection

// sqlcon.Close();

// Response.Write("Backup database successfully");

// }

// catch (Exception ex)

// {

// Response.Write("Error During backup database!");

// }

---------------------------------

aspx code

<legend>Backup Master</legend>

<tr>

<asp:Button ID="btnBackup" runat="server" Visible="false" CssClass="btn btn-primary" Text="DataBase Backup" OnClick="btnBackup_Click" />

</td>

<td>

<asp:Button ID="btn_run" Text="BatchFile" runat="server" onclick="btn_run_Click" />

</td>

</tr>

</table>

<asp:GridView ID="grdDbBackShow" AllowPaging="true" PageSize="10" Visible="false" runat="server" CssClass="table table-responsive Grid"

AutoGenerateColumns="false" onrowcommand="grdDbBackShow_RowCommand"

onpageindexchanging="grdDbBackShow_PageIndexChanging">

<asp:TemplateField HeaderText="Sl.No">

<%#Container.DataItemIndex +1 %>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField HeaderText="Backup Name" DataField="BACKUP_NM" NullDisplayText="N/A"/>

<asp:BoundField HeaderText="Date" DataField="BACKUP_DT" NullDisplayText="N/A"/>

<asp:BoundField HeaderText="Backup Path" DataField="BACKUP_PATH" NullDisplayText="N/A"/>

<asp:TemplateField HeaderText="Download">

<asp:LinkButton ID="linkDownload" runat="server" CommandName="download" CommandArgument='<%#Eval("BACKUP_NM") + ";" +Eval("BACKUP_PATH")%>' Text="Download"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:GridView ID="grd_test" Visible="true" runat="server" AutoGenerateColumns="false" CssClass="table table-bordered table-responsive" AllowPaging="true" PageSize="100"

onrowcommand="grd_test_RowCommand">

<asp:TemplateField>

<asp:HyperLink ID="hyp_down" runat="server" Text='<%#Eval("si") %>'></asp:HyperLink>

<%-- <asp:LinkButton ID="lnkDownload" runat="server" CommandArgument='<%# Eval("fi") %>' CommandName="cmd">Download Backup</asp:LinkButton>

--%>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<%-- <asp:HyperLink ID="hyp_down" runat="server" Text='<%#Eval("si") %>'></asp:HyperLink>--%>

<asp:LinkButton ID="lnkDownload" runat="server" CommandArgument='<%# Eval("fi") %>' CommandName="cmd">Download Backup</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</fieldset>

Home

Monday, June 20, 2022

How to create backup of database in C# ASP.NET 2 another way

No comments:

Post a Comment