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()
| improve this answer | |
  • 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
0

EDIT

A comment by @alroc convinced me to use the SqlServer module instead of the SQLPS module, because the latter had long been deprecated. And immediately after I have made the change, I noticed that it was now possible to create databases from a Microsoft.SqlServer.Management.SMO.Transfer object, which I was not managing before. I don't understand why, and it might even be unrelated and I was just lucky. The SqlServer package can be installed through the following command:

Install-Module -Name SqlServer -AllowClobber

I am thus updating my answer with the working code, which is more readable, more elegant and more performant than my previous answer (at the bottom of this post).

$SQLInstanceName = $env:servername
$SourceDBName = $env:databasename
$SQLUser = $env:adminlogin
$SQLPassword = $env:adminPassword

Import-Module SqlServer -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

Function IsNullOrEmpty([string]$val){
    if ($val -eq $null -or $val -eq '') { $true }
    else{ $false }
}

If (IsNullOrEmpty($SQLInstanceName)) {
    $SQLInstanceName = $args[0]
}

If (IsNullOrEmpty($SourceDBName)) {
    $SourceDBName = $args[1]
}

If (IsNullOrEmpty($SQLUser)) {
    $SQLUser = $args[2]
}

If (IsNullOrEmpty($SQLPassword)) {
    $SQLPassword = $args[3]
}


Try {
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLInstanceName)

    $DestinationDBName = "${SourceDBName}.Staging"
    $SQLSecurePassword = ConvertTo-SecureString $SQLPassword -AsPlainText -Force

    $Server.ConnectionContext.LoginSecure = $false
    $Server.ConnectionContext.set_Login($SQLUser)
    $Server.ConnectionContext.set_SecurePassword($SQLSecurePassword)

    $SourceDB = $Server.Databases[$SourceDBName]
    $ObjTransfer = New-Object Microsoft.SqlServer.Management.SMO.Transfer ($SourceDB)

    $CopyDB = New-Object Microsoft.SqlServer.Management.SMO.Database ($Server, $DestinationDBName)
    $CopyDB.Create()


    # $ObjTransfer.CopyData = $false  - Uncomment this line so that data is not copied across
    $ObjTransfer.CopySchema = $true
    $ObjTransfer.CopyAllTables = $true
    $ObjTransfer.CopyAllDatabaseTriggers = $true
    $ObjTransfer.Options.WithDependencies = $true  
    $ObjTransfer.Options.ContinueScriptingOnError = $true  
    $ObjTransfer.DestinationDatabase = $DestinationDBName  
    $ObjTransfer.DestinationServer = $SQLInstanceName
    $ObjTransfer.DestinationPassword = $SQLPassword
    $ObjTransfer.DestinationLogin = $SQLUser
    $ObjTransfer.DestinationLoginSecure = $false
    $ObjTransfer.TransferData()
}
Catch [System.Exception] {
    # $_ is set to the ErrorRecord of the exception
    if ($_.Exception.InnerException) {
        Write-Error $_.Exception.InnerException.Message
    } else {
        Write-Error $_.Exception.Message
    }

    if($Server.Databases.Name -like $DestinationDBName) {
        Write-Host "Dropping cloned database..."

        # Call drop-db.ps1 to delete the stagingDB
        Invoke-Command { .\drop-db.ps1 $SQLInstanceName $DestinationDBName $SQLUser $SQLPassword }
    }
}
Finally {
    if($Server) {
        $Server.ConnectionContext.Disconnect()
    }
}

I was having a similar error implementing this. Tried literally everything, it just wouldn't work. What did work for me, was generating a script through the ScriptTransfer method, create the new database and then apply the script to the new database through Invoke-SqlCmd. The script uses theSQLPS package.

The code I am sharing can be invoked locally, by passing 4 arguments to the script in the following order:

  1. Server Name
  2. Database Name
  3. Login
  4. Password

And it can also be used on a pipeline. I am using it on Azure DevOps by setting those 4 arguments through a group variable.

I am appending .Staging to the source database name, and that's the name I give to the new database. If something fails along the way, I delete the new database, in case it has already been created.

$SQLInstanceName = $env:servername
$SourceDBName = $env:databasename
$SQLUser = $env:adminlogin
$SQLPassword = $env:adminPassword

Import-Module SQLPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

Function IsNullOrEmpty([string]$val){
    if ($val -eq $null -or $val -eq '') { $true }
    else{ $false }
}

