PowerShell – using SQLPS for backup and restore

For this, I set up a Windows 10 Enterprise VM, and loaded up Localdb.msi and the new 2017 SSMS.

Code is here.

Useful MSDN page here.

Some screenshots

Advertisements

SqlServer Express 2016

Here. Also includes the option to install just LocalDb. See my post on that here. It also mentions Linqpad, which I recommend as a great way of discovering your database server connection details. And you still need to get the MASSIVE SSMS install, although Linqpad is a reasonable high level navigator.

SQL Server Management Studio (SSMS)

This is now a) free, b) no longer part of the SQL Server media. Get it here.

ssms01.PNG

(Right now WordPress image upload seems to have  a glitch where it uploads in reverse order of capture, so read these pictures bottom to top)

LinqPad and LocalDB

I wanted to create the smallest SQLServer database footprint I could… and thought that LocalDB might be the right way to go. However it turns out (and I would like it if I was wrong) that on a bare-ish environment, you have to use to download and selectively install SQLServer Express… and the binaries for that are getting on for 1GB. In fairness, these days SQLServer Express is pretty much full-fat SQLServer. But in the same way that CoreClr tries to give you what you want and no more, could SQLServer not follow that principle, at least? I could work out the minimum footprint I could get away with, having installed it… but I can’t be bothered – for all I know there might be a dependency on some registry. That would be for another time. Anyhow, why did I want it or any database this time around? So that I could have a play with the LinqPad examples.

Stop Press: ha! what do you know – in googling for appropriate hyperlinks, I found this, and more specifically this:

So, OK, before I embark on that, I’ll just dump my screenshots from the more LinqPad focussed session:

With that done, let’s just do a bit on this then… at a stupid hour of the night on a workday:

In fact, it turned out the LocalDB was identical… but the better part was the mere 44MB or so of a download, taking seconds rather than minutes. The installation still expands to 229 MB in Programs and Features, and that’s fine.

Here is the/a default connect string for LocalDB following installation:

(LOCALDB)\MSSQLLocalDB

SqlExpressx05