10 June 2011

SQL Backup Pro for the Accidental DBA

If you've suddenly found yourself responsible for maintaining and backing up your company's servers, you're an 'accidental DBA'. If you've been dropped in the deep end, let Grant Fritchey show you through the backup and restore functionality of Red Gate's SQL Backup Pro, showing you how to schedule regular backups, compress, restore and document your backups with ease.

The most important thing you can do as a DBA is talk to your business about your backups, data loss, and disaster recovery. You need to establish a service level agreement to sets recovery times and allowed potentional data loss. That’s work you have to do on your own.

All the work after that though, SQL Backup Pro 7 can pitch in and help out. In an older article, I offered a general check list of items that you should take into account when setting up your backup and restore operations. Let’s take them one at a time and see how SQL Backup Pro makes things easier. For those of you who haven’t read my previous article, here’s the checklist for reference:

  • Full backups running on a regular basis.
  • Full backup to individual files.
  • Use CHECKSUM with full backup.
  • DBCC run as part of full backup processing.
  • When database is in FULL or BULK LOGGED recovery, run log backups.
  • Log backup to individual files.
  • Use CHECKSUM with log backup.
  • Minimum frequency on log backups is one per hour.
  • Automate the backup process.
  • Practice restore operations frequently.
  • Validate backups by restoring database.
  • Use VERIFYONLY for partial validation of backups.
  • Use backup compression when available.
  • Document backup and restore processes.

Full backups running on a regular basis

While there are any number of methods for creating backups of your database, the fundamental building block for most backup and recovery situations is the full backup. You can use SQL Backup Pro to run ad hoc full backups or schedule full backups. What’s more, you can set up backups for multiple databases at the same time. All this, and more, can be controlled through the SQL Backup Wizard. The first screen allows you to select the server you’re setting up backups for.


The second one lets you designate which databases will be backed up and what type of backup you’re going to run.


You can select a single database for backup as I’ve done above, or you can select multiple databases, including system databases. Remember, you should backup the system databases to help recovery in the event of a catastrophic failure the entire system. You can also set these backups to be a COPY_ONLY in order to avoid messing up any differential backups you’re running if this is an ad hoc backup.

Full backup to individual files

Moving to the next step of SQL Backup conveniently brings us to the next point on my checklist: full backup to individual files. While you can put multiple backups inside a single file, there are multiple issues associated with this. SQL Backup Pro can automatically name your files for you so that each backup file is unique, using the date and time of the backup as part of the name. Even better, you can have the SQL Backup Pro help you by cleaning out older backup files automatically. This also proves useful if you’re copying the databases off to a tape system or an off-site facility, as you can get rid of the files once they’ve been copied.


If you uncheck the “Name file automatically” box, you can provide a single file name. If you also uncheck the “Overwrite existing backup files of the same name” you will then get the stacked backup storage, i.e. multiple backups in one file, but I really don’t recommend it.

As you can see, I’ve also set it up to keep three backup files on the disk, and to run the deletes prior to running the backups. You have a lot of control over this clean-up operation including turning it off completely.

SQL Backup Pro offers additional functionality in that you can have it automatically copy your backup files to a network location, as an added security and as a protection against the possibility of some sort of outage of the backup location after the backup has been taken. You can also select to use multiple files on multiple disks, but these settings are a bit beyond the scope of this article. I just want to point them out.

Use CHECKSUM with full backup

Next up is using CHECKSUM when you take your backups, an option that we can handle in Step 5 of the SQL Backup Wizard (Step 4 covers some optional extras, such as compression and encryption).

The ability to run a CHECKSUM as part of the backup was introduced with SQL Server 2005, and is therefore only available for use with databases on SQL Server 2005 or later. SQL Backup 7 has a specific step for setting up verification of backups including using CHECKSUM.


This particular backup is for backing up a set of databases on a server, so you can enable CHECKSUM and VERIFYONLY, but you can’t do a test restore at the same time. Note that SQL Backup Pro is aware that you can only set this option for backups on SQL Server 2005 and greater.

DBCC done as part of full backup processing

You always want to be sure that you have good backups. Part of what defines “good” is that the backup can be recovered and, once recovered, the database can be accessed. As part of maintaining your backup processes, you should also run DBCC CHECKDB. This can be added through a T-SQL statement in front of the extended stored procedure that calls SQL Backup Pro if you want to run it before the backup If you want to run it as part of a restored database on a second server, you can use the Restore Scheduler Wizard to also enable the DBCC checks:


When database is in FULL or BULK LOGGED recovery, run log backups

Setting up a log backup in SQL Backup Pro is almost no different from setting up a full backup. The same initial backup screen is used. But, you can take advantage of a few features to make it easier. Since only databases in FULL or BULK LOGGED recovery can be backed up with a log backup, if you use the “Filter list to show only databases eligible for chosen backup type” check box, you can see just the databases that are ready for a log backup. Here’s the filter in use:


