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.

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. 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 (941KB)
Download Setup (2MB)

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

window.google_render_ad();

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

Comments

Add New Search

Write comment

Name: Email:   Website: Title: UBBCode:         -color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge   Please input the anti-spam code that you can read in the image.

meena |118.94.102.xxx |2009-03-29 22:58:22 hi , database backup is working perfectly,,now i have taken backup from server, and want to restore on local db, bt at the time of restore when i choose option -drop existing database, n click start restore, the error occur is -cannot drop the database"DB Name" bcoz it is currently in use... i hv checked,,DB not in used anywhere at this time,as i m working in local... at the same time when i click the option -append to databsae,,,,then it go perfectly... why its happening ??? thanx 4 any suggestion ReplyQuote 2 0

Yusuf |59.177.101.xxx |2009-04-01 09:29:08 Very beautiful program ReplyQuote 1 0

Mark Chimes  - Error running V.1.1.0 |202.173.179.xxx |2009-04-21 19:10:52 Hi Shabdar, Nice little app! I have been using it on and off for quite some time now. I installed it at a client site the other day onto two IBM laptops running Windows XP Pro. One both computers, when I run the app and attempt to backup some data, I receive an Unhandled Exception error. "An error occurred creating the form. See Exception.InnerException for details. The error is: Retrieving the COM Class factory for component with CLSID {xxx} failed due to the following error: 80040154." Can you please advise what I can do to resolve this problem? (I should mention that tyhe data is in a SQL Express 2008 database). Regards, Mark Chimes ReplyQuote 0 0

Anonymous |24.233.186.xxx |2009-06-20 15:19:26 First of all, magnificent utility! Love it! Because I restored into a fresh database (i.e., no tables or ANYthing), I found some tables weren't restoring because the user defined types that were used in them didn't exist. (If I had restored into existing database, probably wouldn't have noticed...area unlikely to change...) So, figured "no problem"...and I just ran restore again. But...still had those pesky problems with the missing tables -- and, of course, any FK that relied on those tables or fields. Looked more carefully and noticed that the user Defined Types tab showed my stored procedures, not my userdefineddatatypes. Think in the File: clsScript.vb Function: GetUDDNames - Line where it says: DT.Rows.Add(New Object() {db.StoredProcedures.Item(i).Name, True}) should be be replaced with: DT.Rows.Add(New Object() {db.UserDefinedDatatypes.Item(i).Name, True}) Otherwise, I'm entranced with your utility. We're running on remote server and so many of the other backup possibilities required changes that our remote host wasn't thrilled to do for us. Thanks for your generosity in publishing this! ReplyQuote 0 0

gabriel kofi kyeremeh  - project |41.210.29.xxx |2009-06-27 05:50:54 :0 I am doing a project on vb.net in my school and i need some assistance right away or else i will be in serious trouble, please help!! is about how to create a table from sql and manupulate it in a set form in vissual studio. ReplyQuote 0 0

BK  - Restore Individual Objects |206.107.152.xxx |2009-08-21 12:52:28 I did a backup of 2 tables from DB1 without a problem. I then did a restore to DB2, choosing all 3 check boxes (structure, data, and delete) and then selected the append option. I then opened both tables in DB2 and made data modifications. I then ran performed a backup of DB2. After which I did another restore, this time to DB1, using the same options as the first restore. But this time, I went to the objects window and checked one of the tables and left the other unchecked. I ran the restore and when I opened the restored tables the data changes that were made in DB2 were in DB1 for both tables. So, although I only had only one table checked to restore, it ended up restoring both tables. I can still accomplish my goals with this tool and thanks for putting it up here for us, but I wanted to make this issue known, so it could be looked into in case someone else will require that option to work properly. ReplyQuote 1 0

Sumit Joshi  - Great Application - Ready to use |122.169.30.xxx |2009-09-20 00:13:34 :cheer: Great Work done... Its very easy to use and performance is great. Also the UI is great. No need to worry about. Againg thanks. by Sumit Joshi ReplyQuote 1 0

professional user  - select all |196.205.88.xxx |2009-09-30 09:38:22 hey great work but i found that i should use select all button in users tab i want to make it works automatically but i can't find any code behind Select all button give me the code for select all please thanks again i have some features and fixed things will add my project here very soon when I'm finish ReplyQuote 1 0

professional user |196.205.239.xxx |2009-10-02 17:33:38 WHY THERE IS NO ONE ANSWERED MY QUESTION?? I NEED IT URGENT PLEASE ReplyQuote 0 0

Kailas |121.242.76.xxx |2009-10-13 09:51:29 I am getting error while restoring database please any one knows that Error is following is my code snippets try { StreamReader sw = new StreamReader(restoreFilePath + "\\SQLChromeraSystemDatabase.sql") ; string strCmd = sw.ReadToEnd().Trim(); sw.Close(); //Import tables if (string.IsNullOrEmpty(strCmd) == false) { objDatabase.ExecuteImmediate(strCmd, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, strCmd.Length); //Import data } } catch (Exception ex) { throw ex; } Error is: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Length or precision specification 0 is invalid. please get back to if do you have any clues ReplyQuote 0 0

Kailas |121.242.76.xxx |2009-10-13 09:52:24 I am getting error while restoring database please any one knows that Error is following is my code snippets try { StreamReader sw = new StreamReader(restoreFilePath + "\\SQLChromeraSystemDatabase.sql") ; string strCmd = sw.ReadToEnd().Trim(); sw.Close(); //Import tables if (string.IsNullOrEmpty(strCmd) == false) { objDatabase.ExecuteImmediate(strCmd, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, strCmd.Length); //Import data } } catch (Exception ex) { throw ex; } Error is: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Length or precision specification 0 is invalid. please get back to if do you have any clues ReplyQuote 0 0

Hal |192.234.90.xxx |2009-11-03 11:35:33 I was just wondering if you ever provided a fix for the inability to restore when tables are linked via foreign keys? Thanks ReplyQuote 0 0

Prabhakar Kasi  - Support for Sql Server 2008 |24.5.122.xxx |2009-11-26 18:55:44 This is a wonderful utility it comes handy to transfer db to my website. With sql 2005 it worked great. Wondering abt your plans for sql 2008 ReplyQuote 0 0

SVK  - re: Support for Sql Server 2005 |59.181.132.xxx |2010-01-14 08:35:38 Prabhakar Kasi wrote: This is a wonderful utility it comes handy to transfer db to my website. With sql 2005 it worked great. Wondering abt your plans for sql 2008 the code is for window application can it be used ro web application i need backup utility fr webbased aplication using sql2000 ReplyQuote 0 1

Powered by !JoomlaComment 4.0 beta2