Email Password Remember me?  Lost your password?

• 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)

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Perfect! reachen 20:21 27 Sep '09   You are so brilliant.Thankss for sharing this. Sign In·View Thread·PermaLink Thanks for sharing this! reachen 7:23 18 Aug '09   Thats I wanted.Its a great tool,thanks a lot. Sign In·View Thread·PermaLink i am using EZManage SQL Pro to backup my sql servers itayl 4:09 27 May '09   EZManage SQL Pro provids 5 time faster sql backup using VDI http://www.futureitsoft.com Sign In·View Thread·PermaLink 1.00/5 Thanks a bunch! jm0077 10:28 27 Feb '09   Simple and straightforward. I'll be using this all the time. I have only used/tested the basic backup/restore functionality but I've had no problems so far. Installation might be improved a bit but that's an observation, not a bug. For your own feedback: - XP Pro - SQL Server 2005 Express Thanks again for your efforts and sharing. Sign In·View Thread·PermaLink Error when i Restore data Member 1471517 9:07 13 Feb '09   This message appear in the status colunm in the grid: [Microsoft][ODBC SQL Server Driver][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_Staff_UserType". The conflict occurred in database "Database", table "dbo.Staff", column 'UserTypeId'. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. How can i resolve this Problem please. and thanks Sign In·View Thread·PermaLink 1.00/5 Great article, take a look at that tool also. roby54 12:03 8 Nov '08   http://www.futureitsoft.com Sign In·View Thread·PermaLink Congratulations rolandocamachorojas 12:57 1 Aug '08   Is a perfect tool, i used very good , thanks. Sign In·View Thread·PermaLink Error while taking back up from Backup Utility ershad 5:24 24 Jun '08   hello. when i want to backup a database by this project(Database Backup) it gives some Errors. when it perform this line: db.Tables.Item(sTablesName).ExportData() it give exeption 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" please tell me what is it and what am i resolve this problem. Sign In·View Thread·PermaLink 1.50/5 Brilliant! toddwprice 11:40 3 Jun '08   What a great idea, and a nice implementation! Thanks for sharing this! Sign In·View Thread·PermaLink Doesn't work with Vista... Yogiman 8:01 14 Mar '08   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. Sign In·View Thread·PermaLink 5.00/5 New version of this utility is available Shabdar Ghata 12:10 22 Nov '07   A new version of this utility is now available on my website, http://www.shabdar.org Many issues are fixed in this version. Please check it out. Shabdar Sign In·View Thread·PermaLink Restore is empty ocram1 11:24 11 Oct '07   When I restore a database, the database is created, but there are no tables and no data. Sign In·View Thread·PermaLink Re: Restore is empty Shabdar Ghata 12:22 11 Oct '07   You need to select tables that you want to backup. When you restore, same rule applies. There is a little bug in this program. Sometimes it can not create new database. What you should do is create database manualy in Enterprise manager. And restore database without Create New Database option. Shabdar Sign In·View Thread·PermaLink Re: Restore is empty ocram1 12:48 12 Oct '07   I've checked everything, on backup and on restore, the database gets created, so that's working OK. The program even says that all the tables are created, but the tables were the data has to be restored, give an error (that the table isn't there). Will try and test it like you described. Sign In·View Thread·PermaLink Without sqldmo library zawmn83 22:12 19 Sep '07   I want to backup and restore all tables content only. Is it possible to do it without sqldmo library. I don't want to use sqldmo. Sign In·View Thread·PermaLink 2.00/5 problem in inserting row gaurav kumar jaiswal 23:28 26 Aug '07   hi frndz i m using dataset to insert row in ms access table... it is working fine but when m redebugging the program and inserting the new row old row get deleted and new entery remains..... alse if i m filling the dataadapter with data set old row get deleted... but when i m making setup of my application and rumming it after installation this problem is not there and everything working fine.... i m using vb.net 2005 does any one has solution 4 this.. well thanx in advance gaurav kumar jaiswal Sign In·View Thread·PermaLink Re: problem in inserting row majid_vb 12:34 28 Oct '07   hi i tink your problem is this: for example objcommand.CommandText = "UPDATE UNIT SET [unitName] =@UnitName WHERE unitID=@unitID" [ ] is very important in sql string when use access database bye Sign In·View Thread·PermaLink login form zaqxsws3210 18:06 20 Aug '07   how to make my login form like windows login form ? in vb ,every form has the title in top of the form (the blue on top of each form) i mean how to remove it ,create a form without it freedom! Sign In·View Thread·PermaLink 1.50/5 ek!!plzz m.rizal 17:48 20 Aug '07   i want to know,how to develop backup server to transfer data perminits? backup server Sign In·View Thread·PermaLink UnKnownError anahita_m 1:25 27 May '07   hello. when i want to backup a database by this project(Database Backup) it gives some Errors. when it perform this line: db.Tables.Item(sTablesName).ExportData() it give exeption 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" please tell me what is it and what am i resolve this problem. Sign In·View Thread·PermaLink 3.50/5 Re: UnKnownError ershad 9:18 21 Jun '08   hi anahita_m, Now i am facing same problem which you posted. Have you got any solution for this? Please reply me. Sign In·View Thread·PermaLink cannot connect ErrorProviderExtended1 ryan1214 4:53 3 Apr '07   I don't know how to set ErrorProviderExtended1,so that the program can be run,PLZ HELP! Sign In·View Thread·PermaLink Foreign_keys problem eugenia54 13:50 29 Dec '06   How can I handle the table creation order because it doesn't creates the table if there is an error of this kind, do you have a solution for this?? Foreign key 'FK__tests__3E49B918' references invalid table 'grades'. Could not create constraint. See previous errors. Thank you for your help! Sign In·View Thread·PermaLink 2.00/5 outofmemoryexception eugenia54 11:31 20 Dec '06   Hello i have the same problem, and i haven't found a solution.. did you??? Sign In·View Thread·PermaLink exception of type system.outofmemoryexception was thrown zerdust_reso 4:05 14 Dec '06   main database is giving error doesnt work would you please tell me the cause of this error? "exception of type system.outofmemoryexception was thrown" Sign In·View Thread·PermaLink

Last Visit: 3:44 24 Nov '09     Last Update: 3:44 24 Nov '09 123 Next »