SQL Server backup utility VB.NET

SQL Server backup utility

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. 

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.