How to take backup of Table data from Database to text / sql file using C# and VB.Net in ASP.Net

Hi nedash,

For this you need too add dll from the below path in your project.

32-bit: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

64-bit: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

For SQL Server 2016

32-bit: C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies

64-bit: C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies

You need to add references to:

• Microsoft.SqlServer.ConnectionInfo.dll

• Microsoft.SqlServer.Smo.dll

• Microsoft.SqlServer.Management.Sdk.Sfc.dll

• Microsoft.SqlServer.SqlEnum.dll

C#

using System;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        CreateScriptTable("master", "Customers", ConfigurationManager.ConnectionStrings[1].ConnectionString);
        CreateScriptDataBase("LoginDB", ConfigurationManager.ConnectionStrings[1].ConnectionString);
    }

    /// <summary>
    /// Create single Table script with Data of specified DataBase and Table.
    /// </summary>
    /// <param name="dataBaseName">DataBase Name</param>
    /// <param name="tableName">Table Name</param>
    /// <param name="connectionString">Connection String</param>
    public void CreateScriptTable(string dataBaseName, string tableName, string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);
        ServerConnection serverConnection = new ServerConnection(con);
        Server server = new Server(serverConnection);
        Database database = server.Databases["" + dataBaseName + ""];
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        var sb = new System.Text.StringBuilder();
        Microsoft.SqlServer.Management.Smo.Table table = database.Tables["" + tableName + ""];
        sb.Append("DROP TABLE " + table.Name);
        sb.Append(Environment.NewLine);
        foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
        {
            sb.Append(s);
            sb.Append(Environment.NewLine);
        }
        string folder = Server.MapPath("~/Scripts/");
        string filename = folder + tableName + ".sql";
        System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
        fs.Write(sb);
        fs.Close();
    }

    /// <summary>
    /// Create all Table script with Data of specified DataBase.
    /// </summary>
    /// <param name="dataBaseName">DataBase Name</param>
    /// <param name="connectionString">Connection String</param>
    public void CreateScriptDataBase(string dataBaseName, string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);
        ServerConnection serverConnection = new ServerConnection(con);
        Server server = new Server(serverConnection);
        Database database = server.Databases["" + dataBaseName + ""];
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        var sb = new System.Text.StringBuilder();
        foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
        {
            sb.Append("DROP TABLE " + table.Name);
            sb.Append(Environment.NewLine);
            foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
            {
                sb.Append(s);
                sb.Append(Environment.NewLine);
            }
        }
        string folder = Server.MapPath("~/Scripts/");
        string filename = folder + dataBaseName + ".sql";
        System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
        fs.Write(sb);
        fs.Close();
    }
}