SQL Server Web Based Backup Tool

• Download source - 602 KB

Introduction

For getting backup from a Microsoft SQL Server database, you often need to use SSMS (SQL Server Management Studio). It is not a suitable solution for a common user. Common users do not have suitable knowledge of working with SSMS, also there are others problems such as port limitations, etc.

I believe that a comprehensive application which works with database should have its own database management tool such as a back-up tool.

In my last project, I added back-up tools to my web application and I want to share my code with you.

Background

This is the latest version of my application, the first one was working on ASP.NET web form but I extended it, the current version works on Ajax and ASP.NET Web Service.

Using the Code

For using this code, you should add 5 assembly files to your web project:

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended
• Microsoft.SqlServer.SqlEnum

As you can see, the application has a main web service “BackupServices.asmx” and an html file “index.html”.

The web service has 2 web methods:

[WebMethod(enableSession: true)]
public NSResult GetDatabases(string user, string password, string server)
{
    NSResult result = new NSResult();
    ServerConnection cc = new ServerConnection();

    if (GetServerConnection(user, password, server, ref cc))
    {
        result.LoginResult = "1";
        result.DatabaseList = GetDatabaseList(cc);
    }
    else
    {
        result.LoginResult = "0";
    }
    return result;
}

This web method has been used for checking the user’s authentication and if the authentication is correct, it returns a list of instance’s databases. This web method is called by a JavaScript function:

function LoginToSql() {
    $user = $("#txtUsername").val();
    $pass = $("#txtPassword").val();
    $server = $("#txtServer").val();

    $.ajax({
        type: "POST",
        url: "BackupServices.asmx/GetDatabases",
        data: "{ 'user': '" + $user + "', 'password' : 
        '" + $pass + "', 'server' : '" + $server + "' }",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        beforeSend: function () {
            $('#loader').fadeIn("slow");
        },
        success: function (msg) {
            if (msg.d.LoginResult == "1") {
                $("#dvLoginResult").html("Login Succeed!");
                $("#dvLoginResult").removeClass("btn-danger")
                $("#dvLoginResult").addClass("btn-success")
                $("#dvLoginResult").fadeIn("slow");
                $("#authentication-info").attr("disabled","disabled");
                setTimeout("$('#dvLoginResult').fadeOut('slow')", 1000);

                $("#drpDbList").html(msg.d.DatabaseList);
                $("#backup-form").fadeIn("slow");
            }
            else {
                $("#dvLoginResult").html("Login Failed");
                $("#dvLoginResult").removeClass("btn-success")
                $("#dvLoginResult").addClass("btn-danger")

                $("#dvLoginResult").fadeIn("slow");
                setTimeout("$('#dvLoginResult').fadeOut('slow')", 1000);
                $("#backup-form").fadeOut("slow");
            }
        },
        complete: function () {
            $('#loader').fadeOut("slow");
        }
    });
}

As you can see, this function connects to web methods via an Ajax function. In success call back function, it checks the result and if authentication is correct, it adds a list of databases to a dropdown list in the page.

In the true condition, this function performs two other necessary jobs:

• Disables the authentication form
• Appears the backup form

The second web method that is used in backup procedure is:

[WebMethod(enableSession: true)]
public NSResult GetDatabases(string user, string password, string server)
{
    NSResult result = new NSResult();
    ServerConnection cc = new ServerConnection();

    if (GetServerConnection(user, password, server, ref cc))
    {
        result.LoginResult = "1";
        result.DatabaseList = GetDatabaseList(cc);
    }
    else
    {
        result.LoginResult = "0";
    }
    return result;
}

This web method is called by BackupSql() JavaScript function:

function BackupSql() {
    $user = $("#txtUsername").val();
    $pass = $("#txtPassword").val();
    $server = $("#txtServer").val();
    $dbName = $("#drpDbList").val();``
    $FileName = $("#txtFileName").val();

    $.ajax({
        type: "POST",
        url: "BackupServices.asmx/BackupDatabase",
        data: "{ 'user': '" + $user + "', 'password' : '" + $pass + "', 
	'server' : '" + $server + "', 'dbName' : '" + $dbName + "', 
	'fileName' : '" + $FileName + "' }",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        beforeSend: function () {
            $('#loader').fadeIn("slow");
        },
        success: function (msg) {
            if (msg.d.LoginResult == "1") {
                $("#dvBackupResult").html(msg.d.ReturnMessage);
                $("#dvBackupResult").removeClass("btn-danger")
                $("#dvBackupResult").addClass("btn-success")
                $("#dvBackupResult").fadeIn("slow");
                setTimeout("$('#dvBackupResult').fadeOut('slow')", 8000);
            }
            else {
                $("#dvBackupResult").html(msg.d.ReturnMessage);
                $("#dvBackupResult").removeClass("btn-success")
                $("#dvBackupResult").addClass("btn-danger")

                $("#dvBackupResult").fadeIn("slow");
                setTimeout("$('#dvBackupResult').fadeOut('slow')", 8000);
            }
        },
        complete: function () {
            $('#loader').fadeOut("slow");
        }
    });
    }

I also add two simple classes to my web application that you can see in App_Code. These are used as Json objects.

Points of Interest

In this simple code, I got more familiar with the namespace (Microsoft.SqlServer.Management.Smo) which is very useful.