New Release (1.1.0)

It has been a year since I wrote this article. I got many responses for this backup utility. So I decided to update this program and made major changes to make it more stable in terms of functionality and performance. You can read my original article, SQL Server Backup Utility (Old Version).

Following is a list of changes for a new version:

Backup file extension is now .ZIP instead of .SQLBackup. This is to avoid any confusion.

Old version was having an issue with restoring data in certain conditions. It should have been fixed in this version.

Multi threading is added with backup and restore processes. This way, the program won't freeze during long operations.

UI is improved to display proper status of backup and restore.

Included GNU license.

I will be doing more updates to this program in the next few days. Please check my Website, shabdar.org, regularly to get the latest source.

Introduction

Whenever we want to backup or move the SQL Server database, most of us prefer to use a regular backup utility which is available through Enterprise Manager in SQL Server 2000 or Management Studio in SQL Server 2005. The limitation of using Enterprise Manager or Management Studio is that we have to use either complete or differential backups. This is a very tedious task when we want to backup only a small portion of a large database. For example, in my database I would like to take a backup of only 15 tables out of 100 tables or I may want to backup only a few records from these selected tables. For that reason, I was looking for a small utility which allows me to do so. After searching through hundreds of utility programs, I have decided to write my own utility.

Using Program

Click on Connect. This will display all available tables, views, stored procedures, user defined functions, user defined datatypes and users from database.

If you want to backup only data, check Backup Data and remove checkmark from Backup object structures (Scripts).

If you want to backup only scripts, check Backup object structures (Scripts) and remove checkmark from Backup Data.

Select objects that you want to backup from list. By default, all objects are selected. In the above picture, Adv_StackedBar2 and Adv_UserRoleTypes tables will be excluded from backup.

Apply any condition on table data. For example UserID > 10 and UserID < 25 in the above picture. It means only those records will be exported which satisfy this condition.

Modify number of rows to export on a particular 'table'. For example, in the above picture Top 20 *, means only top 20 rows will be exported for Adv_TodaysOutlook table.

Click on Start Backup. It will ask you to enter a file name and location. Note that backup file has extension *.zip. Remember that this is not a standard SQL server backup file. It's just a zip file which contains scripts for tables, views, stored procedures etc. It also includes data in row (*.dat) files.

Select database backup file (*.zip) that you want to restore. Remember this utility can only restore those backup files (*.zip) which are created by this utility itself. It cannot restore regular SQL server backup files.

When you select backup file, it displays all objects available in backup.

If you want to create a new database, check Create New Database option.

If you want to drop the existing database and recreate it, check Drop Existing Database option.

Select objects that you want to restore from list. By default all objects are selected.

Click on Start Restore.

Using Other Features

As you can see, both backup and restore forms include Preset combo box. What it does is it records server name, database name, user name and password information every time you backup or restore a database. Next time when you want to use the same server, you don't need to enter all these parameters again. You can select it from Preset combo list. It also remembers last accessed SQL server name.

Using Code

I am not writing the description of code in much detail. Most of the code is self explanatory and commented. Just a few quick notes. I have used SQL-DMO library for all database related tasks. For SQL-DMO library, either SQL Express or SQL Server 2000 client tools must be installed on your computer.