Tuesday, September 08, 2009

backups, yeah yeah, we've done that before

So, it was the end of the week, and the beginning of the weekend. I was attempting to do one of the loneliest tasks a DBA does. Backups. I have done these tons of times, and been successful in my sleep. This is a task we all learn, and worry over, and mess up on occasion. Such a simple thing, but ever present.

My thoughts here today are to remind you to simply take your time, plan ahead, and execute successfully. That can be a generalized statement for most anything, but in this case, it holds particular interest to me for backups.

Like I said, it was the end of the week. I was not thinking straight, and wanted to get this simple task done, so I could continue with non-SQL life. I had to restore a series of backups to a test machine. I should have this scripted out somewhere, but didn't. In a pinch, I had to rely on my own grey matter to perform the task adequately. I stole 1 sample that I did have laying around, and attempted to apply it to all 12 different databases. I quickly copied and pasted the sample, and renamed the dbs, the filenames, the internal names. Some of the internal filenames are DB_1.mdf, while others are DB_data.mdf. So copy and paste failed me, and just produced a bunch of TSQL that looked like it should work, yet did not. So, I had to run a bunch of Restore FILELISTONLY commands to ensure that I had the right quantity of internal files, as well as names. This simply took time that I didn't have.

Back to the filenames. I did this quickly, without thinking. I ended up copying the wrong path. The restoration location ended up being c:\mssql\backups, not the typical c:\mssql\data. This wasnt noticed until I had done about 5 databases restored. So, when I was done, I went back down the list and restored them again. More time.

Along the way, I missed a couple of the 12 databases, and had to go back to square
one, and generate the TSQL to restore them, with the correct filenames, and paths.

So, in the end, a task that we all believe we can do in our sleep, and have done many times over in the waking hours, proved to be a thorn in my side. Not because of the nature of the task was difficult, but my hurried approach, and lack of planning caused me to spend much more time than necessary performing said task. Needless to say, I now have a script that contains all the dbs to restore. This, I have already used, to perform a restore of one of these dbs in another test environment. It has already saved me time.

Suggestion. Instead of reading updates on Facebook, or talking with coworkers, or any other task that seems more fun, I should take the time to script out these items that I take for granted, so that I'm ready in a pinch to perform the task quickly, efficiently and correctly.

No comments: