Click here to Skip to main content
14,236,234 members

SQL Server Web Based Backup Tool

Rate this:
4.41 (7 votes)
Please Sign up or sign in to vote.
4.41 (7 votes)
18 Mar 2016CPOL
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:

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

License

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

Share

About the Author

M.M.Mohseni
Software Developer (Senior)
Sweden Sweden
Mehdi Mohseni is a Software senior developer in Future Covenant (www.futurecovenant.com), Mehdi has a deep experience in N-Tier software applications as well as MVC design pattern. Henry has led more than 100 Asp.Net C# or VB.Net Automation applications. His latest project is an EHR (Electronic Health Record) based on Asp.Net C# Application, ADO.Net Entity Framework 5.0 and 3 layer architecture.

Comments and Discussions

 
QuestionWhere is the code of the BackupDatabase web method? Pin
dimpant23-Mar-16 5:16
memberdimpant23-Mar-16 5:16 
General[My vote of 1] BackupDatabase method Pin
Dmitry A. Efimenko22-Mar-16 8:34
memberDmitry A. Efimenko22-Mar-16 8:34 
QuestionNice, have a 5 Pin
Dewey19-Mar-16 7:48
memberDewey19-Mar-16 7:48 
AnswerRe: Nice, have a 5 Pin
M.M.Mohseni20-Mar-16 22:31
memberM.M.Mohseni20-Mar-16 22:31 
QuestionBackupDatabase method? Pin
Wendelius18-Mar-16 23:58
mveWendelius18-Mar-16 23:58 
PraiseNice Pin
RickZeeland18-Mar-16 22:14
mveRickZeeland18-Mar-16 22:14 
Nice, but you might also mention how to set this up on a webserver like IIS or Cassandra, not every user is familiar with this.
You can also add a "download source code" button on top of the article.
GeneralRe: Nice Pin
M.M.Mohseni20-Mar-16 22:26
memberM.M.Mohseni20-Mar-16 22:26 
GeneralRe: Nice Pin
dimpant23-Mar-16 6:14
memberdimpant23-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.

Article
Posted 18 Mar 2016

Stats

14.4K views
311 downloads
13 bookmarked