***-PSSnapin SQLServerCmdLetSnapin110 ....cannot add

Question

***-PSSnapin SQLServerCmdLetSnapin110 ....cannot add

Paresh Motiwala

SSCommitted

Points: 1772
More actions
April 15, 2014 at 8:17 am

#292447

Hello
I have gotten part of this powershell script from the web, and first part configured by me.
#Define Variables and configure variables
#----------------------------------------
#First Set the target database on server1
$targetdb ="DevTest2010_Content80_Portal_ProjServSitCol"
#Now set the source database from server2
$sourcedb="Prod2010_Content80_Portal_ProjServSitCol"
#Define the main source of backup folder
$Mainsource = "\\server3\location1\"
#concatenate Main Source and Source DAtabase name to get the full path to the file containing the backup you wish to use for restore.
$FullSource=$MainSource+$sourcedb
write-host $fullsource
#find the name of the file
$backupfilename = get-childitem $FullSource -Exclude *.trn | sort-object -descending lastwritetime |select Name -first 1
write-host $backupfilename.name
#Concatenate the $fullsrouce with the FileName to feed to the restore command
$sourcefilename=$fullsource+"\"+$backupfilename.name
write-host $sourcefilename
[ScriptBlock] $global:RestoreDB = {
param ([string] $targetdb, [string] $FullSource,
[string] $sqlDataPath, [string] $dataLogicalName, [string] $logLogicalName)
[string] $dbCommand = "RESTORE DATABASE [$targetdb] " +
"FROM DISK = N'$FullSource' " +
"WITH FILE = 1, " +
"MOVE N'$dataLogicalName' " +
"TO N'$sqlDataPath\$targetdb.mdf', " +
"MOVE N'$logLogicalName' " +
"TO N'$sqlDataPath\$targetdb.ldf', " +
"NOUNLOAD, STATS = 10"
$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin110"}
if($sqlSnapin -eq $null)
{
Add-PSSnapin SqlServerCmdletSnapin110
}
Invoke-Sqlcmd -Query $dbCommand
}
It does not let me run Add-PSSnapin SqlServerCmdletSnapin110.
Error message I get is, "Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2."
I could not find anything on the web that would help me overcome this.
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Tony Sweet Ten Centuries Points: 1049 More actions · Answer 1

First do you have SSMS installed on the same box you are trying to run this from? SSMS installs the snapin, but without SSMS installed the snapin does not exist.

If you do have SSMS installed - which version are you using? SQL 2008, 2008R2, 2012?

If you are using 2012 (which based on the snapin being 110 I am assuming you are) then you should be using the SQLPS module (The new way) and not the snapin (The old way).

This may help: http://sqlblogcasts.com/blogs/martinbell/archive/2011/12/08/Powershell-Snapins-with-SQL-2012.aspx

- Tony Sweet

Gary Varga SSC Guru Points: 82166 More actions · Answer 2

SQL Server 2012? If so I believe that it has switched to Modules. See here.

Import-Module "sqlps"

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

Tony "6 seconds quicker than Gaz" Sweet!!!

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 4

Great, so the import module worked.

Thank you so very much for the prompt feedback.

But then, in the script where it restores the sql db, how would I run the restore? by commenting out the lines.

$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin110"}

if($sqlSnapin -eq $null)

{

Add-PSSnapin SqlServerCmdletSnapin110

}

I suspect I might not need them any more...

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

If you mean changing

$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin110"}

if($sqlSnapin -eq $null)

{

Add-PSSnapin SqlServerCmdletSnapin110

}

to

#$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin110"}

#if($sqlSnapin -eq $null)

#{

#Add-PSSnapin SqlServerCmdletSnapin110

#}

Then I think you may be correct.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 6

now the script runs without any error but does not restore the database nor do we have any error logs...ugh

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Gary Varga SSC Guru Points: 82166 More actions · Answer 7

How about outputting $dbCommand to the console to ensure it is a valid command?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Tony Sweet Ten Centuries Points: 1049 More actions · Answer 8

Does the account you are running this under (probably your personal AD account) have database access required to do the restore? You would think if it was a permission or SQL issue it would error out.

- Tony Sweet