Create table
USE [ESDM_NIELIT_NEW]
GO
/****** Object: Table [dbo].[MST_DB_BACKUP] Script Date: 20-06-2022 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_DB_BACKUP](
[BAK_ID] [int] IDENTITY(1,1) NOT NULL,
[BACKUP_NM] [varchar](300) NULL,
[BACKUP_DT] [datetime] NULL,
[BACKUP_PATH] [varchar](300) NULL,
CONSTRAINT [PK_MST_DB_BACKUP] PRIMARY KEY CLUSTERED
(
[BAK_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Stored Procedure
USE [ESDM_NIELIT_NEW]
GO
/****** Object: StoredProcedure [dbo].[USP_DB_BACKUP] Script Date: 20-06-2022 15:42:09 ******/
DROP PROCEDURE [dbo].[USP_DB_BACKUP]
GO
/****** Object: StoredProcedure [dbo].[USP_DB_BACKUP] Script Date: 20-06-2022 15:42:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_DB_BACKUP]
(
@DB_NM VARCHAR(500)='ESDM_NIELIT_NEW'
,@PATH VARCHAR(max)='D:\ESDM_NIELIT_NEW\'
)
AS
BEGIN
BEGIN TRY
--BEGIN TRAN
DECLARE @DATE DATETIME
SELECT @DATE=GETDATE()
DECLARE @DATE1 VARCHAR(20)=''
SELECT @DATE1=REPLACE(CONVERT(VARCHAR(20),@DATE,105),'-','_')+'_'+REPLACE(CONVERT(VARCHAR(20),@DATE,108),':','_')
--SELECT @DATE1
DECLARE @BACK_UP_NM VARCHAR(300)=''
SET @BACK_UP_NM=@DB_NM+'_'+@DATE1+'.bak'
DECLARE @SQL_QUERY VARCHAR(300)=''
SELECT @SQL_QUERY='BACKUP DATABASE '+@DB_NM+' TO DISK='''+@PATH+''+@DB_NM+'_'+@DATE1+'.bak'''
--PRINT @SQL_QUERY;
EXEC (@SQL_QUERY);
INSERT INTO MST_DB_BACKUP(BACKUP_NM,BACKUP_DT,BACKUP_PATH) VALUES(@BACK_UP_NM,@DATE,@PATH);
--COMMIT;
END TRY
BEGIN CATCH
-- ROLLBACK;
PRINT ERROR_MESSAGE();
END CATCH;
END;
--GO
GO
------------------------
ASPX.CS CODE
using System;
using System.Collections.Generic;
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)
{
CreateRandomHashing();
CrossSiteRequestForgery(); //To safe against CRSF attack
DisplayBackup();
checkdrivedetails();
}
}
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)
{
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();
}
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
{
}
}
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 == "cmd")
{
string filename = e.CommandArgument.ToString();
if (filename != "")
{
// 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);
// var fil = System.IO.Path.GetFileName(e.CommandArgument.ToString());
// if (fil.Length > 0)
// {
// zip.AddFile(fil.ToString(), "NIELITBACKUP");
// readMeContent += string.Format("\t* {0} - {1}{2}", "BackupDatabase", 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"));
// 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();
// }
// }
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)
{
// 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
{
}
}
}
////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!");
// }
// }
web.config
<configuration>
<appSettings>
<add key="backup" value="G:\ESDM_NIELIT_NEW\"/>
</appSettings>
</configuration>
No comments:
Post a Comment