Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.
I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older incarnation, the name of which escapes me at the moment) and .Net and I'm used to them. I'd generally lean towards SMO, since it makes some things a lot easier, like scripting out objects. My own code uses SMO some times and .Net some times. I think it's handier to use .Net to get simple result sets, for instance.
I think that Invoke-SQLCMD makes more sense if you have lots of existing TSQL scripts. If you are creating strings and executing them through -Query, that's going to be messy. If you have a good grasp of how Powershell works with .Net and SMO, using Invoke-SQLCMD occasionally, when you have a script file to run, is easy.
I've always found the PSDrive thing clunky and felt that they implemented it because they got caught up in the "everything can look like a file system" idea. I know that the *nix guys love \proc and such, but I feel that this implmentation feels sort of forced. I think that PSDrive is OK, maybe even good if you hate the UI, for exploring things but I've never written a script that uses it.
I have never seen anyone use the WMI provider. So, that would be my last choice.
So, I'd lead with SMO and fall back to .Net when it's handier to.
You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this:
$e = $error[0]
$e.Exception
$e.Exception.InnerException
$e.Exception.InnerException.InnerException
...
Until I find the real error. It could be a problem with the actual restore (i.e. database with that name already exists, bad paths for the physical files, etc). But until you get to the root of that exception you won't know!
Best Answer
If you are on the SQL Server where the database resides, then you can follow these steps to connect to a database in PowerShell
First you have to load the SQL Server PowerShell Module. This is achieved with the following simple command:
You have now loaded the SQL Powershell Module and have been switched to the SQLSERVER drive.
Let's see what we can retrieve from the system:
That looks promising.
We'll switch into the SQL Server Database Engine (SQL) and see what we have:
Ok. Let's connect to the
SQLSERVER-NAME
instance and carry on researching (I usedlocalhost
in my example, because I am on the server itself):Switch to the DEFAULT instance:
And retrieve a list of instance objects:
Ok, switch into the databases...
Once you are in the correct database, you can then set of relevant CMD-lets.
You can then carry on from here.