Just like full backups, you can pick one or more databases that you want log backups for, and set them up in the GUI, speeding up the process of putting backups in place.

Log backup to individual files

Again, the settings here are identical to those available for the full backup operations. It’s still best to let SQL Backup Pro name the files automatically because you will be able to tell visually when a backup was taken. This is especially useful for point in time recovery, when you need to know which file is likely to have the moment you’re looking for.

Use CHECKSUM with log backup

The same methods and approaches are applicable here as for the full backup.

Minimum frequency on log backups is one per hour

SQL Backup Pro provides a full set of mechanisms for establishing backup schedules and works with SQL Agent to set them up on your servers. The nice thing is you can control all the schedules, and monitor the backups, from a central location: SQL Backup Pro.

It’s not too hard to set up log backups for hourly execution. Getting the schedule set up is really just a matter of translating the English statement “I want log backups to run once an hour all day every day” Into settings within the Schedule Editor. I won’t discuss all the possible permutations of setting up schedules. Instead, I’ll just focus on the basics. Obviously, since you need this backup to run over and over all day long, it’s going to be a recurring backup, so that sets the first choice for you. The Frequency is, again, obvious: we want to run it every day, so that determines the next two settings. You then have to determine the Daily Frequency. You want it to occur more than once a day, so you’re going to select a value and, in this case, pick “Hours” from the drop down list. You can even put a time restriction in, if you have a data load running, that you want to avoid, or something similar. For most of us, these schedules will run until we switch off the server, but it’s nice to know you could control when it stops as well.


You can adjust this as needed to support the choices you need to make for your business. It’s also possible to make multiple different schedules and have them all active at the same time.

Automate the backup process

Automating the backup process is mostly about getting it on a schedule, as I showed above. There’s a bit more to it than that, though: making adjustments to what’s running, such as adding a step for running DBCC on the databases before the backups run, or copying the backup to a network location. In addition to automating execution, you also should automate reporting, especially on failures.

Because SQL Backup Pro provides both a T-SQL command through the extended procedures, and a command line command, you can use any standard method for automating maintenance. You can set up either type of command to run through SQL Agent. You can use DOS or PowerShell scripts to call the command-line scripts. You have a lot of options for automation with SQL Backup Pro.

Practice restore operations frequently

It can’t be emphasized enough: your backups are only good if you can restore them. This doesn’t simply mean that the backups have been validated through CHECKSUM or VERIFYONLY. It means that you know how to run a restore and you are able to run that restore under pressure. This is another place where having SQL Backup Pro running will help you immensely. Why? Because the same flexible GUI and command-line interfaces you’ve been using to set up backups are also available with the restore operations.

Using SQL Backup Pro you have a couple of options here. When scheduling backups you can set up a prompt to immediately launch the Schedule Restores wizard:


This can automate your process of running the restore processes and getting a DBCC check run against the restored database. Running DBCC against your restored database is a good way to get a validation of the database while moving the load off the production server.


To automate the restore operations just click on the link in the reminders. This will launch the Restore Database schedule wizard:


You can simply test the last full backup, or you can test a backup set including the full, differential and log. If you’re databases as split to multiple files in multiple locations, you can add additional folder locations as needed. The server doesn’t have to be the server you took the backup from, in fact, it’s much better to have a separate server for backup testing so that you’re not placing additional load on your production instance.

Next you get to define the name of the database you’re going to restore to:


I like the ability to decide how you want to restore the database too. You can simply use the existing database name, or you can restore to a specific testing database as I did using the defaults. If you don’t use the defaults, be very careful about using the “Killing any existing connections to the database.” If you’re connected to the wrong server, you could cause some serious issues in your systems.

Next you need to specify where the restored files that define the database are going to be stored. If you’re restoring to a different server, you can usually just use the default file locations for that server. If you are restoring to a server where the database already exists, you’ll need to specify new names and locations for your files:


After that you just determine if you also get the opportunity to run a DBCC process against the restored database. Best of all, you can then delete the database after it has been run through all the testing:


After that you just determine if the restore operation is a complete recovery or not and set up a schedule for it. All this allows for automated testing of your backups as well as offline consistency checks from the production systems.

Manual Restores

You can also use SQL Backup Pro to restore a database manually. You select a database and click on the restore button. The very first screen allows you to pick which of the recent restore operations you want to use on the selected database:


In addition to using the backup history, you can browse the file system to locate a backup. You can change the Server and the Database, and the list of available restore operations will shift. Once you’re all set, click Next.

The next screen checks if additional files are necessary to restore this database, based on the backup header. In the example above, the file I have selected would be the only one necessary for a restore. If I had selected a differential backup, then SQL Backup Pro would figure out which Full backup would be needed to support the restore operation. It would then show that backup as being an integral part of the restore. Depending on the type of restore you’re running, there could be a large number of additional files that get selected and included in the restore operation.


