Voice of the DBA

Author: way0utwest

Converting SQL Backup Files to MTF

I got a copy of some backup files recently and needed to restore them. However, I don’t have SQL Backup installed on all my instances. It’s not a big deal to install it, but since this is a one-off, I decided to just decompress the files.

The documentation for SQL Backup Pro notes that you will have the file converter installed with your SQL Backup installation. The path given is the default, but if you’re like me, you need to track down the actual path. Mine is actually on my e: drive, which means I need to use the full path to call the program.

The converter is a command line tool, so I need to open a command prompt and then type the path to the file. I could to this many ways, but I started by going to the folder with my .sqb files. I then typed this:

This actually worked, but it then put the decrompressed files in the folder with the SQL Backup utility. I then adjusted the command to this:

“E:\Program Files\Red Gate\SQL Backup 7″\sqbconverter FULL_
INS1_SQLServerCentral_20160210_000500.sqb H:\SQL Server\Backup\sqlservercentral_20160209.bak MyPassword
This command actually is seen in the help if you type sqbconverter with no parameters. The format is the .exe, then the input file (the .sqb), the output file (the .bak) and the password.

The utility gives you the progress as the files are decompressed.

Depending on the threads used, you’ll end up with multiple files. In my case, 7 files.

Now I can restore each of these as a normal, striped backup.

March 8, 2016
The Data Driven Event

This Thursday, March 10, 2016, at 10am, EST, MIcrosoft will host Data Driven, a live virtual conference. This is slated to be the launch event for SQL Server 2016, though there has been no information released about whether this is also when the platform will RTM. To date we have had a number of CTPs (CTP 3.3 is the latest), but no Release Candidates (RC) (as of the time of this writing), which we usually see before the final product is released.

However the CTP has been running well for me. The areas I’ve been testing and looking at seem to work well and I’m rather excited about this version of SQL Server. I don’t think I’ve looked forward to a new version this much since 2008. The in between versions have been somewhat meh, without a substantial number of changes. SQL Server 2012 was better than the R2 and 2014 releases, but still, a limited number of changes.

However SQL Server 2016 really feels like Microsoft has refocused on the platform, made substantial engineering investments, and is really changing the capabilities of the product. From the Query Store to Always Encrypted to the Stretch Database, we’ve got quite a list of thing to learn about SQL Server 2016. I know I’ve been working through some of these areas and each time it seems I’ve caught up with the changes, there are more items to learn.

This event is scheduled for two hours on Thursday, so it’s not an all day commitment. I’m sure many of you aren’t looking to upgrade to SQL Server 2016 right away, but this is a chance to learn a bit about what you may look forward to in the future, as well as an opportunity to get excited about the new Data Platform changes from Microsoft. Satya Nadella, the CEO will speak, as well as other executives, and I’m sure, a number of demos. So consider scheduling a meeting for yourself this Thursday and getting excited about the future of data on the Microsoft platform.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

March 8, 2016
Saving Time Through Automation

Recently I ran across a nice post from Joey D’Antoni about not installing SQL Server from the GUI. It’s a good post, and it reminds me that I should be using this technique each time I build a new VM, but what really struck me was the cartoon from XKCD he linked in. Here it is:

When I look at the cartoon, it seems that it’s easy to waste a lot of time on repetitive tasks. And it is. This is one of the great pet peeves of programmers who look to automate tasks so that they don’t have to worry about repeating them. The power of computers is in repeating instructions over and over again, something that humans struggle with. We make mistakes, we get bored, and this isn’t a good use of our time.

However the cartoon distorts things a bit since the time lost is across 5 years. I don’t know how many tasks I have that take 5 seconds, but I do have some 30s tasks, and certainly a few that take a couple minutes. Even if I do them daily, that means if I automate them in less than a day, I’ve saved time. However, some of my automated tasks still require me to spend some time on them, so my savings aren’t always realized.

That doesn’t mean they aren’t worth automated in some way. I work on an Excel sheet almost daily, and each time I do, I change dates, which takes 10-15s to do. I spent about 5 minutes automating this, so I save time, but more importantly, I reduce tedium and stress, and that can be one of the best reasons to automate. Those simple tasks we dread might be the best items to automate. Of course, keep things simple, since you have to support your automation over time.

There are also the tasks that are better handled through automation. Many of us know that checking to see if all our SQL Agent jobs succeeded or if there are any errors in log files is something best handled through automation. Those simple tasks that many of us may consider part of our jobs are items that are easily automated, and more importantly, if those are the only tasks we perform, it’s entirely possible we’ll be considered redundant and obsolete over time.

I would urge you to learn to automate those pieces of your job you do regularly. Use Powershell, T-SQL, Agent, whatever, but make your job less stressful and less tedious. It’s good for your career, for your job retention, and it’s fun. Besides, I’ve never run out of work, so removing tedium from my job hasn’t resulted in me being less valuable as a DBA or developer.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

March 7, 2016
Restoring a Striped Backup–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I uncompressed some SQL Backup Pro files. Since multiple threads were used to make the backup, I ended up with multiple files in my file system, as shown here:

Each of these is part of a striped backup, a piece of a backup file. To restore the backup, I need all the files to reassemble the backup. This is fairly simple, but you should be aware of how this works and how to perform a restore.

In my case, you can see I have 7 files for each database. They are the same name with an _0x at the end, with x being the number of the file.

To restore these, I go to SSMS and click the restore choice. That gives me my restore database dialog, where I can select that I’m restoring from devices. As you can see below, no devices (files) are selected.

I then click the ellipsis to get a dialog allowing me to add files.

Hopefully you’ve done this before, and you can click “add” to add files. You need to naviate to the location of your backup files if it isn’t the default.

Next you can select the files. Holding down CTRL, I can multi-select files.

Once I pick them, I click OK and then I see them all in the device dialog.

Now I click OK and SQL Server reads the headers, and I end up with a single database to be restore, as shown below.

Now, I can click OK, and often do in development areas. HOWEVER, if you are doing this in production, please, please, please, click the Script button instead. You’ll get a new query window, and you can cancel out of this dialog to see the code.

From here, you should click “Save” and save this, then execute it.

As you can see above, the statement is simple. List each disk file, separated by a comma. Then the rest of the RESTORE statement is a standard restore.

SQLNewBlogger

This is a fairly simple task, one I’ve done dozens of times, but it’s worth practicing. If you want to write about it, what happens if you’re missing a file? What if you change the order of files? This was a 10 minute blog, and it’s a good chance to play and practice your restore skills, which are as important as anything you do as a DBA.

March 7, 2016