Generate Database Backup (.bak) file and download using C# and VB.Net in ASP.Net

thereallover01
 
on Oct 20, 2021 12:02 AM
Sample_834720.zip
724 Views

i need to back up and download database of sql

i have found people first using back up query and backing up in a local folder and then downloading the file but in my case since it is hosted website i can't do that.

i am thinking of using temp folder.

any help will be appreciated.

DataTable dt = blu.queryFunction("BACKUP database " + dbNAme + " to disk='" + backupDestination + "\\" + dbNAme + " of " + DateTime.Now.ToString("yyyy-MM-dd@HH_mm") + ".Bak'");

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 25, 2021 02:14 AM

Hi thereallover0...,

Check this example. Now please take its reference and correct your code.

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    string dbNAme = "AJAXSamplesDB";
    string backupDestination = Server.MapPath("~/BackUp");
    if (!Directory.Exists(backupDestination))
    {
        Directory.CreateDirectory(backupDestination);
    }
    string fileName = dbNAme + " of " + DateTime.Now.ToString("yyyy-MM-dd@HH_mm") + ".bak";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "BACKUP database " + dbNAme + " to disk='" + backupDestination + "\\" + fileName + "'";
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = query;
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteScalar();
            con.Close();
        }
    }

    byte[] bytes = File.ReadAllBytes(Path.Combine(backupDestination, fileName));
    // Delete .bak file from server folder.
    if (Directory.Exists(backupDestination))
    {
        Directory.Delete(backupDestination, true);
    }
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/octet-stream";
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
    Response.BinaryWrite(bytes);
    Response.Flush();
    Response.End();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dbNAme As String = "AJAXSamplesDB"
    Dim backupDestination As String = Server.MapPath("~/BackUp")
    If Not Directory.Exists(backupDestination) Then
        Directory.CreateDirectory(backupDestination)
    End If
    Dim fileName As String = dbNAme & " of " & DateTime.Now.ToString("yyyy-MM-dd@HH_mm") & ".bak"
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "BACKUP database " & dbNAme & " to disk='" & backupDestination & "\" & fileName & "'"
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = query
            cmd.Connection = con
            con.Open()
            cmd.ExecuteScalar()
            con.Close()
        End Using
    End Using

    Dim bytes As Byte() = File.ReadAllBytes(Path.Combine(backupDestination, fileName))
    ' Delete .bak file from server folder.
    If Directory.Exists(backupDestination) Then
        Directory.Delete(backupDestination, True)
    End If
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.ContentType = "application/octet-stream"
    Response.AppendHeader("Content-Disposition", "attachment; filename=" & fileName)
    Response.BinaryWrite(bytes)
    Response.Flush()
    Response.End()
End Sub

Screenshot

thereallover01
 
on Oct 26, 2021 01:21 AM

i still got "Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack." but i found that it was because of update panel in download button.

Thanks a lot !!!

so here's the working code :D

protected void btnDownload_Click(object sender, EventArgs e)
{
    try
    {
        string dbNAme = dbName;
        string backupDestination = Server.MapPath("~/BackUp");
        string dateStamp = DateTime.Now.ToString("yyyy-MM-dd@HH_mm");
        string fileName = dbName + " of " + dateStamp + ".Bak";
 
        if (!Directory.Exists(backupDestination))
        {
            Directory.CreateDirectory(backupDestination);
        }
 
        DataTable dt = blu.queryFunction("BACKUP database " + dbNAme + " to disk='" + backupDestination + "\\" + fileName + "'");
 
 
        byte[] bytes = File.ReadAllBytes(Path.Combine(backupDestination, fileName));
        // Delete .bak file from server folder.
        if (Directory.Exists(backupDestination))
        {
            Directory.Delete(backupDestination, true);
        }
        Response.Clear();
        Response.Buffer = false;
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/octet-stream";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
        Response.BinaryWrite(bytes);
        Response.Flush();
        Response.End();
    }
    catch(Exception ex)
    {
        string exception = ex.ToString();
    }           
}