How to take backup sql server database through ASP.NET and C# .NET ?


In this article I am going to explain simple way to take backup of your database from ASP.NET. This is one line of code to take backup of your database. Use this technique in windows application too to take auto backup every day.

Description


Normally we are handled lots of query to process and save / modify records in database. Some time we did mistake in delete query to loss some records, in that time we need old database backup to recover database.

Here I am explained in simple way to take back up of your database. Use this technique in windows application too in form load event and add that .exe to windows scheduled task to take back up automatically every day.

In ASP.NET


Default.aspx Code Behind



using System.Data;
using System.Data.SqlClient;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
//Metioned here your database name
string dbname = "test1";
SqlConnection sqlcon=new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs 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!");
}
}
}

In C#.NET Windows application and add scheduled task



using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
//Metioned here your database name
string dbname = "test1";
SqlConnection sqlcon=new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs 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 " + dbname + " to disk='" + destdir + "\\" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".Bak'", sqlcon);
sqlcmd.ExecuteNonQuery();
//Close connection
sqlcon.Close();
MessageBox.Show("Backup database successfully");
}
catch (Exception ex)
{
MessageBox.Show("Error During backup database!");
}
}
}
}

Build that windows application in bin directory .exe file automatically created.
Now go to control panel choose sheduled task -> Add Schedule task choose that exe file and select which data that exe run after that your database backup automatically created every day and stored in specified location.

Output
Check the D:\backupdb directory the database backup created automatically.

Conclusion:
I hope this article help you to learn take database in easy way.


Comments

Guest Author: S. SUKUMAR17 Jul 2012

Nice.........

Author: nuwan rathnayake10 Aug 2012 Member Level: Silver   Points : 0

Its The Best....
NICE WORK.

Author: nuwan rathnayake10 Aug 2012 Member Level: Silver   Points : 1

Dear Ravindran,

Is it possible to backup in LAN Sql Server Database.
I mean, one machine have install sql server and another machine havent install sql sever. I run this application non sql server machine is it possible to backup Database Sql server installed Data Base.

Thank You.

Author: Ravindran11 Aug 2012 Member Level: Diamond   Points : 0

Nuwan,

The sql server login user have sufficient privilege then it is possible but the backup is stored in the SQL server installed PC location only not your PC

Author: nuwan rathnayake11 Aug 2012 Member Level: Silver   Points : 0

Thanks Your advice Ravindran. I learn lot of thing to day.

Author: Ravindran11 Aug 2012 Member Level: Diamond   Points : 0

thanks nuwan.. check it my other articles too..

Guest Author: ShaikInthiyaz01 Sep 2012

VERY SIMPLY

i believe that you have good explanation skills

Guest Author: savitri08 Oct 2012

I am geeting error while giving bak up using sqlexpress/Windows application

error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection

Please Give solution

Guest Author: Uday Gupta02 Nov 2012

Simple coding & Nice work

Author: nuwan rathnayake30 Dec 2012 Member Level: Silver   Points : 7

My local pc this asp.net programme perfectly work. I implement & run this programme our MS2008 sever machine. I got the this error.

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The backup or restore was aborted. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlBackup.BackupDB_Separate() in E:\site\ifinance\SqlBackup.aspx.vb:line 91

Please help me.

Author: Ravindran31 Dec 2012 Member Level: Diamond   Points : 0

nuwan,

Increase your command timeout to avoid this error

Guest Author: Damodar Salui20 Mar 2013

how can i put dbName;
For my dbName="D:\App_data\Database.MDF".
if i put @"D:\App_data\Database.MDF" or "D:\\App_data\\Database.MDF"

It will show incorrect syntax near ':'.

Please give me solution for this.

Guest Author: sandip25 Mar 2013

Very nice..!!!

Guest Author: ravi02 Jul 2013

getting Error During backup database,please help me for c#.net program

Guest Author: max27 May 2014

Cannot open backup device 'C:\backupdb\28052014_103059.Bak'. Operating system error 3(The system cannot find the path specified.).