Trying to use SMO Transfer Object to transfer a database from one instance to another
1
vote
2
answers
5242
views
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 , C:\powershell_sample_scripts\runMigration.ps1: line 150
at , : line 1
PipelineIterationInfo : {}
Asked by Gandolf989
(1540 rep)
Nov 25, 2015, 02:48 PM
Last activity: Apr 27, 2020, 09:49 PM
Last activity: Apr 27, 2020, 09:49 PM