Brad Hoff's Notes about SQL

Category Archives: Tools

Several people have asked me to post my script for automatically cycling through each database in an instance, restoring a virtual hyperbac database, performing consistency checks, and then e-mailing out the results, in reference to my previous post.

I am sorry it took me so long to get this put together, but pulling it out of my environment was the tough part. I have a lot of infrastructure built up around this, so I had to extract the parts and try to cull it into one consistent script. I have not tested this script very thoroughly, so please use with caution. Make sure it works in your environment. Keep in mind that you must have hyperbac already installed and setup to make proper user of this script.

Microsoft added Intellisense to SSMS in 2008 and many of us have come to love this feature. However, in 2012, one of my co-workers noticed a problem (from his perspective) in the Intellisense completion action. The default configuration of SSMS 2012 is such that you can no longer complete an Intellisense selection using a “Space”. I never noticed this issue because I use “Tab” to do my completions, which still works correctly. My co-worker was further annoyed to find that there is no way to change this in the SSMS “Tools–>Options” dialog, as you might expect. However, there is a fix available. As noted in this StackOverflow Article, if you hit CTRL+ALT+SPACE, the input mode is changed and space works once again. I hope this helps someone who may have been frustrated by this.

There are many articles on the Internet explaining why it is important to check the consistency of all of your databases on a regular basis. If you are not made aware of corruption in your system as soon as possible, it is likely that you could miss a growing problem until you are in a crisis. However, even though we know consistency checking is important, it can be hard to find a time when you can afford the large performance hit upon your production machines, due to the resource intensity of CheckDB.

So, rather than run CheckDB on my production boxes, I made the choice to take the latest full backup and restore it to a test server, and run CheckDB against that every week. While this does not avoid the possibility of corruption happening since the last full backup, it does let me know that, at the very least, my last full backup is safe. I also get the added built-in benefit of testing my restore strategy each week.

However, this poses other problems. Some of my databases are quite large, and doing a full restore takes a considerable amount of time and disk space. This is where Red-Gate comes in.

Red-Gate recently added a tool to their arsenal called SQL Virtual Restore. This tool allows the DBA to use Red-Gate’s HyperBac Filter Driver to allow you to restore a virtual copy of a database using far less space, spending less time, and all utilizing native SQL Backup and Restore syntax. SQL Virtual Restore references the backups you supply to it, creates custom “indexes” on your local disk (to help Red-Gate transcend your backups quickly), and creates small empty structures for your files (virtual database files). This allows you to have an unaltered database backup serve your reads and the virtual database files serve your writes. The savings are truly amazing:

With this tool at hand, I next needed to get my scripts written.

I already have a harness for this sort of maintenance – I have an instance dedicated to DBA tasks. Running in this instance, are different jobs that perform tasks at different intervals; every 15 minutes, every hour, day, week, so forth. These jobs fire off SSIS packages which collect information about my instances in my environment, such as which backups ran successfully in the last week, what security changes have occurred, which databases have datafiles which are going to grow soon, which indexes need attention, which jobs are running longer than usual, and so forth; all based off of a list of instances that I update whenever I add or modify a SQL instance to my environment.

So, since I already have all of the information I will need to write my restore and CheckDB scripts, I am all set. I created a stored procedure that would:

Loop through my list of Instances

For each instance, loop through each database

For each database:

Gather the list of the backup files from the latest successful full backup

Verify that the most recent backup is not “too old” (comparing against a configurable value)

Gather all datafile logical names (as of the time the backup was taken)

Copy those backup files locally

Create a native syntax restore script, using dynamic SQL, from the list of backup files and specifying a local location for each virtual data file (one VDF for each logical file in the backup)

If the restore was successful, execute DBCC CheckDB against the newly restored database, saving all results to a table

Once the DBCC CheckDB has been performed, I check the results in the table to discover if there were any problems found, noting the result

I then drop the Virtual Database and the local copy of the backups

Once every database in the instance has been checked, the script then e-mails me with the results, each database is listed, along with whether the check was successful, found issues that I need to look into, if the latest backup was too old, or if the restore failed (along with any errors)

Using this method, I am able to consistency check all of my databases every week without ever having to lift a finger. I look over the e-mails I get from the script, for each Instance, and make sure everything was successful. If an error ever pops up, I know that I only have to spend my time on the databases that are actually having problems.

This has been a huge time-saver for me and has given me additional peace of mind.