Art of the DBA Rotating Header Image

January, 2017:

Using #PowerShell to Restore to a New Location

Now that I’ve gotten some of my thought pieces out of my brain, I wanted to get back to some more technical posts, starting with some simpler techniques for people trying to figure out how to use SQL Server and PowerShell together. I know that a lot of database pros are starting to understand the importance of the language, but still struggle with some practical examples of how to get started. One of my goals with this blog is to bridge that gap.

When restoring a backup, it can be tedious to restore to a new location and have to figure out your MOVE statements. If you only have one data file and one log file, it’s probably not a big deal, but it’s still annoying. Usually, the steps for me are:

  1. Figure out my new data and log paths.
  2. Run a RESTORE FILELISTONLY against the backup file to get the files.
  3. Write out my RESTORE WITH MOVE commands using the new paths.
  4. Execute

None of this is difficult, but we can still make it easier. We have an established process, so putting some PowerShell scripting around it can automate our restore to make the script building faster and more consistent.

Our weapon of choice will be Restore-SqlDatabase. This workhorse cmdlet has been part of the both the old SQLPS and the new SqlServer modules. The functionality hasn’t really changed, meaning that what we go over here should work for you regardless of what module you use. I always recommend using the most recent version of the code, but don’t worry if you can’t.

The cmdlet is straightforward in its use. Fundamentally, all we need to declare is an instance, database name, and backup file. However, if we don’t declare anything else, the cmdlet will try and restore the database files to their original locations. Keep in mind this is no different than how a normal RESTORE DATABASE command works.

This is where we make our lives easier with PowerShell. First off, to move files using Restore-SqlDatabase, we need to create a collection of RelocateFile objects. Don’t let the .Net-ness of this freak you out. All we’re doing is creating something that has the logical file name and the new physical file name. In other words, it’s just an abstraction of the MOVE statement in RESTORE DATABASE.

Let’s look at some code. I’ve got a script, but I think the best way to approach it is to break it up and talk about each section individually, just to make sure we’re all on the same page. To get started, we should declare a few things: the new file locations, output of a script file, database name for the restore, backup file, and then an array we can store our RelocateFile objects in.

#Set Variables
$NewDataPath = 'C:\DBFiles\Data'
$NewLogPath = 'C:\DBFiles\Log'
$OutputFile = '.\restore.sql'
$dbname = 'AdvWorks2014'
$BackupFile = 'C:\DBFiles\AdventureWorks2014.bak'
$relocate = @()

Next up is a simple RESTORE FILELISTONLY to get our file list. This needs to be done with Invoke-SqlCmd because there’s no support in Restore-SqlDatabase (or any other cmdlet) for the file list option.

#Get a list of database files in the backup
$dbfiles = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "RESTORE FILELISTONLY FROM DISK='$BackupFile';"

Now comes the “magic”. Our RESTORE FILELISTONLY call gives us a collection for all our files, but it’s all the old locations. We will look through this collection, do some string replacement, and create our RelocateFile objects. I want to call out the use of Split-Path -Leaf, a handy cmdlet that will separate out the different parts of a file path. By using -Leaf, the cmdlet give you only the actual file name. We can just append that value to the end of our new path (using Join-Path) and use that for creating the RelocateFile object for each file.

#Loop through filelist files, replace old paths with new paths
foreach($dbfile in $dbfiles){
  $DbFileName = $dbfile.PhysicalName | Split-Path -Leaf
  if($dbfile.Type -eq 'L'){
    $newfile = Join-Path -Path $NewLogPath -ChildPath $DbFileName
  } else {
    $newfile = Join-Path -Path $NewDataPath -ChildPath $DbFileName
  }
  $relocate += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ($dbfile.LogicalName,$newfile)
}

Creating the RelocateFile objects is the heavy lifting. After this, it’s just a matter of calling Restore-SqlDatabase with the right arguments. Note that I’m using the -Script argument and piping this to Out-File. We’re using PowerShell to create a SQL script, which is a pattern I like. As handy as these tools are, they don’t always get everything, so I will use scripts to create scripts and then edit the final output with whatever else I need.

