I’ve been meaning to write something this post for a while but the stars have aligned this week. Garry Bargsley (blog | twitter) published a post about making a schema-only copy of a database on the day that I needed to solve that exact problem. But that’s not what this is about. It’s just a convenient way to demonstrate this shortcut.
I’m sure that a lot of folks do work on the PowerShell command line, trying various things, before committing to writing a full script or function. Work it out step by step, try a few different things, etc. But once you’ve finished, how can you turn it into a script? Let’s take a look.
I started by running each command in series on the command line. But I made one little mistake – I didn’t create the target database ahead of time. So I took care of that, then re-ran the transfer.
One-Offs Are Never One-Offs
I’ve got that done now, but I’ll have to do it again in the future. Time to start making a script out of this. I could tediously copy & paste each line from my Terminal session into Visual Studio Code, but…yes, tedious.
A Better Way
Get-History shows me all the commands I’ve run in the current session. And they’re indexed!
Commands 23 through 29 are what I ran to make my prototype work, but for a final script, I don’t need all of them and the order needs to change. I need to execute 23 through 26, then 28, then 27 (the target database has to exist before I execute the transfer).
Get-History returns an object, I can grab just the commands that I’m interested in (by passing a comma-separated list of
Ids or a collection of them), extract the
CommandLine property, and do something else with the commands that were executed.
That “something else” being “put them onto the clipboard” with
Set-Clipboard in this case.
Get-History -Id 23,24,25,26,28,27 | Set-Clipboard;
On Windows you can pipe to
clip.exe which does the same thing. But
Set-Clipboard is the way of the future, and it’s cross-platform whereas
clip.exe is not.
Finish Building It
Each of the commands that I need is on my clipboard, so I can drop them right into VSCode.
Because I passed distinct IDs to
Get-History, they’re returned in the order that I asked for them. Next, I can parameterize everything and wrap it up into a function with all the trimmings.
Command-line prototyping with a shortcut to starting a real script. I used to painstakingly copy & paste from the PowerShell prompt, but this is faster, easier, and more accurate.