Resources » Code Snippets » ASP.NET WebForms

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


Posted Date: 25-Mar-2012  Last Updated:   Category: ASP.NET WebForms    
Author: Member Level: Diamond    Points: 25


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.


Did you like this resource? Share it with your friends and show your love!

Responses to "How to take backup sql server database through ASP.NET and C# .NET ?"
Guest Author: S. SUKUMAR     17 Jul 2012
Nice.........


Author: nuwan rathnayake    10 Aug 2012Member Level: Silver   Points : 0
Its The Best....
NICE WORK.



Author: nuwan rathnayake    10 Aug 2012Member 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: Ravindran    11 Aug 2012Member 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 rathnayake    11 Aug 2012Member Level: Silver   Points : 0
Thanks Your advice Ravindran. I learn lot of thing to day.


Author: Ravindran    11 Aug 2012Member Level: Diamond   Points : 0
thanks nuwan.. check it my other articles too..


Guest Author: ShaikInthiyaz     01 Sep 2012
VERY SIMPLY

i believe that you have good explanation skills



Guest Author: savitri     08 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 Gupta     02 Nov 2012
Simple coding & Nice work


Author: nuwan rathnayake    30 Dec 2012Member 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: Ravindran    31 Dec 2012Member Level: Diamond   Points : 0
nuwan,

Increase your command timeout to avoid this error



Guest Author: Damodar Salui     20 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: sandip     25 Mar 2013
Very nice..!!!


Guest Author: ravi     02 Jul 2013
getting Error During backup database,please help me for c#.net program


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


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India