Hi akhter,

Let the client to download the script file in their pc.

Refer below code. 

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Smo

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    //CreateScriptTable("MetaTagsDB", "MetaTags", ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    CreateScriptDataBase("MetaTagsDB", ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
}

public void CreateScriptTable(string dataBaseName, string tableName, string connectionString)
{
    string folder = Server.MapPath("~/Scripts/");
    string fileName = "";
    if (!Directory.Exists(folder))
    {
        Directory.CreateDirectory(folder);
    }
    SqlConnection con = new SqlConnection(connectionString);
    ServerConnection serverConnection = new ServerConnection(con);
    Server server = new Server(serverConnection);
    Database database = server.Databases["" + dataBaseName + ""];
    if (database != null)
    {
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        StringBuilder sb = new StringBuilder();
        foreach (Table table in database.Tables)
        {
            if (table.Name.ToLower() == tableName.ToLower())
            {
                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);
                }
                fileName = folder + tableName + ".sql";
                StreamWriter fs = File.CreateText(fileName);
                fs.Write(sb);
                fs.Close();
            }
        }
    }

    Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);
    Response.ContentType = "application/sql";
    Response.WriteFile(fileName);
    Response.End();
}

public void CreateScriptDataBase(string dataBaseName, string connectionString)
{
    string folder = Server.MapPath("~/Scripts/");
    string fileName = "";
    if (!Directory.Exists(folder))
    {
        Directory.CreateDirectory(folder);
    }
    SqlConnection con = new SqlConnection(connectionString);
    ServerConnection serverConnection = new ServerConnection(con);
    Server server = new Server(serverConnection);
    Database database = server.Databases["" + dataBaseName + ""];
    if (database != null)
    {
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        StringBuilder sb = new StringBuilder();
        foreach (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);
            }
        }
        fileName = folder + dataBaseName + ".sql";
        StreamWriter fs = File.CreateText(fileName);
        fs.Write(sb);
        fs.Close();
    }

    Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);
    Response.ContentType = "application/sql";
    Response.WriteFile(fileName);
    Response.End();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    CreateScriptDataBase("MetaTagsDB", ConfigurationManager.ConnectionStrings("constr").ConnectionString)
End Sub

Public Sub CreateScriptTable(ByVal dataBaseName As String, ByVal tableName As String, ByVal connectionString As String)
    Dim folder As String = HttpContext.Current.Server.MapPath("~/Scripts/")
    Dim fileName As String = ""

    If Not Directory.Exists(folder) Then
        Directory.CreateDirectory(folder)
    End If

    Dim con As SqlConnection = New SqlConnection(connectionString)
    Dim serverConnection As ServerConnection = New ServerConnection(con)
    Dim server As Server = New Server(serverConnection)
    Dim database As Database = server.Databases("" & dataBaseName & "")

    If database IsNot Nothing Then
        Dim scripter As Scripter = New Scripter(server)
        scripter.Options.ScriptData = True
        scripter.Options.ScriptSchema = True
        scripter.Options.ScriptDrops = False
        Dim sb As StringBuilder = New StringBuilder()

        For Each table As Table In database.Tables

            If table.Name.ToLower() = tableName.ToLower() Then
                sb.Append("DROP TABLE " & table.Name)
                sb.Append(Environment.NewLine)

                For Each s As String In scripter.EnumScript(New Urn() {table.Urn})
                    sb.Append(s)
                    sb.Append(Environment.NewLine)
                Next

                fileName = folder & tableName & ".sql"
                Dim fs As StreamWriter = File.CreateText(fileName)
                fs.Write(sb)
                fs.Close()
            End If
        Next
    End If

    Response.AppendHeader("content-disposition", "attachment; filename=" & fileName)
    Response.ContentType = "application/sql"
    Response.WriteFile(fileName)
    Response.End()
End Sub

Public Sub CreateScriptDataBase(ByVal dataBaseName As String, ByVal connectionString As String)
    Dim folder As String = HttpContext.Current.Server.MapPath("~/Scripts/")
    Dim fileName As String = ""

    If Not Directory.Exists(folder) Then
        Directory.CreateDirectory(folder)
    End If

    Dim con As SqlConnection = New SqlConnection(connectionString)
    Dim serverConnection As ServerConnection = New ServerConnection(con)
    Dim server As Server = New Server(serverConnection)
    Dim database As Database = server.Databases("" & dataBaseName & "")

    If database IsNot Nothing Then
        Dim scripter As Scripter = New Scripter(server)
        scripter.Options.ScriptData = True
        scripter.Options.ScriptSchema = True
        scripter.Options.ScriptDrops = False
        Dim sb As StringBuilder = New StringBuilder()

        For Each table As Table In database.Tables
            sb.Append("DROP TABLE " & table.Name)
            sb.Append(Environment.NewLine)

            For Each s As String In scripter.EnumScript(New Urn() {table.Urn})
                sb.Append(s)
                sb.Append(Environment.NewLine)
            Next
        Next

        fileName = folder & dataBaseName & ".sql"
        Dim fs As StreamWriter = File.CreateText(fileName)
        fs.Write(sb)
        fs.Close()
    End If

    Response.AppendHeader("content-disposition", "attachment; filename=" & fileName)
    Response.ContentType = "application/sql"
    Response.WriteFile(fileName)
    Response.End()
End Sub

Hi akhter,

Apply proper permissions to the folders it does not have access.

You need to provide Read/Write permissions to

IIS_USR

NETWORK SERVICE

IIS_USRS

Access to the path denied error also came if file doesn't exist.

Before Response.WriteFile you should perform a check to see if the file exists first.

This will also give you the opportunity of making sure that the path it's attempting to get will work.

if(File.Exist(fileName))
{ 
    Response.AppendHeader("content-disposition", "attachment; filename=" + fileName);
    Response.ContentType = "application/sql";
    Response.WriteFile(fileName);
    Response.End();
}