0

Here is a powershell script:

[string] $server   = "devserver\mssql";          # SQL Server Instance
[string] $database = "ftg";      # Database with the tables to script out.
[string] $folder   = "d:\FT\FTProject\";          # Path to export to
[string] $SQLLogin = "Sa";
[string] $SQLPass  = "Sa1234";

# Reference to SMO
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');

Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Started ...");

$con = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server, $SQLLogin, $SQLPass);
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($con);
$db = New-Object Microsoft.SqlServer.Management.SMO.Database($srv, $database);


#Use SMO Transfer Class by specifying source database
#you can specify properties you want either brought over or excluded, when the copy happens
$ObjTransfer   = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer;
$ObjTransfer.Database = $db

At last line an error occurs:

Exception setting "Database": "Cannot convert the "[ftg]" value of type "Microsoft.SqlServer.Management.Smo.Database" to type "Microsoft.SqlServer.Managem ent.Smo.Database"." At D:\FT\FTProject\Setup\transfer.ps1:31 char:1 + $ObjTransfer.Database = [Microsoft.SqlServer.Management.SMO.Database] $db + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException + FullyQualifiedErrorId : ExceptionWhenSetting

I also tryed to set $db as follows:

$db = $srv.Databases[$database]

What is wrong?

host.13
  • 108
  • 1
  • 1
  • 11

2 Answers2

1

Try the following:

$db = $srv.Databases[$database]
...
$ObjTransfer = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $db

This should create the Transfer object using $db as the Source Database.

See example at MSDN: Transferring Schema and Data from One Database to Another in PowerShell

Eris
  • 7,378
  • 1
  • 30
  • 45
  • This doesn't works too. Error occurs: New-Object : Cannot find an overload for "Transfer" and the argument count: "1". At D:\FT\FTProject\Setup\transfer.ps1:25 char:18 + $ObjTransfer = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfe ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [New-Object], MethodException + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands. I also tryed an expample from your link - same error occurs. – host.13 Nov 27 '13 at 07:04
0

I think it because different versions of Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.SmoExtended.dll were loaded.

I get the same error when i loaded first assembly in 10 version and another one in 12.

When i loaded both of them explicitly in the same version everything worked fine.

Add-Type -path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"

Add-Type -path "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll"
Alexan
  • 8,165
  • 14
  • 74
  • 101
jml
  • 1