SQL Server backup utility using VB.Net

Whenever we want to backup or move SQL Server database, most of us prefer to use regular backup utility which is available through Enterprise Manager in SQL Server 2000 or Management Studio in SQL Server 2005. Limitation of using Enterprise Manager or 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 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.

Release (1.1.1) July 2011

Added a small fix for table names with spaces.

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. Here is a list of changes I have made in this program.

• Backup file extension is now .ZIP instead of .SQLBackup. This is to avoid any confusion.
• Old version was having 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 program won’t freeze during long operations.
• UI is improved to display proper status of backup and restore.
• Included GNU license.

Download Source (945KB) (new version July 13, 2011)
Download Setup (2MB) (old version)

Features

• Backup of selected objects
• Backup of selected data
• Backup of only scripts
• Backup of only data
• Backup of both scripts and data
• Supports backup from remote severs over web, LAN or even local pc.

Using Program

To backup database,

• Open database backup window from tools menu.
• Enter server name, database name, user name and password for SQL Server.
• 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 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 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 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. Remeber 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.

  To restore backup,

• Open Database restore window from tools menu.

• Enter server name, database name, user name and password for SQL Server.
• Select database backup file(*.zip) that you want to restore. Remeber this utility can only restore those backup files(*.zip) which are created by this utility itself. It can not restore regular sql server backup file.
• 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 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

Using Code

Objects from 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

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

'Generate script with drop statement
Dim SQL as String = 
 db.GetObjectByName("Employee").Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops)
 + db.GetObjectByName("Employee").Script()

Limitations

Enhancements

Acknowledgments

Requirements

.Net Framework 2.0

MS SQL Server 2000 or MS SQL Express 2005 or MS SQL Server 2005 client components (For SQL-DMO Library)

How to install utility

• Make sure that you have either SQL Server 2000 or 2005 client components installed on your computer. If you do not want to install these components, you can install Microsoft SQL Server 2005 Backward Compatibility Components. You can download it from following link,
  Download Microsoft SQL Server 2005 Backward Compatibility Components
  These components are needed for SQL-DMO library
• Download setup zip file from link provided on top of this article. Extrace and run setup.exe. It should create icons in your programs menu and desktop

How to use source code

• Download source code zip file from link provided on top of this article and run DatabaseBackup.sln solution file with Visual Studio 2008.

Special Notes

I have published this article on codeproject.com as well. Here is the link to this article.

http://www.codeproject.com/vb/net/SQLDBBackup.asp

Comments/Questions

{kunena_discuss:17}