Voice of the DBA

Category: Blog

  • Sabbatical Day 2- Red Rocks

    My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

    Day 2 of the sabbatical was the opposite of day 1. Instead of an early start, it was a late one. I was up early, with PT and some chores, but the sabbatical portion of this was at night. I had a 6pm class at Red Rocks community college.

    I left early, needing a student ID and wanting to get oriented. However traffic was a mess and my 45 min drive to the other side of town ended up taking more like 70 and I was too late to get an ID. I’m not sure how often traffic is like that, but part of today and tomorrow needs to be finding a better route to that side of town. A few accidents, and the radio said traffic was worse than normal, but I’m still concerned.

    Red Rocks Community College is nestled in the foothills just a little SE of Golden. It’s a beautiful spot, looking out over the mountains. I should have taken pictures, but I was a bit frazzled when I got there and it was confusing as to where the classroom was. I ended up driving around campus a bit before I found the location.

    Apparently the Fine Woodworking program at Red Rocks is very well known. It was one of the few I’d seen nationwide that offered regular programs. However the instructor polled people since he regularly gets people driving from Wyoming, down South, even out west near Glenwood Springs for classes.

    There’s a nice shop and I’ll get some pictures next time I go. It’s large and there were actually two classes in there at the same time. A turning class and my intro to woodworking. However noise wasn’t an issue last night since things are spread out.

    Our class project is to build a small and table. The sample he showed was nice, tapered legs, beveled top, held together with mortise and tenon joinery. However we’re not using power tools, but hand tools. Slightly disappointing to me as I wanted to help get some skill with other items, but not too bad. It will be good practice since I’ve not really built anything that didn’t require glue or screws and this won’t. Pinned dowels to hold the tenons in there.

    It doesn’t sound like a lot, but I’m sure it will be. We have roughly eight weeks to learn how to flatten a board with a hand plane (our first project), then sharpen tools, then build mortise and tenons, and finally the table. It’s an interesting class, and while I don’t worry too much about the grade, I do want to do well and go on to other classes.

    Last night we learned about planes, took one apart, learned how it works and then started seeing how to sharpen the iron. It was mostly listening and watching, which made for a long night on hard shop stools. By the end, I was exhausted and I’ll certainly keep that in mind for the future. I might need to stick with classes that are one night a week if I continue.

    Still, it’s exciting, and somewhat amazing to see how flat and smooth the instructor got a piece of wood with a plane. No power tools, no sandpaper. Hoping I learn how to do that.

  • Is PowerShell worth the time?

    This is a good question for me to ask. I’ve been learning it, and working through examples in my PowerShell Challenge, but it wasn’t until months later that I found a real reason to try PowerShell.

    When I realized I had lost 12 databases, I could have spent around 10 minutes attaching them all manually. That felt like a pain, and a waste of time. I could have probably scripted something in T-SQL, specific to this situation, in about 15-20 minutes tops.

    However scripting something general, like an attach T-SQL script that figures out which databases to attach based on a path, would have taken me an hour, perhaps more. It would have been a bit of a chore to test and debug, and I’d worry it wasn’t rock solid because I’d be trying to work inside, and outside, of T-SQL.

    Powershell felt like a better fit, and this seemed like something built for PowerShell and SMO. I spent around 3 hours one night searching around and writing the script. This included some false starts, and some experimenting with things. I spent at least 30 minutes trying to restore the mdf before I realized that I should be attaching it. Then it took me a bit to work through some examples I’d seen about attaching that didn’t make sense. Part of it was my unfamiliarity with PoSh.

    However at the end, 3 hours+ in, I had a script that attached databases quickly. I tried it on a few VMs and it worked great. I dropped it on GitHub, so it’s always available to me on multiple machines, and I can clean it up over time.

    I can also share it. When I googled around, I didn’t find a good, generic script like this. I also had to compile information from multiple places. Writing this series of posts allows me to potentially help others, but also it allows me to showcase a bit of knowledge and double check myself. As I broke the main script down, I also rewrote pieces, cleaned it up, practiced a bit of programming, and learned more about PoSh. I bet if I had to re-write this script, it would take me an hour to 90 minutes, tops.

    Was it worth it? If I never run this again, that’s debatable. However if I ever need it again, I think it’s easily worth it. Especially with the knowledge gained.

  • Attaching All Databases with PowerShell–Attaching Missing Databases

    I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.

    In the last post, I had a script that matched up databases with the mdf files in a folder. That’s good, but that’s actually the opposite if what I want to return. I want to return the files that aren’t matched up.

    To do this, I add a few variable to my script, re-setting it for each loop of a file in my folder. I do this inside my test for the extension (shown), so I’m not executing this if I don’t need to.

    if ($file.Extension -eq ‘.mdf’)
    {
    if ($debug -eq 1)
    {
    write-host “MDF Files: ”  $file.name
    #end debug
    }

        # Reset our flag for each file
    $found = 0

    The last two lines are what I added. We set this to 0, or false, because we assume we haven’t found a database that matches this file by default. That way when we do find a file, we can trip the flag.

    The next step is to set that tripwire. Inside the loop, where we check for the file matching a database file, we add a reset of this flag.

    if ($file.FullName -eq $dbfile.FileName)
    {
    $found = 1

    Since we will check this file against every database, this logic allows the flag to be set, and it doesn’t get reset for this physical file. Any databases we check that don’t match this file won’t reach this point.

    The last step is the other end of the file loop. After we’ve left the database loop, we check to see if our file was found. If it’s not ($found is still 0), then we can do work. I’ve included the end of the foreach and the end of the mdf test for reference.

        # end foreach
    }

    if ($found -eq 0)
    {
    # attach this file
    if ($debug = 7)
    {
    Write-Host $file.Name “not found”
    #end if debug
    }

    #end if found = 0
    }

    There’s a comment placeholder in there to show the action we need to take, and there’s a debug to print things. Let’s set debug to 7 and run this.

    attach_n

    That’s bad. Certainly I noted that my test databases (db1, db2, db3) were detected. These I detached manually to play in a test environment. However why are my system objects there?

    I looked through the code and realized it’s because I had this line:

    | Where-Object {$_.ISSystemObject -eq $false}

    I’m ignoring system objects in my scan, but I don’t want to do that. I actually have these databases, so I removed that Where-Object call. Then I get this:

    attach_o

    That’s what I want to see. Now I have a list of files to attach, let’s work inside SQL Server.

    Attaching Database Files

    I have my .mdf files, but I also want my ldf files. I know these fit a pattern from looking at the files. Since I haven’t changed anything from the defaults, I can exploit that pattern. If you change things on your systems, make sure you keep a pattern.

    I did some googling, and found that the AttachDatabase method takes a few parameters. However one of them is a StringCollection so I need to create that.

    $dbfiles = New-Object System.Collections.Specialized.StringCollection

    Once I have this variable, I can then add my mdf file. The FullName property includes the path, and I call the Add() method.

    $dbfiles.Add($file.FullName) | Out-Null

    Now, I need a few more things. I need the log file and the database name. The database name is first, mostly because I thought of it first.  The BaseName is just the name of the file. I found that out by using my debug clause and writing out the various properties until I got the one I wanted.

    #get database name
    $dbname = $file.BaseName

    The next step is to get my log file. I can use the basename, along with my folder path, and include my log pattern. Once I build this file, I add it to the collection.

    # get log file, assuming same basename as mdf
    $logfile = $folder + “\” + $file.BaseName + “_log.ldf”
    $dbfiles.Add($logfile) | Out-Null

    I added a logging item, which will always run, to my script as this is output I’d want to see.

    “Attaching as database (” + $dbname + “) from mdf (” + $file.FullName + “) and ldf (” + $logfile + “)”

    Now we attach the database. I’ve seen some code that had other parameters, but this worked well for me. I captured this in a try..catch block, mostly because it failed early on and this allowed me to see the whole exception. Some of what was shown from PoSh was truncated, so this helped me to realize I needed to add the “_log” to my filename.

    try
    {
    $server.AttachDatabase($dbname, $dbfiles)
    #end try
    }
    catch
    {
    Write-Host $_.exception;
    #end catch
    }

    That’s it. I run it and get some output.

    attach_q

    A slight issue in my debug code. I had $debug = 7 and needed $debug –eq 7. Still, it records each database as being attached, with the catch block not executing. If I check SSMS.

    attach_p

    My databases are back.

    It’s not a perfect script, and there are probably improvements, but it does get me my databases back easily.

  • Sabbatical Day 1 –The Appliance Guy

    My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

    Today was the first real day of my sabbatical I decided that I’ll treat the weekends mostly like weekends since I have no shortage of ranch chores to do and I’ll try to stick to sabbatical growth and work during the week.

    I started today at the Habitat for Humanity ReStore. I had signed up to volunteer a half day a few weeks ago and today was the day. Up at 7, showering, getting ready and on the road at 8 to be sure I was there on time. The store is a bit of a distance for me, 50mi by the Google Maps app, so I wanted to be sure I was there on time.

    The ReStores are places where people can donate old furniture, tools, appliances, building supplies, etc. and they are then re-sold to the public. The profits from the sales go to funding the Habitat organization. I don’t know about the other areas, but the presentation I got from Habitat for Humanity Denver said that the three stores in our metro area pay for all the operational costs of the affiliate. This means that in Denver, 100% of any donations, mortgage repayments, etc. goes back to building more houses.

    I arrived at the store a few minutes early, and walked up to find someone else waiting that was a first time volunteer as well. We chatted for a minute before the store opened. Once inside, we signed in, had a brief orientation video (repeated a lot of what I’d gotten at the Denver HQ) and then got our aprons and gloves. After a tour of the store, we were split up to different departments. I ended up in appliances, which meant working with refrigerators, ovens, washers, dryers, dishwashers and microwaves.

    My basic job today was the test new arrivals, of which there were a lot from the weekend. I worked a 9-2 shift, and after 45 minutes getting started, I spent the day moving large appliances around, to and from the back and around the area. We’d plug in 2-3 fridges and leave a cup of water in the freezer to see if they worked. We’d attach plugs to other appliances, most of which didn’t have them, and then see if ovens turned on, if washers’ motors worked, and in general, just give them a basic electrical test. If they worked, we’d mark them, and set them up for sale. If not, we’d take them in the back where salvagers would pick them up once a week.

    It’s simple work, fairly basic, and not too strenuous. I helped a few people load appliances, and otherwise did basic menial work, including sweeping and mopping when we made a mess, and simple electrical connections. All in all a calm, quiet day.

    With the Denver area building 30-40 homes a year, housing people that are without housing or in poor situations, it felt good to be contributing to the cause. I’m scheduled to go back out there next week and I’ll try to get in a different department.