Voice of the DBA

Category: Blog

Working on the Road
I spend a decent amount of time on the road, traveling to events. Last year I think I spent 50 nights in hotels for work. That’s a fair amount, and it can be challenging to get everything I need to accomplish done in those situations. I’ve gotten good at ensuring my laptops are setup, I have cloud copies of information, or VPN access to files. While I miss my multiple monitors at home, I can work on the laptop for a week without too many issues.

However there’s one thing I often do before trips: I prep. I get most of my tasks scheduled; I pre-build and prepare newsletters. In short, I try to minimize the work I need to do on a deadline when I’m traveling.

This week I have a slightly crazy week.
- UPDATE Monday, I was supposed to be in Fort Collins, CO, with the State Knowledge Bowl competition for my son. However he had other work to do and decided to focus on his classes, so I went skiing 😉
- Tuesday, Fort Collins, CO – morning work in the hotel, afternoon Knowledge Bowl competition, evening, a bit more work.
- Wednesday, home office – Daily work and routine
- Thursday, Spokane, WA – Hotel working
- Friday, Spokane, WA – holiday, volleyball tournament for my daughter.
There’s a level of craziness, with changing locations and working sporadically this week. Tuesday should be a holiday, but I’ll have to work around things. I’ll actually try to do some work on Monday around the competition to minimize the disruptions. I have some meetings I couldn’t easily move, so I elected to just work around Tuesday.

Wednesday will really be my only normal work day, though I have an evening flight to Spokane, so I will feel some pressure here.

Thursday is a travel day for my daughter. I’ll be in the hotel working while she wanders around Spokane with her team.

Friday is a tournament, afternoon wave, so if I haven’t finished everything I need, I’ll be working a bit in the am here.

Not my kind of week, but I have these happen at times.
March 15, 2016
Finding Strings

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

A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.

CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

GO

INSERT dbo.MyTable

    ( mystring )

VALUES

    ( ‘This is a 7’ )

, ( ‘There is a 7 in this string’ )

, ( ‘Why must 7 always be here’ )

, ( ‘No 7s here. JK’ )

, ( ‘My 7s here’ )

, ( ‘An 7s here.’ )

, ( ‘On 7s here.’ );

GO

SELECT

    ‘SetPosition’ = SUBSTRING(mystring, 4, 1)

, ‘FindThe7’ = SUBSTRING(mystring, CHARINDEX(‘7’, mystring), 1)

FROM

    dbo.MyTable AS mt;

DROP TABLE dbo.MyTable;

March 11, 2016
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
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