Disaster Recovery

How do I use SQL Backup?

You want to make sure that that data in your database is safe. One way to ensure this is to have a good backup program in place. Steven S. Warren shows you how to use SQL Backup.

I recently had the opportunity to work with a piece of software for my SQL Server backups. The product is called SQL Backup and it is made by Red Gate Software. With SQL Backup, you can perform the following:

  • Compressed backups
  • Perform faster online backups
  • Encrypt SQL Server backups with 128 bit Rijndael encryption
  • Command line interface

Installing the tool is a snap. Simply run the SQLBackupSetup.exe and the Welcome window appears (Figure A). Next, accept the license agreement and choose the Destination Folder for your installation of SQL Backup (Figure B).

You now have the option of installing an extended store procedure that allows you to perform backups remotely (Figure C). You are now ready to install the application by clicking Install. Once the installation is complete, select the Launch SQL Backup checkbox and click Finish, as shown in Figure D.

Next, you have to activate the server with your registration key as shown in Figure E. If you do not activate the server, you can use it for 14 days.

You can perform a backup using native TSQL or using SQL Backup’s user interface. Let’s first go over how to perform a backup and restore using SQL Backup’s UI.

To begin, launch SQL Backup from the Start menu (Start | SQL Backup | SQL Backup) as shown in Figure F. Next, right-click on a database and choose either backup or restore. The UI also has Backup and Restore buttons for you to use. The wizard walks you through a simple process to backup or restore your database. During the wizard, you have the option to choose a Full, Differential, or Transaction log backup as well as the ability to encrypt your data.

On the Backup Options window, you can backup the database to a single file, split backup files, and/or produce mirrored backup files to make multiple copies of a backup file at the same time. You can also choose your compression options. The UI is very intuitive and just requires you to do some due diligence and become familiar with the product.

The restore wizard is just as intuitive as the backup wizard is. Let’s move on and look at how to perform backups using TSQL (Figure G).

When you run the Backup or Restore wizard from within the software, you have the ability to choose your scripting format. If you were going to run the query in Query Analyzer, you would want to choose the Extended stored procedure format. As you can see, this software is very flexible and allows a host of options.

In the future, I plan to write several articles on my favorite SQL Server tools. Download a trial copy of SQL Backup today and see if it fits your needs.

4 comments
killmenot
killmenot

Our company tried to use Red Gate in the past but we think it's too heavy. So we didn't buy it. It has too many options from my point of view. We wanted to find and use something lightweight and we found "SQL Backup and FTP" (http://sqlbackupandftp.com/). We were very surprised, it so easy to use. BTW, Red Gate has nice compare tools. I'd like them:)

kolawolesodimu
kolawolesodimu

how do i aasign right to create view bcos i can rename, update, or create view again

floediden
floediden

how do you write sql scripts to backup databases

fuller.artful
fuller.artful

I agree with you 100%. In my experience, SQL Backup is three times as quick and the resulting file size is three times as small as with the MS backup routine. I even tried to RAR the resulant 6 GB file and it took an hour and saved only 500 KB. What you did not touch on, and what I was hoping for tips on, is installing SQL Backup for clustered servers. I have downloaded some documents from Red Gate but I'm still fuzzy on this topic. Thanks, Arthur

Editor's Picks