Online Microsoft SQL Server Backup and Restore Made Easy with myLittleBackup
by Anthony Wilko, Infuseweb LLC

Short description

For service providers and anyone else needing online backup and restore of Microsoft SQL Server for their customers, look no further than myLittleTools' myLittleBackup. With an easy to use web-based front-end, users can quickly and easily perform, download, upload, and restore backups to and from Microsoft SQL Server 2000, 2005, and 2008. myLittleBackup takes care of any synchronization issues, even allowing for seamless integration with custom software such as Control Panels and portals. In all, a very useful application that should be a no-brainer for Microsoft SQL hosting providers looking to make their customers lives easier.

Introduction

I've been running a small web hosting company for around 7 years now. One of the biggest requests I get from my customers is to download a backup of their Microsoft SQL Server (MS SQL) databases or restore a backup from one they've developed locally. If you're familiar with this scenario, you know this can be a tedious task if done manually, especially if you have multiple servers in a distributed hosting environment.

For example, if you haven't automated any of your MS SQL backup/restore processes and the customer requests a backup of a SQL database, you've got to then log into the database server, run a backup of their database from SQL Management Studio, then copy the file over to the user's FTP root so they can download it. While not terribly bad, it does take time and it's a little inconvenient for all involved.

But that's the easy part. The tediousness comes into play when a customer requests a restore of a database file they've given you if, say, they've transferred hosts. Hopefully, they provided you with either a valid backup file (.BAK) to restore or both the data file and log file (.MDF/.LDF) so you can attach their database into your SQL Server. Once you have the proper file, you can proceed with the restore. If you're restoring a .BAK file, then you've got user synchronization issues to deal with once the DB is restored. If you're attaching the database, you need to make sure the right DB user is selected when you attach so they can access their database. Either way, it's more work than you probably can or want to deal with on a regular basis.

If you don't feel like dealing with that, your other option is to provide the user with the ability to backup and restore directly through SQL Management Studio. Usually, though, this means they need to have elevated privileges,and with that they could end up with greater freedoms than hosts want to contend with.

We found the solution to these problems with myLittleTools myLittleBackup. myLittleBackup was developed to specifically allow users to easily perform backups and restores of their Microsoft SQL 2000, 2005, and 2008 databases through a simple web-based interface. They can also store and download a customizable number of backups, and restore from either their desktop or their stored backups. Furthermore, it handles all the database user synchronization issues (such as orphaned users) that you once had to do manually. Over the last year using this product, I can safely say it has saved our staff and customers countless hours of back and forth when backing up and restoring Microsoft SQL Server databases.

So let's talk more about the software and how it works.

Requirements

myLittleBackup v1.7 is a .NET 2.x and higher web application that support Microsoft SQL Server 2000, 2005, and 2008 and requires Microsoft IIS 5, 6, or 7 web servers to run. Outside of that, it's a very lightweight application requiring only about 1.1 MB of disk space and very little CPU to run. The database backup and restore folders are what will take up the most space and how much you need for that all depends on how many databases you want to back up and retain copies of and how big they are backed up.

Installation

Installation is very straightforward. Just set up an IIS web site with .NET 2.x or higher enabled, unzip the web app into a web accessible folder under that web site and be sure that folder is set up as an application, and you're done!

Configuration

This is where things could get a bit tricky depending on how you want to set up the myLittleBackup application. The installation guide goes step by step through setting up each of the four use scenarios so I am only going to cover them briefly here.

The different scenarios include:

• IIS, SQL, and storage on same machine (the easiest to setup). This scenario is the easiest to set up because everything is on the same machine. This would be a good setup for a standalone hosting server, for example. You just need to create some local folders to store the uploads and backups and share them so that the application can access them as defined in the manual. Just make sure you have enough disk space on the server to account for the additional user backup files that will be placed on the server.
• IIS and Storage on one machine, SQL on another. If you have separate database servers, you can use this method to access them all from one server that hosts the application and manages the backup and upload files. This can be trickier to set up because you need to make sure the SQL server has access to the backup and restore files placed on the IIS machine. Impersonation is not necessary, but there does have to be some synchronization of user accounts set up on each machine to ensure sharing of files can occur.
• IIS on one machine, SQL on another, and storage on a third. For a fully distributed setup this might be a good scenario. For example, you could have a file share on a SAN that stores all the backed up and restore files reducing the space requirements on the IIS server and SQL server. But, because this configuration requires three different machines, setting up access permissions across them all might be a difficult challenge depending upon your network environment. The installation instructions talk you through setting permissions on each machine, but it may take a little experimentation to find the right settings in your environment.
• IIS on one machine, SQL and storage on another (a typical distributed host scenario and recommended by myLittleTools). This approach is a nice balance where you can keep your database files all together on one server, and let the application reside elsewhere. This scenario does use impersonation so that the application server can manipulate files as it needs to on the database server and it can be a bit of a headache to set up as we experienced during our testing, depending on your network setup. But once we got all the kinks worked out, things have been running smoothly ever since.

Customization

