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 Download Source (945KB) (new version July 13, 2011)
Download Source 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

SQL Server backup utility

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.


Restore Database

  • 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

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 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 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.

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


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 an 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 backup of user roles and triggers.
Backup file is in zip format. (You can extract it using winzip or other zip utility and view its content.). It can not be restored using SQL Server Enterprise Manager.


Enhancements

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 utilized for your regular backups.


Acknowledgments

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


Requirements

Visual Studio.Net 2008 (For source code)

.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

Doesn't work with vista.
It gives error saying it has stopped working as soon as I hit backup.

It connects fine to the server and displays tables and other objects but cant backup.
thanks for any input.
=> sunny (Friday 14-Mar-08 11:39 AM)
Hi Sunny,

Can you give detailed error message that you are getting?
=> Shabdar (Friday 14-Mar-08 01:59 PM)
Hi,
It just says "Backup Database has stopped working".
I have uploaded the screenshot at
http://img233.imageshack.us/my.php?image=vistaerrorod1.jpg
=> sunny (Friday 14-Mar-08 03:51 PM)
Hmmm..I think Vista is giving a more generalized error rather than giving proper description. Try running this application as administrator? It might be some privilege related error.

Can you check event viewer on your PC? It should have some description about this error.
=> Shabdar (Saturday 15-Mar-08 02:11 PM)
Thanks for the great work. Problem however; I'm backing up from SQL Server 2005 Express, user table data only, that works fine.

But when I open the zip file to restore, no objects (table names I assume) appear in the Tables tab below. Nothing appears there at all. Is this a known bug with a fix? Running Windows XP SP2, SQL 2005 Express SP2 SSMS.

Cheers!
=> Andrew (Tuesday 25-Mar-08 08:04 AM)
Great program, but I wonder if in a future version it will manage schema, i mean make a back up of a schema, or filter the tables by schema?
=> Daniel Brito (Thursday 10-Apr-08 12:20 PM)
It's already backing up schema. It's not limited to data.
=> Shabdar (Sunday 13-Apr-08 01:55 PM)
From what I have researched so far sounds like a great program! 2 questions though. 1) Is the database actually compressed?

2) Could I use this program with the task scheduler to backup every night? Basically write a batch file that starts the program executes and closes.

Thanks much
=> Mike (Sunday 13-Apr-08 11:12 PM)
(1) Yes, database is compressed in Zip format

(2) No presently this program can not be used with scheduler. Good suggestion though. I would like to release this version.
=> Shabdar (Monday 14-Apr-08 08:48 AM)
Sometimes I get error while backing up tables and program shutdown intermittently.

I backed up the Data and object structure in 2 different file and that worked

Excellent, program to back the SQL Server.
=> Sarfaraz (Monday 14-Apr-08 08:24 AM)
It would be helpful if you can give me detailed error message. If program is giving more generic error, check your Event viewer and see if it has any description.

Someone has reported that this program does not work well with Vista. If that is the case, try using it with Windows XP. I don't have Vista, so I could not test it.

=> Shabdar (Monday 14-Apr-08 08:51 AM)
Hi
Great prog. But it doesn't work when a table is into a schema.

