SQL Server backup utility

• Download source - 401 Kb
• Download setup - 2.01 MB

Introduction

Whenever we want to backup or move SQL Server databases, most of us prefer to use a regular backup utility which is available through the Enterprise Manager in SQL Server 2000 or the Management Studio available in SQL Server 2005. The limitation of using the Enterprise Manager or the Management Studio is 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 the backup of only 15 tables out of 100 tables. I want to backup only a few records from these selected tables. For that reason, I was looking for a small utility which allows me ht facility. After searching through hundreds of utility programs, I decided to write my own.

Features

• Backup of selected objects
• Backup of selected data
• Can backup scripts alone
• Can backup data alone

Using the program

To backup the database:

• Open the database backup window from the Tools menu.
• Enter the server name, database name, user name, and password for SQL Server.
• Click on Connect. This will display all the available tables, views, stored procedures, user defined functions, user defined datatypes, and users from the database.
• Select how many rows you want to export from each table, e.g., 'Top 1000 *'. Default is 'Top 100 percent', which means all rows.
• If you want to backup data alone, check Backup Data and remove the checkmark from Backup object structures (Scripts).
• If you want to backup scripts alone, check Backup object structures (Scripts) and remove the checkmark from Backup Data.
• Select objects that you want to backup from the list. By default, all objects are selected.
• Apply any condition on the table data. For example, 'StudentID > 120 and StudentID < 200' in the above picture. It means only those records will be exported which satisfy this condition.
• Modify the number of rows to export on a particular 'table', if you want to do so. For example, in the above picture, 'Top 99 *' means top 99 rows only for the 'OtherIncome' table.
• Click on Start Backup. It will ask you to enter a file name and location. Note that the backup file has an extension *.SQLBackup.

To restore the backup:

• Open the Database Restore window from the Tools menu.

• Enter the server name, database name, user name, and the password for SQL Server.
• Select the database backup file that you want to restore. Note that the database backup file extension for this program is *.SQLBackup.
• When you select the backup file, it displays all the objects available in the backup.
• If you want to create a new database, check the Create New Database option.
• If you want to drop existing an database and recreate it, check both the Create New Database and the Drop Existing Database options.
• Select the objects that you want to restore from the list. By default, all objects are selected.
• Click on Start Restore.

Using other features

• As you can see on both the backup and the restore forms, there are buttons available as Load Settings and Save Settings As. Once we setup different parameters for backup, i.e., selecting objects, specifying conditions etc., we can save all of these together in a settings file. We can load these settings any time we want, so that we do not need to enter those conditions each and every time we restore or backup a database.

Using the code

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

Objects from the SQL-DMO library

'Server object from SQL-DMO library

Dim oServer As New SQLServer2
'Database object from SQL-DMO library

Dim db As SQLDMO.Database2
'BCP Utility from SQL-DMO Library

Dim objBCP As New SQLDMO.BulkCopy2

Function for exporting data to a file from SQL Server

objBCP.DataFilePath = "c:\temp\EmployeeData.dat"
db.Tables.Item("Employee").ExportData(objBCP)

Generating SQL script for a SQL Server object

'Generate script with drop statement

Dim SQL as String = _
  db.GetObjectByName("Employee").Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops) _
  + db.GetObjectByName("Employee").Script()

Limitations

• This utility can not take backups of user roles and triggers.
• The backup file (*.SQLBackup) format is a Zip format. It can not be restored using Enterprise Manager. (If you rename this file to *.zip, you can extract it using WinZip or other Zip utilities and view its contents.)

Enhancements

• Multi-threading can be added during the backup or the restore process. This will avoid freezing of forms, specially during the backup or restore process of large databases.
• This program can be enhanced further to include a Windows service which can take regular backups for SQL Server without user interactions. Thus, this utility can be used for your regular backups.

Acknowledgments

I have used SharpZipLib (ICSharpCode.SharpZipLib.dll), a free open source Zip utility library available from www.icsharpcode.net, for zipping the backup directory. This file is included in the Bin\Release folder.

Requirements

• Visual Studio .NET 2005 (for the source code)
• .NET Framework 2.0
• MS SQL Server 2000, or MS SQL Express 2005, or MS SQL Server 2005 client components (for the SQL-DMO Library)