The nice thing about myLittleBackup is that you can customize much of the interface, turning on or off features of the application, or eliminating them completely from the menus by simply editing some of the config files. It is even possible to change the look and feel by creating new skins.

Here's a list of some of the noteworthy features that can be customized:

• Configure where you want to backup and uploaded restore files to be stored.
• Configure how many backups and/or restores a user can maintain in their inventory
• Set a max database upload file size limit
• Determine which database roles the restored users on a database will be assigned
• Auto- fix orphaned users (this one is a real time saver!)
• Restore original database users to ensure a database uploaded and restored by the customer doesn't lose the original user created on the database. Prevents disconnect between users created by a control panel and those contained in the uploaded database.
• Set databases to read only when uploaded
• Automatically shrink the database to save space
• Custom SQL that can be executed after a restore
• Check a database to ensure it doesn't have any consistency problems
• An activity log indicating the backups and restores being done by users, along with customizable log path.
• Ability to modify menu items to hide or show them as necessary for your environment
• Ability to skin the interface to match your look and feel

Putting it to use

As previously mentioned, once you have myLittleBackup set up, it's a matter of following the web-based wizard to backup and restore Microsoft SQL Server databases. I'm going to cover the main usage areas of myLittleBackup here.

Logging In

When you first visit myLittleBackup, you're presented with a login screen (Figure 1). The Options button has been clicked to expand the lower half connection properties for demonstration. Depending on how many database servers you have configured in your config file, the Server Name may be a dropdown field allowing you to choose which server you want to log into. Authentication methods can be selected (Named Pipes, Multiprotocol, TCP.IP, etc), or left at Default which uses the standard TCP/IP connection method.

Once you've logged in, you're presented with the Tools menu open by default (Figure 2), which contains links to backup, restore, check, and manage database files.

Backing Up a Microsoft SQL Database

Clicking the Backup Databases link brings you to the backup wizard, which is a simple 5 step process to backing up the logged in database user's database. The steps include 1) choosing from a dropdown of available databases which the user has the ability back up 2) Viewing information about the database such as upload size and last backup 3) Providing a custom backup set name and description (optional) 4) Confirmation and execution of the backup and 5) Retrieval of the backup file for download (Figure 3)

Restoring a Microsoft SQL Database

Restoring a MS SQL database is also a simple wizard-driven process. Clicking the Restore Databases menu link brings you to the restore wizard page which contains a 6 step process for restoring a database. These steps include 1) Choose the database you wish to restore to from the available databases tied to the logged in user 2) Review information about the current database you're about to restore to 3) Choose a backup file to restore. In the previous step, we backed up the test database and you can see that it is available to restore from, or you can click the browse button to restore a .BAK file you have on your local computer. 4) Review the backup sets available to restore from 5) Click the restore button to begin the restore process and 6) Perform a check on the database to make sure it uploaded properly and there were no errors (Figures 4 and 5)

Checking Databases for Correct User Mappings

myLittleBackup has a nice database check feature that allows you to check for database inconsistencies in users such as orphaned users, and corrects the errors if possible. Again, this is all simple with a wizard-driven 4 step process. 1) Choose the database 2) Review the information about the database 3) Check for user inconsistencies 4) Run a check on other database properties for any set up errors (Figure 6).

Managing Backup Files

The last main user area is the Manage Backup Files area. Here, users can manage any files they've backed up, or uploaded to myLittleBackup. They are able to delete backups, download backups, and copy backups from backup to restore folders so they can restore them when they're ready. The 4-step backup management wizard, as usual, makes this a no-brainer process (Figure 7).

Silent integration

One of the nice extra features of myLittleBackup is that it can integrate with existing software, such as hosting control panels, through the use of a silent logon module. This prevents users of a third party application from having to log in to myLittleBackup separately, providing a more seamless experience. While this feature was not tested in this review, it appears to be pretty straight forward. A handy 3 page integration guide details what parameters to send to the myLittleBackup so developers can easily add it into their third party applications.

Room for improvement?

Overall, this is a great application and there's not much I can fault. One thing I did notice that doesn't seem to be addressed yet is that over time as users move on and their databases are deleted from the server their backup/restore files don't get cleaned off the server. This means you have to periodically go through the folders and determine who no longer exists. This is made a little easier by the fact myLittleBackup puts the backup/restore files in folders named the same as their databases, so you just have to look through your active SQL databases in SQL Management Studio and delete any in myLittleBackup that aren't on the database server.

And while the actual app installation process was smooth, the configuration of the permissions and communication between servers (or even on the standalone setup) can be a bit tedious and frustrating at times. myLittleTools was always there to help as usual, and they've updated their instructions to be more detailed, but this is an area I'd still like to see refined a little more.

Conclusion

If you're a web host, or even just a company needing remote backup/restore of your Microsoft SQL Server database files, you owe it to yourself to take a close look at myLittleBackup. I don't know of any other program that makes it so easy to do the task and if the number of hosts and programs integrating myLittleBackup into their environments is any indicator, this software will continue to save administrators countless hours supporting customer requests to backup and restore their databases.