#Create Restore script
Restore-SqlDatabase -ServerInstance localhost `
-Database $dbname `
-RelocateFile $relocate `
-BackupFile "$BackupFile" `
-RestoreAction Database `
-Script | Out-File $OutputFile

By saving and reusing this script, I have saved myself a lot of man hours for restores. The strength here isn’t in any mystery code or magic functionality. It is simply a matter of leveraging a framework to automate an existing process.

I’ve actually taken this script and created a more formalized function with it. The core is there, but in keeping with the tooling spirit, I’ve added some additional code that validates file system paths. You can find it on my GitHub repository and you’re welcome to download and make use of it yourself.

Highways and Railroads

Stop me if you’ve heard this one….

“We don’t have time for process, it just slows us down.”

In the buzz of today’s world, you’ve probably had this thrown at you at least once. Process has supposedly become an anathema to productivity. Everyone from developers up to CTOs seem to think that you need to get rid of process in order to turn out timely software.

Well, they’re wrong.

I understand the reasons behind it. As someone who’s had to fill out change request forms to alter a server’s MAXDOP setting, process can be tedious and troublesome. It can be a shackle that mires work in the swamp of forms and sign offs. When you’re living in a world of “get sh*t done”, process can be a thorn in everyone’s side.

There’s another side of the coin, though. Process, done right, will ensure that work is done the same way every time. It can provide protection against costly mistakes, unexpected outages, and other business pains that puts everyone under the gun. Also, if you build your process right, it won’t be a bottleneck to your work but a guide rail to keep you on the right track.

Driving vs. Riding

The analogy I like to use with folks when I explain the need for process is by comparing highways and railroads. Think about how you drive for a moment. When you’re on the highway, with a bunch of other people trying to get to the same place, you can only go so fast. You’re going to be limited to how many other cars are around you, so more traffic means slower speeds. Of course, then you get that one person who starts trying to move faster than is safe in traffic, usually causing an accident.

This is the lack of process at play. Different teams or developers have their own car and are trying to get to the release. However, everyone else is doing the same and your highway only has so much bandwidth. Sure, we have a road, so some path is defined, but there’s nothing to govern your interactions with the other cars other than general momentum and the number of resources you have to make the deploy work. You could make the road wider by getting more resources, but that’s not scalable.

Contrast this with a railroad track. It’s straight, narrow, and trains can fly along this because all the roadblocks are out of the way. You can link multiple cars together on a train to get everyone to the same place at the same time, which is a lot faster than using the highway. Even if the train moves a little slower than a car, it will still get there ahead because there isn’t anything blocking the track. You also get there safer because there isn’t any competing traffic to get in the way and risk an accident.

Building railroads is the goal of implementing process. You want to build something that is direct and well defined. What we lose in freedom pays off in speed. We’re building bullet trains that can deliver rapid deployments through consistent, repeatable action. This is achieved by  removing blocks, reducing stops, and streamlining the process we use to get things done. Process should not be a throttle, but a track, giving us a clear method of getting things done faster.

I’ve Been Working On The Railroad

How do we go about building this Nirvana? It takes cooperation between both development and operations teams to build this right, as both are invested in the outcome. Developers want to deliver new code to market faster and operations wants that code to be safe, stable, and not put the business at risk. Both sides must come together to collaborate on building the process. And make no mistake, this will be a building effort. There is no black voodoo magic in making this happen.

The first step is actually to write everything down. I always preach that the most basic form of automation is a check list. You can’t automate a process if you don’t know what it is. Start with a plan that everyone puts together for how software should be released and use that as your road map.

“We can’t take the time to stop doing everything just to put this together!!!” Yeah, I’ve heard that one too. No one is asking you to stop working just to switch to a new process. All I’m suggesting is, if you’ve got your highway, start building your railroad next to it. People will still be driving on the road.

You also might have people concerned about getting it right the first time. Let’s be honest here, you may not. Treat this plan like you would any other piece of work you have and develop it iteratively. Start somewhere and, as you build things out, fix the problems you discover. Your release process is just another piece of software, with features and bugs. No reason not to treat it in the same way.

Once you’ve got your plan, start following it. It might be inconvenient, but this is the sort of thing that takes discipline. It may seem tedious, but this is the start of a diet or exercise plan. It will be tough, but over time it will get easier and you’ll get better at it.

As you follow your plan, you will be able to introduce tooling and automation to speed up parts of your railroad track. Unit tests, automated builds, source control, incremental changes…these are all pieces of the process. Improve your process one piece at a time, making each new change so it can be evaluated and verified before moving on to the next step.

Build your process with the goal of removing traffic jams and slow downs. You want to lay down track to give guidance and consistency. You will find that you and those around you will achieve that mythical space of being faster, more reliable, and ultimately more agile with your software development. In short, you’ll run like you’re on rails.

All Aboard

Building this sort of process is at the heart of DevOps. DevOps isn’t a tool or a magic method, it is a deliberate culture of cooperation and discipline. The only way you will build your railroad is if you bring together development teams and operations engineers and work together to relieve your shared pain. That is the selling point: both sides share many of the same challenges, so it should a no-brainer to come together and solve those issues.

If you’re looking for a real world example, check out Farm Credit Services of America. This article articulates, in a very real way, how one company moved into a DevOps culture and built their own railroad. While there’s a lot of information in the article, at the core it is about how development and operations collaborated to achieve something together.

I’ve seen similar work in other companies. It is usually painful growth, but the results far exceed the gains. What is important is to not lose sight of the need for process. Getting rid of process, if anything, will hinder you just as much as following some archaic method merely because it is rote. Build your process with the goal of removing bottlenecks and establishing guide rails. You will find that you and those around you will achieve that mythical space of being faster, more reliable, and ultimately more agile with your software development.

(I want to thank Rie Irish(@IrishSQL) for proof reading and contributing to this blog article.)

We Are All Developers Now

Last year, I had a pretty intense conversation with a friend of mine at a SQL Saturday. It was one of those that started with the typical “grumble grumble grumble damn devs” statement. There was a time I would have echoed that with a hearty “harrumph harrumph”, but as I’ve progressed through my career, I’ve come to realize that the line between developers and DBAs has softened and blurred, particularly in the age of DevOps. What followed was a back and forth about the habits of DBAs and developers and lead me to a phrase I’ve added to my lexicon: “We’re all developers now”

I know, I know. What about the long standing division between righteous Operations folks (DBAs, sysadmins, network engineers, and their ilk) versus the .Net, Java, Node, and other heathens of the Developer world. These “barbarians” assail the fortresses of Operations with hastily written code that is not properly tested. This causes sleepless nights filled with pages  that a weary admin must respond to and resolve. Why would anyone in their right mind associate with these irresponsible practices?

Borrowing From Your Neighbor

The first step to answering this question is to step back and actually look at what happens in the developer world and compare it to what we do in administration. It’s a common practice to borrow code and practices from our peers in the SQL world, so why not do the same with those who develop other types of code? Especially those who develop code for a living (hint: consider the recursiveness of this statement).

As an administrator, I created all sorts of administrative scripts. Obviously I’ve got a reputation for PowerShell, but I also have T-SQL scripts that I use as well. For a while I would hack these together as any other good DBA and store them on a USB/Dropbox/Google Drive/OneDrive. It was functional, but I only ever had the current state of those scripts and didn’t always know why I changed things. I could put in a header, but this was tedious and manual, an anathema to how I work.

Enter my time at Xero. I hadn’t really used source control before and the teams there used it heavily. I got a rapid introduction to GitHub from Kent Chenery(@kentchenery) and Hannah Gray(@lerevedetoiles). It didn’t take long for me to realize the benefits of source control, not just for databases, but for my own personal scripts. Now I maintain my own personal GitHub repo and not only have a central location where my code is stored, but it can be shared with others who can contribute and collaborate.

Code, Rinse, Repeat

After adopting source control, I began to look at other developer practices and habits. While one can debate the pros and cons of Agile development, one of the concepts I like is iterative development. As with other work we do, iterative development isn’t rocket science, but it took me a while to adopt it because of a natural fear admins have: production paranoia (aka “what will this break”).

Admins of all stripes are in constant fear of breaking production, and for good reason. We want every change to be right and as close to perfect as possible. However, most folks who develop iteratively realize that perfect is a road block. It is hard to anticipate all the factors. When you develop iteratively, you ship what you can and fix/fail fast once you deploy it.

I’ve adopted this approach for my own script/process development. Whether I’m publishing a script or deploying a server, I focus on delivering a product. I test aggressively, but I’m prepared for the event that something will fail. I focus on the feedback loop to test, evaluate, remediate, and deploy. As an aside, this feedback loop is often where application developers will fail, because they are often driving towards the next set of improvements or features. It’s incumbent on both sides of the fence to adopt and enforce the feedback loop.

It’s All Just Ones and Zeroes

I could go on about habits I’ve adopted, but the real question is “why are developer practices important to administrators?” As we move into a realm of automation and scripting (as any good admin will), we are doing more and more code development. Sure, we can click through GUIs to setup SQL Server or run a backup, but the more experienced folks out there will have scripts to accomplish these tasks. Network and system admins are deploying infrastructure to the cloud using CloudDeploy or ARM templates. We live in an age where almost everything can be codified.

This codification means it is imperative that we focus on good habits for managing our code. It might be that we’re writing T-SQL code for SQL maintenance. Perhaps we’re writing shell scripts to execute code deploys or build a continuous integration pipeline. Suddenly we’re developers of a different stripe.

So what do you do about it? You probably haven’t implemented some of these habits and, likely, you might be a little mystified on how to get started. I suggest you start where I started: Go to a developer and talk to them. Maybe chat with a couple. Go to a local developer user group and see what they’re talking about. This is about learning, so find a mentor who can help you learn this topic.

And who knows? Maybe you can teach them a few things about your world while you’re at it.

Be Like Water

“You must be shapeless, formless, like water. When you pour water in a cup, it becomes the cup. When you pour water in a bottle, it becomes the bottle. When you pour water in a teapot, it becomes the teapot. Water can drip and it can crash. Become like water my friend.

Bruce Lee

 

If you look around the internet, you will come across this famous quote. Often cited by arm chair philosophers (myself included), these simple sentences speak on being flexible and adaptable within your life and how being too rigid can limit us. These limits will make it difficult to truly grow and improve ourselves, whether it is our professional or personal lives.

We can break this quote down in many ways, but I’d like to focus on how it impacts technology professionals. One thing I love about working in technology is how often things change. The joy comes from the constant learning we must do to keep pace. If we’re not pushing ourselves to explore and discover, we risk falling behind and losing our way.

Empty Your Mind

If we look at the IT career path through the lens of Lee’s quote, the idea of flexibility becomes obvious. Through my time in the IT industry, I’ve seen the rise of the Internet, the cloud, concepts like Agile development and DevOps, and other changes that range between large and subtle. If I’ve learned anything, it is that defining myself as a DBA, sysadmin, or developer limits what I am willing to do and narrows the opportunities available to me.

When someone asks you to take on a new task, how do you approach it? Do you look at it through the lens of your job role? Or, instead, consider how it relates to your career path? Do you determine what you want to work on based on what you know and are comfortable with, or do you let the opportunities shape your growth?

As technology professionals, it’s important not to limit ourselves. Today’s experiment might be tomorrow’s trend. A hobby we tinker with could easily become our driving passion. We need to be receptive to what’s around us, be formless and fill the demands that are presented to us. Do not close yourself off to challenges because you haven’t done them, but be prepared to know what it takes to fill those vessels.

H Two O

This probably sounds daunting because there’s so much going on in the technology world. Should we be able to do anything? Be ready to build networks, write applications, design databases, and so on? How do we keep up with the overwhelming number of disciplines and developing technologies in our world?

I’ve long held that the “full stack developer” is a myth. We live in a world of specialization and trying to have deep knowledge of all disciplines is impossible. Sure, we can understand something of everything (being a jack of all trades, master of none), but it becomes difficult to bring the full weight of our expertise to bear if we only know a little bit of a lot of things.

At some point in our careers we need to understand what makes up our “water”. This brings me to another long held view of mine: technologies change, but concepts remain the same. The example I like to use is relational databases. Over the years we’ve had platforms like Microsoft SQL Server, Oracle, MySQL, PostGREs…the list goes on and on. Each of these platforms introduces new features every few years, trying to one up each other.

At the core, though, each of these platforms is a relational database. The relational model was codified by E.F. Codd back in 1969. Think about that for a moment. While Microsoft SQL Server is adding new features every few years, the foundational concepts that we all work with are forty seven years old. Almost half a decade of a technological principle that we build our careers on.

I don’t consider myself a SQL Server professional. My skill set is not limited to just SQL Server as a platform, but relational database design and data management. I am strongest with Microsoft’s offering, but I can adapt to another platform because I understand and own the foundation. The core concepts are what I build upon, which is what makes me stronger and more prepared.

Be Like Water

Becoming like water is more than just being adaptable, it’s about understanding what defines you. Job roles and requirements are just vessels we fill with the knowledge and experience we have. If we choose to be like water, we remain fluid and can, at once, fill the needs of a role as well as be more than that.

Be the sum of the things you have learned, not the tasks you can accomplish. My challenge to you for the new year is to build a deeper, more fulfilling career. Flow and adapt, learn and absorb. Empty your mind and be open to the numerous possibilities in front of you.

Be like water, my friend.