like mike.table1 (not dbo.table1).
=> mike (Wednesday 30-Apr-08 03:52 AM)
same question here
=> Daniel Brito (Wednesday 30-Apr-08 10:11 AM)
Mister, can I use with integrated authentication ? thanks in advance
=> ae (Wednesday 30-Apr-08 09:29 AM)
Mister, why use OleDb Connection, not Sql Server Connection? any reason about it ? thanks.
=> ae (Wednesday 30-Apr-08 10:02 AM)
Whenever i am trying to take backup i click on connect button, it connects and shows all table of database but not taking
backup sometime it show this error
"Could not find file 'D:Documents and SettingsAdministratorDesktopDatabaseBackupbinDebugBackupTempAccessories.dat'."
=> Ershad Ali (Saturday 21-Jun-08 05:28 AM)
after clicking on connect button showing all tables, but when trying to take backup by click on back button it shows sometime this error also
"[Microsoft][ODBC SQL Server Driver][SQL Server]Code page 720 is not supported by SQL Server
[Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations"
=> Ershad Ali (Saturday 21-Jun-08 05:33 AM)
Hi
please give me solution for my problem which i mention earlier
=> ershad ali (Monday 23-Jun-08 09:58 AM)
Visit following link. It addresses this issue,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58290

=> Shabdar (Monday 07-Jul-08 11:24 AM)
is there a way to compare the script prepared by the objects and come with a change in any objects by keeping a source and destination server. Something like a database sync utility.
=> AK (Wednesday 25-Jun-08 03:45 PM)
when i restore database form local machine to remote server that time my table name is change with dbo,means my table name is citymaster then it display on server usrname.citymaster and this give error when i run my application what is solution
=> Amit (Saturday 05-Jul-08 04:50 AM)
Go to clsScript.vb file. Search for term "dbo" and remove it. Run project again. It should work.
=> Shabdar (Monday 07-Jul-08 11:15 AM)
Forgot to mention that there are multiple places in clsScript.vb file where you need to remove dbo.
=> Shabdar (Monday 07-Jul-08 11:16 AM)
great program keep on developing
much respect from africa
=> paul (Tuesday 15-Jul-08 03:49 AM)
Wanted to know what this error was.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 38: Length or precision specification 0 is invalid. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '[tableName]'.

It happens only on 1 of my tables, but when ive done other databases and tried to restore same problem.
=> Dk (Friday 18-Jul-08 02:01 AM)
How Can I work with windows authentication database (itegerated security)
the backup utility doesn't work on this case
=> Nisreen (Sunday 27-Jul-08 03:15 AM)
I have the same problem, ive enable mixed authentication in SQL Management Studio and nothing. what i did was i enabled the SA account in sql manager and changed the password. then you use the sa login account to go connect to the database that you want to backup/restore
=> Donovan (Monday 28-Jul-08 09:51 AM)
the program make the backup and restore but only the structure no the data, how can backup all the record for each table ?
=> roberto (Friday 01-Aug-08 10:41 AM)
Hi
Its a very helpful project can I get the same in
Csharp .net
=> Anu (Saturday 02-Aug-08 04:13 AM)
i found a problem when restoring a database with tables linked by foreign key , where when adding the table it adds all its relation regardless that the related table has not beeen created yet.
=> walid (Sunday 03-Aug-08 02:19 AM)
If you want there is another way to backup your database and restore it. You just have to add the functionality in your form to make it work.

Backup Database dbName to disk = location

and if you want to restore it

Restore database dbName from disk = location

the locations of both backup/restore need to include the database name for eg C:..Backup..dbName.bak
=> Donovan (Monday 04-Aug-08 01:09 AM)
the problem is when restoring a database with tables linked together by a foreign key, an error is caused to prevent the creation of the rest of tables because it fails to find the other table which it has a relation with.
=> walid (Monday 04-Aug-08 10:08 AM)
hi
i was use the source in sql database project with vb2005, when i use the backup and restore form, a message box appear that (Check Parameters, SQL server does not exists or invalid user name and password given . check Server name or password and username)
=> mc (Saturday 09-Aug-08 12:05 AM)
Sounds like your connection string is not structured correctly.

Server=(local);Data Source=.sqlexpress;Integrated Security=True

use that one.



=> Donovan (Monday 11-Aug-08 01:17 AM)
After i was clicking on The connect button showing all tables, but when i'm trying to get backup by click on backup button, i have this error "[Microsoft][ODBC SQL Server Driver][SQL Server]Code page 720 is not supported by SQL Server
[Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations"
=> mc (Saturday 13-Sep-08 12:59 AM)
well, when i start debugging the code source, and after clicking Start Backup, the compiler stop on this code:
"db.Tables.Item(sTableName).ExportData(objBCPExport)" in the 'ExportData' function in 'clsScript' Class.
did u have any solution,
=> mc (Saturday 13-Sep-08 01:09 AM)
Thanks for this Backup Utility!!! I translated it to Spanish so if you want it just ask... thanks again...
=> Jorge Villacorta (Thursday 18-Sep-08 11:33 PM)
Hai Shabdar,

Thanks for apps, I'm already try to used your apps on win2k3 sp2 server, but when try to backup using condition, i found that the apps just stop/close without notification, and on event viewer i'm find somthing like this:

Event Type: Error
Event Source: .NET Runtime 2.0 Error Reporting
Event Category: None
Event ID: 5000
Date: 10/29/2008
Time: 2:09:59 PM
User: N/A
Computer: xxxxx
Description:
EventType clr20r3, P1 databasebackup.exe, P2 1.0.0.0, P3 47449c96, P4 databasebackup, P5 1.0.0.0, P6 47449c96, P7 41, P8 0, P9 system.overflowexception, P10 NIL.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 63 00 6c 00 72 00 32 00 c.l.r.2.
0008: 30 00 72 00 33 00 2c 00 0.r.3.,.
0010: 20 00 64 00 61 00 74 00 .d.a.t.
0018: 61 00 62 00 61 00 73 00 a.b.a.s.
0020: 65 00 62 00 61 00 63 00 e.b.a.c.
0028: 6b 00 75 00 70 00 2e 00 k.u.p...
0030: 65 00 78 00 65 00 2c 00 e.x.e.,.
0038: 20 00 31 00 2e 00 30 00 .1...0.
0040: 2e 00 30 00 2e 00 30 00 ..0...0.
0048: 2c 00 20 00 34 00 37 00 ,. .4.7.
0050: 34 00 34 00 39 00 63 00 4.4.9.c.
0058: 39 00 36 00 2c 00 20 00 9.6.,. .
0060: 64 00 61 00 74 00 61 00 d.a.t.a.
0068: 62 00 61 00 73 00 65 00 b.a.s.e.
0070: 62 00 61 00 63 00 6b 00 b.a.c.k.
0078: 75 00 70 00 2c 00 20 00 u.p.,. .
0080: 31 00 2e 00 30 00 2e 00 1...0...
0088: 30 00 2e 00 30 00 2c 00 0...0.,.
0090: 20 00 34 00 37 00 34 00 .4.7.4.
0098: 34 00 39 00 63 00 39 00 4.9.c.9.
00a0: 36 00 2c 00 20 00 34 00 6.,. .4.
00a8: 31 00 2c 00 20 00 30 00 1.,. .0.
00b0: 2c 00 20 00 73 00 79 00 ,. .s.y.
00b8: 73 00 74 00 65 00 6d 00 s.t.e.m.
00c0: 2e 00 6f 00 76 00 65 00 ..o.v.e.
00c8: 72 00 66 00 6c 00 6f 00 r.f.l.o.
00d0: 77 00 65 00 78 00 63 00 w.e.x.c.
00d8: 65 00 70 00 74 00 69 00 e.p.t.i.
00e0: 6f 00 6e 00 20 00 4e 00 o.n. .N.
00e8: 49 00 4c 00 0d 00 0a 00 I.L.....

How can resolved this problem?

Many Thanks

Triesna
=> triesna (Wednesday 29-Oct-08 03:02 AM)
Thanks for the program.
it keeps on crushing when trying to backup a remote DB. am using win xp pro with sql server 2005 installed.
here is what i was able to collect:

EventType : clr20r3 P1 : databasebackup.exe P2 : 1.0.0.0
P3 : 47449c96 P4 : databasebackup P5 : 1.0.0.0 P6 : 47449c96
P7 : 30 P8 : 6f P9 : pszqoadhx1u5zahbhohghldgiy4qixhx

-----

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in DatabaseBackup.exe

Additional information: [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 862 is not supported by SQL Server
[Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

=> ziv (Monday 17-Nov-08 04:58 AM)
Hi
i am restoring sql server 2005 express database to sql server 2000 database using above Utility.

one column is in binary format in sql server 2005 express databse.
this column is not restoring as binary format in sql server 2000

Are athere any sugesstions

Bye
=> chandra sekhar (Wednesday 10-Dec-08 09:36 AM)

Posted: 5 years 6 months ago by vishnu101 #184
vishnu101's Avatar
hello sir , i just download backup utility , but while running the project it asking Present , user name , password , and database name ?

please guide me how to take backup and restore the database and where the concept multithreading have been used ?
Posted: 6 years 1 week ago by thantowi.jauhari.u@gmail.com #113
thantowi.jauhari.u@gmail.com's Avatar
can i use this application for sql server 2008 backup and restore ?
and i have a trouble when try to run this application.

my computer spesification
- Win 7 x86
- Sql server 2008 R2

please give me the best solution, thank you :)
Posted: 6 years 1 week ago by JK123 #111
JK123's Avatar
Hi, Please help me out, Is there any option to joining mltiple tables in the same database ? I would be very happy if you reply me asap.
Posted: 6 years 1 month ago by shabdar #89
shabdar's Avatar
Hi

Thanks for reporting this issue. I have fixed it in new version, you can download it.
Posted: 6 years 1 month ago by petebob796 #78
petebob796's Avatar
This tool looks great but I seem to have a problem with it.

One of our tables has spaces in the column names if I export this table and then import later the table is created correctly but when attempting to import the data it crashes out with an import error. I guess because the column names somewhere have not been surrounded with [] brackets. Hopefully this is an easy fix.
Posted: 6 years 2 months ago by shabdar #46
shabdar's Avatar
Weding wrote:
This utility is splendid! I really liked it on my Windows XP, where it worked without any problems.
BUT: When I now have upraded my hardware, and bought a new PC, installed with 64-bits Windows 7 the program starts up, and when I click on Tools -> Database Export it stops; The problem seems to be clsScript.vb where all references to SQLDMO fails. "Cannot get COM-classfabric for component CLSID(.....), error 80040154. The Interop.SQLDMO.dll file is in my Bin-folder (as it was on my XP-computer...). How can this code run under 64-bits Win 7?

Hi,
Make sure that you have installed SQL Server Client components on your laptop. i.e. SQL Server Management Studio or Enterprise Manager if you are using older version of SQL Server. SQL-DMO library is part of client components. If they are not installed, you may get this error. For more information see below article,

http://msdn.microsoft.com/en-us/library/aa274817%28v=sql.80%29.aspx

Once you install client components, add reference to Sqldmo.dll again.
Posted: 6 years 2 months ago by Weding #45
Weding's Avatar
This utility is splendid! I really liked it on my Windows XP, where it worked without any problems.
BUT: When I now have upraded my hardware, and bought a new PC, installed with 64-bits Windows 7 the program starts up, and when I click on Tools -> Database Export it stops; The problem seems to be clsScript.vb where all references to SQLDMO fails. "Cannot get COM-classfabric for component CLSID(.....), error 80040154. The Interop.SQLDMO.dll file is in my Bin-folder (as it was on my XP-computer...). How can this code run under 64-bits Win 7?