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.
Filed under: Blog Tagged: powershell, syndicated