Click here to Skip to main content
15,867,851 members
Articles / Web Development / HTML

SQL Server Web Based Backup Tool

Rate me:
Please Sign up or sign in to vote.
4.41/5 (7 votes)
18 Mar 2016CPOL2 min read 21.9K   354   14   8
An ASP.NET one page application for getting backup from MS-SQL Server

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:

C#
[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:

JavaScript
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:

C#
[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:

JavaScript
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Sweden Sweden
Mehdi Mohseni is a Software senior developer (Consultant) in Sigma ITC (www.sigmait.se), Mehdi has a deep experience in N-Tier software applications as well as MVC design pattern. Mehdi has led more than 100 Asp.Net C# or VB.Net Automation applications. Mehdi is working in Toyota Material Handling Logistic Solution as Senior .Net Developer now.

Comments and Discussions

 
QuestionWhere is the code of the BackupDatabase web method? Pin
dimpant23-Mar-16 5:16
dimpant23-Mar-16 5:16 
General[My vote of 1] BackupDatabase method Pin
Dmitry A. Efimenko22-Mar-16 8:34
Dmitry A. Efimenko22-Mar-16 8:34 
QuestionNice, have a 5 Pin
Dewey19-Mar-16 7:48
Dewey19-Mar-16 7:48 
AnswerRe: Nice, have a 5 Pin
M.M.Mohseni20-Mar-16 22:31
M.M.Mohseni20-Mar-16 22:31 
Hi Dewey,
I plan to extend this application to add the following functionalities:
- Add Windows application GUI
- Restore a backup

I thank you for your helpful suggestion and I will add it to the application. I will inform you when I add these new functionalities.
QuestionBackupDatabase method? Pin
Wendelius18-Mar-16 23:58
mentorWendelius18-Mar-16 23:58 
PraiseNice Pin
RickZeeland18-Mar-16 22:14
mveRickZeeland18-Mar-16 22:14 
GeneralRe: Nice Pin
M.M.Mohseni20-Mar-16 22:26
M.M.Mohseni20-Mar-16 22:26 
GeneralRe: Nice Pin
dimpant23-Mar-16 6:14
dimpant23-Mar-16 6:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.