I’ve talked about code that writes code, Powershell for fun and profit, and I even have an intro to Powershell cmdlets class available. Today, we’re going to take a specific, simple example of generating RESTORE LOG statements from a directory list of files.
Note that there’s clearly more than one way to go about this kind of task. The following is just one of several methods that are way better than writing a hundred RESTORE statements by hand.
How can one go about this?
- Launch Powershell and navigate to the directory.
- Get a list of filenames into a file using a command similar to
dir *.trn | format-table name | out-file c:\temp\logfiles.sql - Open that logfiles.sql file in SSMS and do a quick series of replace-alls. For example, for log backup files that begin with “DB1”:
- Replace “DB1” with “RESTORE LOG [DB1] FROM DISK = ‘\\backup\svr\DB1”
- Replace “.trn” with “.trn’ WITH NORECOVERY;”
How does this work? Let’s break it down step by step.
Run Powershell
You can find Powershell in your Start menu by, of course, typing in “Powershell”. Or you can hit Windows-R to get the Run dialogue, type “powershell”, and hit enter.
To navigate to a directory, just use the command “cd \\backups\svr\“.
Get a list of filenames
Let’s break down the Powershell command we used above, dir *.trn | format-table name | out-file c:\temp\logfiles.sql :
- dir gets a list of all the files in the current folder.
- *.trn filters the listing, getting only those files ending in .trn.
- The pipe | passes that list on to the next command.
- format-table name tells Powershell we just want the name. Not the create date, not the directory…just a list of file names with no extra information.
- The second pipe | passes that list of file names on to the next command.
- out-file c:\temp\logfiles.sql creates (or overwrites) a file at that name and location, and writes the input (the list of file names) to it.
That’s it!
Find and Replace in SSMS
There’s a lot of fancy, regular-expressionsy stuff we could do once we have that file open in SSMS. But in this case, we can do plain old text replacements, to turn this:
DB1_log_20180101120000.trn
Into this:
RESTORE LOG [DB1] FROM DISK = ‘\\backup\svr\DB1_log_20180101120000.trn’ WITH NORECOVERY;
All we had to do was add text before the leading DB1, and more text after the closing .trn. Simple! And, we didn’t have to copy-paste 137 file names and hand-edit each one into a restore command!
As Will Hunting might say, “Work smahtah, not haahdah.”