1

The schema tables are owned by dbo and another schema name. The code below is not moving over any tables. Is there something else that I need to add to make it transfer the tables? There are no stored procedures or views that need to get moved.

$xfr1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer($prodServerDB.Databases[$devDatabase])

#Set this objects properties
$xfr1.DestinationLoginSecure           = $false
$xfr1.DestinationServer                = $devServer
$xfr1.DestinationLogin                 = $devUsername
$xfr1.DestinationPassword              = $devPassword
$xfr1.DestinationDatabase              = $devDatabase
$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

$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()

Current Error Stack below:

PS C:\powershell_sample_scripts> $Error | select *


ErrorRecord                 : Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details."
StackTrace                  :    at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs,
                              MemberInfo memberInfo)
                                 at CallSite.Target(Closure , CallSite , Object )
                                 at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
                                 at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
WasThrownFromThrowStatement : False
Message                     : Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details."
Data                        : {System.Management.Automation.Interpreter.InterpretedFrameInfo}
InnerException              : Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. --->
                              System.Data.SqlClient.SqlException: User, group, or role 'user_account' already exists in the current database.
                                 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                                 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                                 at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
                              TdsParserStateObject stateObj, Boolean& dataReady)
                                 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
                                 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean
                              asyncWrite)
                                 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                                 at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
                                 at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
                                 --- End of inner exception stack trace ---
                                 at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
                                 at CallSite.Target(Closure , CallSite , Object )
TargetSite                  : System.Collections.ObjectModel.Collection`1[System.Management.Automation.PSObject] Invoke(System.Collections.IEnumerable)
HelpLink                    :
Source                      : System.Management.Automation
HResult                     : -2146233087

PSMessageDetails      :
Exception             : System.Management.Automation.MethodInvocationException: Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner
                        exception for details." ---> Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. --->
                        System.Data.SqlClient.SqlException: User, group, or role 'user_account' already exists in the current database.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                           at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
                        TdsParserStateObject stateObj, Boolean& dataReady)
                           at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
                           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
                           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                           at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
                           at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
                           at Microsoft.PowerShell.Executor.ExecuteCommandHelper(Pipeline tempPipeline, Exception& exceptionThrown, ExecutionOptions options)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : TransferException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at <ScriptBlock>, C:\powershell_sample_scripts\runMigration.ps1: line 150
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}
  • 1
    Do you receive any errors or output otherwise? – user507 Nov 25 '15 at 23:43
  • As I stated below, I am not currently getting any errors, but no data is getting inserted in the destination database. Even though I specify $xfr1.CopyData = $true, no data is being copied. I mostly work with Oracle databases, hence it can be pretty frustrating trying to use an unfamiliar tool set and have it silently fail rather than fail and tell me what I did wrong that caused it not to work. There must be someone who is capable of making this code work. Thanks. – Gandolf989 Nov 30 '15 at 19:56
5

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()
  • I was getting a similar error message. I was using the Transfer object to script creating the users after i drop and create the DB. I kept getting errors for trying to create system objects or for users already existing. So I just run a manual script to create the users and schema's. Now the script is running all without error, but no data is getting inserted into the destination DB. The whole point in using the Transfer object was to move not just the tables, indexes and constraint, but to also get the data. So either I am using the wrong method or I am lacking something in my script. Thanks. – Gandolf989 Nov 30 '15 at 19:52
  • If you want to transfer the data my script above worked calling TransferData()and moved the tables and data in my test database. – user507 Nov 30 '15 at 20:03
  • I tried your script and got the following error: "An error occurred while transferring data. See the inner exception for details. User, group, or role 'user_account' already exists in the current database. Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details." At C:\powershell_sample_scripts\runMigration.ps1:150 char:1 + $xfr1.TransferData() + ~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : TransferException" – Gandolf989 Dec 1 '15 at 16:36
  • @Gandolf989 as noted in the error user, group, or role 'user_account' already exist in the current database. – user507 Dec 1 '15 at 16:37
  • I added the error stack to my question. Thanks. – Gandolf989 Dec 1 '15 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.