Here I am going to explain how to take the SQL Server database backup using C# coding.
You have to add the following references in your application
Go to Your Application and Right Click on References folder and select Add Reference.
Now Go to "Browse" Tab and browse the following path-
"C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies"
Now Select the following dlls :
Now Create a .aspx page and copy and Paste the 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></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center"><pre class="code"
style="background-color: white; border: 6px solid rgb(234, 234, 234); color: #444444; font-family: 'courier new'; font-size: 13px; line-height: 18px; outline: rgb(212, 212, 212) solid 1px; overflow: auto; padding: 15px; position: relative; text-align: center; width: 510px;"><asp:Image
ID="Image2" runat="server" ImageUrl="~/dotnetlogo.png" Width="430px" />
Server Name : <asp:TextBox ID="txtservername" runat="server" placeholder="Enter Server Name"></asp:TextBox>
DataBase Name : <asp:TextBox ID="txtDbName" runat="server" placeholder="Enter Database Name"></asp:TextBox>
Path : <asp:TextBox ID="txtpath" runat="server" placeholder="Enter Path"></asp:TextBox>
File Name : <asp:TextBox ID="TextBox1" runat="server" placeholder="Enter a file Name "></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Back Up" />
<asp:Label ID="Label1" runat="server" Text="Label" Visible="false"
style="color: #FF3300; font-weight: 700"></asp:Label>
<asp:Image ID="Image1" runat="server" Height="48px" ImageUrl="~/dotnet6.png"
Width="371px" /> </pre></div>
</div>
</form>
</body>
</html>
Then Go to the .Cs page and paste the code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// For sql server authentication
// public void BackupDatabase(string databaseName, string serverName, string destinationPath, string ss, string username,string password)
//For Windows authentication
public void BackupDatabase(string databaseName, string serverName, string destinationPath, string ss)
{
//Define a Backup object variable.
Backup sqlBackup = new Backup();
// string userName;
// string password;
//Specify the type of backup, the description, the name, and the database to be backed up.
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = ss;
sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath +ss+".bak", DeviceType.File);
// For sql server authentication
//Define Server connection
ServerConnection connection = new ServerConnection(serverName,username,password);
// For Windows authentication
//Define Server connection
ServerConnection connection = new ServerConnection(serverName);
//To Avoid TimeOut Exception
Server sqlServer = new Server(connection);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);
//Set the Incremental property to False to specify that this is a full database backup.
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
//Specify that the log must be truncated after the backup is complete.
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
sqlBackup.SqlBackup(sqlServer);
//Remove the backup device from the Backup object.
sqlBackup.Devices.Remove(deviceItem);
}
protected void Button1_Click(object sender, EventArgs e)
{
//BackupDatabase("Testing","Computer","D:\\");
// For sqlserver authentication
//BackupDatabase(txtDbName.Text,txtservername.Text,txtpath.Text,TextBox1.Text,"username",
"password");
// For Windows authentication
BackupDatabase(txtDbName.Text,txtservername.Text,txtpath.Text,TextBox1.Text);
Label1.Visible = true;
Label1.Text = " You take Database back up successfully </br>"+"Your file name is "+TextBox1.Text+".bak";
}
}
You have to add the following references in your application
Go to Your Application and Right Click on References folder and select Add Reference.
Now Go to "Browse" Tab and browse the following path-
"C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies"
Now Select the following dlls :
Demo :
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoExtended
- Microsoft.SqlServer.SqlEnum
Now Create a .aspx page and copy and Paste the 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></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center"><pre class="code"
style="background-color: white; border: 6px solid rgb(234, 234, 234); color: #444444; font-family: 'courier new'; font-size: 13px; line-height: 18px; outline: rgb(212, 212, 212) solid 1px; overflow: auto; padding: 15px; position: relative; text-align: center; width: 510px;"><asp:Image
ID="Image2" runat="server" ImageUrl="~/dotnetlogo.png" Width="430px" />
Server Name : <asp:TextBox ID="txtservername" runat="server" placeholder="Enter Server Name"></asp:TextBox>
DataBase Name : <asp:TextBox ID="txtDbName" runat="server" placeholder="Enter Database Name"></asp:TextBox>
Path : <asp:TextBox ID="txtpath" runat="server" placeholder="Enter Path"></asp:TextBox>
File Name : <asp:TextBox ID="TextBox1" runat="server" placeholder="Enter a file Name "></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Back Up" />
<asp:Label ID="Label1" runat="server" Text="Label" Visible="false"
style="color: #FF3300; font-weight: 700"></asp:Label>
<asp:Image ID="Image1" runat="server" Height="48px" ImageUrl="~/dotnet6.png"
Width="371px" /> </pre></div>
</div>
</form>
</body>
</html>
Then Go to the .Cs page and paste the code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// For sql server authentication
// public void BackupDatabase(string databaseName, string serverName, string destinationPath, string ss, string username,string password)
//For Windows authentication
public void BackupDatabase(string databaseName, string serverName, string destinationPath, string ss)
{
//Define a Backup object variable.
Backup sqlBackup = new Backup();
// string userName;
// string password;
//Specify the type of backup, the description, the name, and the database to be backed up.
sqlBackup.Action = BackupActionType.Database;
sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" + DateTime.Now.ToShortDateString();
sqlBackup.BackupSetName = ss;
sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem
BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath +ss+".bak", DeviceType.File);
// For sql server authentication
//Define Server connection
ServerConnection connection = new ServerConnection(serverName,username,password);
// For Windows authentication
//Define Server connection
ServerConnection connection = new ServerConnection(serverName);
//To Avoid TimeOut Exception
Server sqlServer = new Server(connection);
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName];
sqlBackup.Initialize = true;
sqlBackup.Checksum = true;
sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.
sqlBackup.Devices.Add(deviceItem);
//Set the Incremental property to False to specify that this is a full database backup.
sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
//Specify that the log must be truncated after the backup is complete.
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
sqlBackup.SqlBackup(sqlServer);
//Remove the backup device from the Backup object.
sqlBackup.Devices.Remove(deviceItem);
}
protected void Button1_Click(object sender, EventArgs e)
{
//BackupDatabase("Testing","Computer","D:\\");
// For sqlserver authentication
//BackupDatabase(txtDbName.Text,txtservername.Text,txtpath.Text,TextBox1.Text,"username",
"password");
// For Windows authentication
BackupDatabase(txtDbName.Text,txtservername.Text,txtpath.Text,TextBox1.Text);
Label1.Visible = true;
Label1.Text = " You take Database back up successfully </br>"+"Your file name is "+TextBox1.Text+".bak";
}
}
Very good tutorial ..
ReplyDelete