Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Article

SQL Server backup utility

Rate me:
Please Sign up or sign in to vote.
4.88/5 (49 votes)
3 Jun 20064 min read 283.7K   13.7K   159   58
Restore/backup selected objects in SQL Server.

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

Image 1

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.

Image 2

  • 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

VB
'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

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

Generating SQL script for a SQL Server object

VB
'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)

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Comments and Discussions

 
QuestionBackup Pin
Saark Computers23-Jan-15 19:17
Saark Computers23-Jan-15 19:17 
Generalhi Pin
sajjiamo9-Mar-11 19:01
sajjiamo9-Mar-11 19:01 
Generalhelp-help Pin
aseman_shab31-Dec-10 20:56
aseman_shab31-Dec-10 20:56 
GeneralKeep it up Shabdar Pin
Sayed Sajid31-Jan-10 2:21
Sayed Sajid31-Jan-10 2:21 
GeneralPerfect! Pin
reachen27-Sep-09 19:21
reachen27-Sep-09 19:21 
GeneralThanks for sharing this! Pin
reachen18-Aug-09 6:23
reachen18-Aug-09 6:23 
Generali am using EZManage SQL Pro to backup my sql servers Pin
itayl27-May-09 3:09
itayl27-May-09 3:09 
GeneralThanks a bunch! Pin
jm007727-Feb-09 9:28
jm007727-Feb-09 9:28 
QuestionError when i Restore data Pin
Member 147151713-Feb-09 8:07
Member 147151713-Feb-09 8:07 
GeneralGreat article, take a look at that tool also. Pin
roby548-Nov-08 11:03
roby548-Nov-08 11:03 
JokeCongratulations Pin
rolandocamachorojas1-Aug-08 11:57
rolandocamachorojas1-Aug-08 11:57 
GeneralError while taking back up from Backup Utility Pin
ershad24-Jun-08 4:24
ershad24-Jun-08 4:24 
GeneralRe: Error while taking back up from Backup Utility Pin
czekanm20-Jan-11 2:19
czekanm20-Jan-11 2:19 
GeneralBrilliant! Pin
toddwprice3-Jun-08 10:40
toddwprice3-Jun-08 10:40 
GeneralDoesn't work with Vista... Pin
Yogiman14-Mar-08 7:01
Yogiman14-Mar-08 7:01 
NewsNew version of this utility is available Pin
User 99267422-Nov-07 11:10
User 99267422-Nov-07 11:10 
GeneralRestore is empty Pin
ocram111-Oct-07 10:24
ocram111-Oct-07 10:24 
AnswerRe: Restore is empty Pin
User 99267411-Oct-07 11:22
User 99267411-Oct-07 11:22 
GeneralRe: Restore is empty Pin
ocram112-Oct-07 11:48
ocram112-Oct-07 11:48 
GeneralWithout sqldmo library Pin
zawmn8319-Sep-07 21:12
zawmn8319-Sep-07 21:12 
Questionproblem in inserting row Pin
gaurav kumar jaiswal26-Aug-07 22:28
gaurav kumar jaiswal26-Aug-07 22:28 
AnswerRe: problem in inserting row Pin
majid_vb28-Oct-07 11:34
majid_vb28-Oct-07 11:34 
Generallogin form Pin
zaqxsws321020-Aug-07 17:06
zaqxsws321020-Aug-07 17:06 
Generalek!!plzz Pin
m.rizal20-Aug-07 16:48
m.rizal20-Aug-07 16:48 
QuestionUnKnownError Pin
anahita_m27-May-07 0:25
anahita_m27-May-07 0:25 
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.


General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.