Take Backup of a SqlServer database through c# in
asp.net
This is my aspx code:
<%@ Page Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!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 runat="server">
<title>Take DataBase Backup</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="4"
cellspacing="4"
width="50%"
align="center"
border="1">
<tr>
<td width="200px">
SqlServer:
</td>
<td>
<asp:DropDownList ID="CmbServerName"
runat="server"
OnSelectedIndexChanged="CmbServerName_SelectedIndexChanged"
Width="200px"
AutoPostBack="true">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
DataBase Name:
</td>
<td>
<asp:DropDownList ID="ddlDataBAse"
runat="server"
OnSelectedIndexChanged="CmbServerName_SelectedIndexChanged"
Width="200px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
UID:
</td>
<td>
<asp:TextBox ID="txtUid" runat="server"
Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Password:
</td>
<td>
<asp:TextBox ID="txtPassword"
runat="server"
Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnBackup"
runat="server"
Text="Take
Backup" OnClick="btnBackup_Click"
Width="200px"
/>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
This is my aspx.cs code:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.SqlClient;
using System.Data.Sql;
public partial class _Default :
System.Web.UI.Page
{
SqlConnection sqlcon = new
SqlConnection();
SqlCommand sqlcmd = new
SqlCommand();
SqlDataAdapter da = new
SqlDataAdapter();
DataTable dt = new
DataTable();
protected void
Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GetAllSqlServer();
GetAllDataBaseName();
}
}
private void
GetAllSqlServer()
{
string myServer = Environment.MachineName;
DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();
//If you are in a network then use below code
SqlDataSourceEnumerator sdse = SqlDataSourceEnumerator.Instance;
DataTable table = sdse.GetDataSources();
foreach (DataRow
row in table.Rows)
{
CmbServerName.Items.Add(row["ServerName"].ToString()
+ "\\" + row["InstanceName"].ToString());
}
//If you are on a local machine
CmbServerName.Items.Add(myServer);
}
protected void
btnBackup_Click(object sender, EventArgs e)
{
//Your Connection String
sqlcon.ConnectionString = @"Server="
+ CmbServerName.SelectedValue.ToString() + ";database="
+ ddlDataBAse.SelectedValue.ToString() + ";uid="
+ txtUid.Text + ";pwd=" +
txtPassword.Text + ";";
string destdir = "C:\\backupdb";
if (!System.IO.Directory.Exists(destdir))
{
System.IO.Directory.CreateDirectory("C:\\backupdb");
}
try
{
//Open connection
sqlcon.Open();
//query to take backup database
sqlcmd = new SqlCommand("backup database " +
ddlDataBAse.SelectedValue.ToString() + " to
disk='" + destdir + "\\"
+
ateTime.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!");
}
}
protected void
CmbServerName_SelectedIndexChanged(object
sender, EventArgs e)
{
GetAllDataBaseName();
}
private void
GetAllDataBaseName()
{
String conxString = "Data
Source='" + CmbServerName.SelectedValue.ToString() + "'; Integrated Security=True;";
using (SqlConnection
sqlConx = new SqlConnection(conxString))
{
sqlConx.Open();
DataTable tblDatabases = sqlConx.GetSchema("Databases");
sqlConx.Close();
foreach (DataRow
row in tblDatabases.Rows)
{
ddlDataBAse.Items.Add(row["database_name"].ToString());
}
}
}
}
When I run My Application:
Figure1.
After selecting sqlserver All database will
automatically populate in database name dropdown list like below.
Figure 2.
After selecting database of which you want to take
database backup just type user ID and password and click takebackup button..
Figure 3.
The location where your backup file saved.
Figure 4.