Click here to monitor SSC
  • Av rating:
  • Total votes: 7
  • Total comments: 0
Grant Fritchey

SQL Backup Pro for the Accidental DBA

10 June 2011

If you've suddenly found yourself responsible for maintaining and backing up your company's servers, you're an 'accidental DBA'. If you're someone who has been dropped in the deep end, let Grant Fritchey show you through the backup and restore functionality of Red Gate's SQL Backup Pro (new version 7.0 recently released), 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.

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.

Select the databases to backup

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.

Configure backup file settings

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.

Configure backup checks and email notification

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:

Specify verification for the restored database

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:

Select backup type and databases

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.

The SQL Backup Pro schedule editor

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:

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.

The Schedule Restores wizard

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

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:

Naming the database 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:

Specifying 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:

Specify Verification options for the restored database

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:

Select the destination server and backups to restore

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.

Running a DBCC process against the restored database

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.

Choosing database restore options

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:

Configure restore options for the destination database

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.

Choosing the level of compression for your backup

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.

SQL Backup Pro Compression Analyzer

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.

Where backups are and aren't occurring

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.

Backup Activity history

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.

Conclusion

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.

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.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 7 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.