Tag Archives: restore

How to save your life with powershell.

I’m always preaching the wonders of powershell, but I think quite often I forget to stress the impact that it can provide. And today is one of those times when I’m able to give you a real life impact of how PS can save your life… ok, your job anyway.

One of the things PS does really well is scripting DB objects. It’s always one of the first things I show that’s really useful in my classes and it always gets a appropriate amount of gasp. But other than creating dev systems from prod, what good is it? Well, it’s a fabulous way to script your prod environment on a regular basis just to have the scripts handy. If you’re able to use PS to easily script your tables, SPs, views, users, etc. then you’re going to have a history of your DB that you can pull from anytime. And if you’re smart enough to store those scripts in txt files with dates appended to them, then you can always get back a complete version of your DB for any given day. Cool huh?

And the implications of this are huge. Not only can you create a complete environment for any given day so that you can compare it with the current version, or any other version you like, but much more likely is the scenario where someone makes a mistake in prod and needs some small piece of code replaced. So someone drops an SP, or alters a view, or kills a job, or anything else along those lines. Of course, this won’t help you with data accidents, but imagine the possibility of being able to pull your butt out of the fire because you were able to recover from a mistake you made… or anyone else made.

This came not only in handy, but actually saved my ass at my last gig where I had these scripts running and accidentally dropped all the SPs in our prod DB. I could have sworn I was on dev, right? So I had my PS scripts to import the objects again too, so as soon as I realized my mistake I recreated all the SPs and all was well with only a minor hiccup to our reporting cycle. But that’s exactly what I’m talking about. If you have these things ready to go you’re ready for whatever kind of disaster comes up and if you not only have the scripted objects sitting there, but also the scripts to put them back, then you’re in the best shape you can possibly be in.

My favorite way to store these like I said is in txt files.
I put them in a folder named for the DB, then another subfolder after the object type, then I name the files by schema.objectname.txt. So a sample path to an object would look like this:

Now to parse the date back out is a simple matter and can be handled with a simple PS script. And the scripts you need to restore can be as easy or complicated as you like. You can have any or none of the following:

Restore all tables
Restore all indexes
Restore all SPs
Restore all views
Restore single SP
Restore single view

You could also combine them into a single PS script more or less that you just pass an object type to and it figures out the path and does the restore for you that way. Either way is good really.

So setup your PS scripts to script your DB objects and store them someplace. It’s only txt so you can keep them pretty far back if you like, and even archive them in zipped files if you like.

Good luck.

Snotting Rights

I recently ran across a perfectly delicious situation I wanted to share with you.  Someone actually wrote to tell me about this.  He just started a new gig and there was massive corruption in one of the dev DBs.  He went to ask the DBA where the backup was and he got a very snotty reply. 

Well, as it turns out this was something that the DBA had gone around and around with them about.  See, the devs didn’t want the DBA to do anything on their precious box so they refused to give him rights.  He tried to explain that they needed backups, etc but they wouldn’t hear of any DBAs pissing all over their dev box. 

And now when there’s massive corruption and they need to get their dev code back they call the DBA to ask for help.  Y, fat chance guys. 

I’m here to tell you that the DBA has full snotting rights here.  And it only goes to teach them a lesson I hope.  DBAs aren’t here to piss on your parade.  We’re here to make sure your parade lasts as long as you want and you can even start your parade over and over again if you need to.  Seriously guys all metaphors aside, we’re here to help.  So stop acting like we’re on different sides.

Watch my free SQL Server Tutorials at:

Read my book reviews at:

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:


Spoil your users

It happens sometimes when things just don’t go as planned. One of the things I always do is to make sure that things I may need in a hurry are ready. Restores are just that kinda thing. I go out of my way to make sure that for any given box, I can access complete restores within just a few secs. I’m using LiteSpeed, so I’ve written my own code that will create a list of restore statements for me based off of the last full backup and all the logs since then. And I can produce hundreds of lines of restore code in just about 5secs, including opening the script.

Well, it happened today. My first restore in a while, and it happened to be on a box where the LiteSpeed process had stopped logging to the central repo so I didn’t have my usual list of backups to use to create my statements. So there I was building statements by hand, which wasn’t too hard because I got lucky and they only needed a few. Then about 15mins later, the manager of that group came up to me and said that I had forgotten to send the email that the restore was done. When I told him that it was because I hadn’t started it yet, he was like WHAT? What’s taking so long, this kinda thing only takes a couple mins usually.

And that’s what you want to hear. You want your users to get spoiled to getting these kinds of things fast. I’ve since fixed my LiteSpeed glitch and the next time he’ll be good to go. But I love it when stuff like this happens because it means I’m doing a good job and the users have come to count on me being reliable and fast.