Skip to main content
2 of 2
typo
Tom V
  • 15.8k
  • 7
  • 66
  • 88

Since I don't know exactly what error you are getting, I will just let you know what error I get based on your code and the database I chose to test against.

I will note a good portion of the properties you are using, are not valid in SQL Server 2012 SMO (at least on my machine)

The end result I used for the script is at the end. I got a few different errors in my case:

Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details." At line:46 char:1

If you are getting this error you can check the Exception property on $error and find the cause: enter image description here

I am getting the above error because I already ran this once and it partially created objects.

The only other error I get is similar in nature where I have to check the Exception property but the other errors were related to code in procedures of the database I chose to test against. I changed to a database I have that simply has two tables with a few hundred rows of data and it completed with no error.

So in the end if you want to edit your question to provide the exact errors you might be getting, I can adjust the answer if I am able to figure out anything.

$error.Clear() Add-Type -AssemblyName 'Microsoft.SqlServer.Smo,Version=11.0.0.0,Culture=neutral,publickeytoken=89845dcd8080cc91' Add-Type -AssemblyName 'Microsoft.SqlServer.SmoExtended,Version=11.0.0.0,Culture=neutral,publickeytoken=89845dcd8080cc91' $srv = New-Object Microsoft.SqlServer.Management.Smo.Server 'MANATARMS\SQL12' $db = $srv.Databases["Credit"] $dbDest = 'CreditCopy' <# Main error I received was using this line as you had it formated: "Cannot index into a null array." #> #$xfr1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer($prodServerDB.Databases["Credit"]) $xfr1 = New-Object Microsoft.SqlServer.Management.Smo.Transfer($db) #Set this objects properties $xfr1.DestinationLoginSecure = $true $xfr1.DestinationServer = 'MANATARMS\SQL12' $xfr1.DestinationDatabase = $dbDest $xfr1.BatchSize = 10485760 $xfr1.CopyData = $true $xfr1.CopyAllTables = $true $xfr1.CopyAllObjects = $true $xfr1.CopyAllDatabaseTriggers = $true $xfr1.CopyAllLogins = $false $xfr1.CopyAllRoles = $false $xfr1.CopyAllUsers = $false $xfr1.CopySchema = $true $xfr1.PreserveDbo = $true $xfr1.PreserveLogins = $true <# these properties does not exist on the object for 2012 SMO #> ##$xfr1.Options.AllowSystemObjects = $false ##$xfr1.Options.ContinueScriptingOnError = $true ##$xfr1.Options.Indexes = $true ##$xfr1.Options.IncludeIfNotExists = $true ##$xfr1.Options.DriAll = $true ##$xfr1.Options.SchemaQualify = $true ##$xfr1.Options.ScriptSchema = $true ##$xfr1.Options.ScriptData = $true ##$xfr1.Options.WithDependencies = $true # Script the transfer. Alternatively perform immediate data transfer with TransferData method. # $xfr1.ScriptTransfer() # $xfr1.EnumScriptTransfer() $xfr1.TransferData() 
user507