Sample Header Ad - 728x90

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