Copy SQL Server database with PowerShell script

Question

I want to copy database within the same server to have a test database using the under code but it works fine the first run and then an error occur .I think that was a problem of the name of the destination database because i change the name of destination it works also .How can I proceed to override the destination database without renaming the destination.

 Import-Module SQLPS -DisableNameChecking

        #your SQL Server Instance Name
        $SQLInstanceName = "DESKTOP-444"
        $Server  = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

        #provide your database name which you want to copy
        $SourceDBName   = "test"

        #create SMO handle to your database
        $SourceDB = $Server.Databases[$SourceDBName]

        #create a database to hold the copy of your source database
        $CopyDBName = "$($SourceDBName)_copy"
        $CopyDB = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Database -ArgumentList $Server , $CopyDBName

    $CopyDB.Create()

    #Use SMO Transfer Class by specifying source database
    #you can specify properties you want either brought over or excluded, when the copy happens
    $ObjTransfer   = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $SourceDB
    $ObjTransfer.CopyAllTables = $true
    $ObjTransfer.Options.WithDependencies = $true
    $ObjTransfer.Options.ContinueScriptingOnError = $true
    $ObjTransfer.DestinationDatabase = $CopyDBName
    $ObjTransfer.DestinationServer = $Server.Name
    $ObjTransfer.DestinationLoginSecure = $true
    $ObjTransfer.CopySchema = $true

    #if you wish to just generate the copy script
    #just script out the transfer
    $ObjTransfer.ScriptTransfer()

    #When you are ready to bring the data and schema over,
    #you can use the TransferData method
    $ObjTransfer.TransferData()

Answer 1

I was able to run your code multiple times without any issues. The following is the slightly cleaned-up version (structural changes):

Import-Module SQLPS -DisableNameChecking

$SQLInstanceName = "(local)"
$SourceDBName   = "sandbox"
$CopyDBName = "${SourceDBName}_copy"

$Server  = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $SQLInstanceName
$SourceDB = $Server.Databases[$SourceDBName]
$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -ArgumentList $Server , $CopyDBName
$CopyDB.Create()

$ObjTransfer   = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $SourceDB
$ObjTransfer.CopyAllTables = $true
$ObjTransfer.Options.WithDependencies = $true
$ObjTransfer.Options.ContinueScriptingOnError = $true
$ObjTransfer.DestinationDatabase = $CopyDBName
$ObjTransfer.DestinationServer = $Server.Name
$ObjTransfer.DestinationLoginSecure = $true
$ObjTransfer.CopySchema = $true

$ObjTransfer.ScriptTransfer()
$ObjTransfer.TransferData()

What error did you get?

The one thing I noticed. If the cloned database already exists, the script will fail. You should get an exception up around the $CopyDB.Create() statement and probably another one when you go to copy the objects to the cloned database.

I'd either drop the database if it exists, or abort script execution if it exists.

Answer 2

EDIT

I was told to use the module SqlServer instead of the module SQLPS, 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

Thus I am 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 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()
    }
}