If (IsNullOrEmpty($SQLInstanceName)) {
    $SQLInstanceName = $args[0]
}

If (IsNullOrEmpty($SourceDBName)) {
    $SourceDBName = $args[1]
}

If (IsNullOrEmpty($SQLUser)) {
    $SQLUser = $args[2]
}

If (IsNullOrEmpty($SQLPassword)) {
    $SQLPassword = $args[3]
}

Try {
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLInstanceName)
}
Catch [System.Exception] {
    # $_ is set to the ErrorRecord of the exception
    if ($_.Exception.InnerException) {
        Write-Error $_.Exception.InnerException.Message
    } else {
        Write-Error $_.Exception.Message
    }
}
Finally {

    Try {
        $StagingDBName      = "${SourceDBName}.Staging"
        $SQLSecurePassword  = ConvertTo-SecureString $SQLPassword -AsPlainText -Force

        $Server.ConnectionContext.LoginSecure = $false
        $Server.ConnectionContext.set_Login($SQLUser)
        $Server.ConnectionContext.set_SecurePassword($SQLSecurePassword)

        $CreationScriptOptions = New-Object Microsoft.SqlServer.Management.SMO.ScriptingOptions
        $CreationScriptOptions.ExtendedProperties= $true
        $CreationScriptOptions.DRIAll= $true
        $CreationScriptOptions.Indexes= $true
        $CreationScriptOptions.Triggers= $true            $CreationScriptOptions.ScriptBatchTerminator = $true
        $CreationScriptOptions.IncludeHeaders = $true;
        $CreationScriptOptions.ToFileOnly = $true
        $CreationScriptOptions.IncludeIfNotExists = $true     
        $SourceDB = $Server.Databases[$SourceDBName]
        $ObjTransfer = New-Object Microsoft.SqlServer.Management.SMO.Transfer ($SourceDB)
        $ObjTransfer.options=$CreationScriptOptions # tell the transfer object of our preferences

        $FilePath = Join-Path $PSScriptRoot "$($StagingDBName).sql"
        $ObjTransfer.Options.Filename = $FilePath; 
        $ObjTransfer.ScriptTransfer()

        $CopyDB = New-Object Microsoft.SqlServer.Management.SMO.Database ($Server, $StagingDBName)
        $CopyDB.Create()

        $auth=@{UserName=$SQLUser;Password=$SQLPassword}
        Invoke-SqlCmd -InputFile $FilePath -ServerInstance $Server -Database $StagingDBName @Auth -Verbose
    }
    Catch [System.Exception] {
        # $_ is set to the ErrorRecord of the exception
        if ($_.Exception.InnerException) {
            Write-Error $_.Exception.InnerException.Message
        } else {
            Write-Error $_.Exception.Message
        }

        if($Server.Databases.Name -like $StagingDBName) {
            Write-Host "Dropping staging database..."

            $auth=@{UserName=$SQLUser;Password=$SQLPassword}
            Invoke-SqlCmd -ServerInstance $Server @Auth `
                -Query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name ='$($StagingDBName)') `
                            BEGIN `
                                ALTER DATABASE [$($StagingDBName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; `
                                DROP DATABASE [$($StagingDBName)]; `
                            END;" `
                -Verbose
        }
    }
    Finally {
        $Server.ConnectionContext.Disconnect()
    }
}
| improve this answer | |
  • 1
    Please don't use the sqlps module. It was superseded four years ago by sqlserver, is much faster, fixes a lot of bugs, and is still actively being developed. – alroc Apr 21 at 21:14
  • Thank you @alroc. I changed the answer in order to follow your advice. – ccoutinho Apr 27 at 2:58
  • @alroc I noticed that as soon as I started using the SqlServer package, it became possible to use a Microsoft.SqlServer.Management.SMO.Transfer object to actually create a database. Something I haven't been able to achieve with the SQLPS package (explained in my answer). Do you think the problem I was having is related with the use of the SQLPS package? – ccoutinho Apr 27 at 22:03
  • 1
    You're working with newer versions of SMO and the PowerShell functions with lots of enhancements and bug fixes over the dozen years since sqlps was released. It stands to reason that more things would be working better now, and new things are now possible. I haven't used this functionality specifically, and don't recall the last time I used sqlps or SqlServer, as I use dbatools and haven't looked to see what I'm missing out on from SqlServer. – alroc Apr 28 at 1:14
  • Makes sense! Thanks for bringing it up and for the explanation – ccoutinho Apr 28 at 1:24

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.