Now you need to determine where you’re restoring the database to. You can use the restore to revert an existing database to a previous state, or to create a new one. If you’re restoring to an existing database, you can kill all connections to the database automatically. You can’t have anyone connected to the database while you restore, so this is a good option, but be careful in using it. You could kick business people off the database in the middle of their work.


Where necessary, you can also adjust the location of the files that define the database. Clicking Next, lets you make a few more decisions, such as the recovery state:


As you can see, SQL Backup Pro can just finish out the restore for you, or let you set it up for a point in time recovery, or some other type of restore. Alternatively, it can help you perform those operations as well.

Validate backups by restoring database

We just walked through two options for restoring the database. You can also use the scripted options through the command line or T-SQL, just like for backups. Regardless of which method you use, restoring the database acts as a mechanism for validating that the backup is good. For very important systems, it’s probably a good idea to automate a regularly scheduled restore so that you always know if the backup is good.

Use VERIFYONLY for partial validation of backups

SQL Backup Pro supports running a restore using VERIFYONLY as part of the standard backup process if you select it. This is a great way to partially validate your backups, so it’s worth setting up a system to have this in place. You can also set up your backups to run a VERIFYONLY check when they successfully complete by using the command line options.

Use backup compression when available

Compression technology is a huge boon to the DBA. Compression on backups generally does two things: saves disk space and speeds up the process. People are frequently surprised to hear that compression speeds up backups, but it’s true. The most resource intensive part of the backup process is writing out to disk. so reducing the amount of data written to disk speeds up backups. SQL Server 2008 and above offer native backup compression, in both Standard and Enterprise editions. However, if you are running an older version of SQL Server, or you need to backup an Express edition, you will be unable to compress your backups with native tools. SQL Backup Pro can compress backups from SQL Server 2000 and later. What’s more, SQL Backup Pro compression is actually more efficient than the native compression mechanisms.

Setting up compression is extremely simple. First, you simply decide if you want compression on the backup or not. Then, you get to determine the level of compression. It’s all controlled through a very simple GUI.


Which compression setting you choose also determines how much of the CPU you’re going to sacrifice in order to save disk space. Simply choosing the max at all times is probably not always the best choice, but choosing a level of compression is difficult unless you understand what to expect. That’s where the Compression Analyzer comes in. You can see the button for it above.

Clicking that button opens the Compression Analyzer. You can run this against any database you like. Bear in mind, though, that running this on a production instance could use resources, so judicious application of the Analyzer is call for. Once you’ve picked the database, you start the test. You can stop it at any point, but you’ll have incomplete data if you do.


Once the analysis is complete, you’ll have a very good understanding of just how much compression you can expect to get from any given database. Now you can make an informed decision about whether it’s worth adding more processing power to go from 74.16% compression to 85.90% compression, or not. Once you know which level of compression you want to use, simply set that in the compression settings on the previous screen. You can use compression from the command line as well as the GUI.

Although it’s not a part of the checklist, the aim here is to meet business requirements, and one of these requirements may be encryption. You can set encryption up for your backups from the Backup Processing screen or from the command line.

Document Backup and Restore Processes

While SQL Backup Pro won’t document your backup and recovery process for you, it does act as a reporting and monitoring mechanism so you can see what kinds of backups have been running on your servers, and whether or not they were successful. For living processing and recent history you can look directly at the SQL Backup Pro user interface itself.

For example, when the GUI opens and you select a server, you can see right away a historical record of backups. This shows backups on my machine from 8AM. I don’t recommend running production backups at that time because it’s probably the start of the business day. I’m running them locally at that time so that I can see them when they occur. You’ll also note the bit of red for the database BookInventory_Virtual which is marked as Suspect. That’s because I deleted some files while the server was offline as part of an experiment. You can see that backups are not occurring there, and it stands out.


You can also drill down on an individual database and see its log or full backup operations. At the bottom of the screen you can see the Activity History for whichever server and database you select. Mine shows backups over the last several weeks, including the failures I had when I was using a restricted folder for some of the backups. You can look at other details in the same way by switching between the tabs on this screen.


Finally, you can generate reports that show which backups have run and which have not for individual servers or sets of servers. You’ll still need to document which job has been configured on which server, any special settings or considerations, and so on, but SQL Backup Pro will go a long way to helping you out.


As you can see, SQL Backup Pro is a very functional piece of software. More importantly, it’s functional specifically in the areas that are most important to getting a good set of backups configured, monitored, and tested. SQL Backup Pro provides multiple means of automation. It works with SQL Server in a very tightly coupled manner, so you’re given plenty of control over your processes. Overall, it’s the tool an accidental DBA needs to get their database backups under control more easily and efficiently.

tryitnow110x80.gif Try SQL Backup Pro – put Grant’s backup advice into practice
Try out the brand new features, including integrated backup verification. Make checking for corruption an easy step in your normal backup and restore routines. Find out more.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 11018 times – thanks for reading.

Tags: , , , , ,

  • Rate
    [Total: 8    Average: 3.8/5]
  • Share

Grant Fritchey

View all articles by Grant Fritchey