sql-server - 使用 PowerShell 脚本复制 SQL Server 数据库

Question

我想在同一台服务器中复制数据库以使用下面的代码创建一个测试数据库，但它在第一次运行时工作正常，然后发生错误。我认为这是目标数据库名称的问题，因为我更改了目的地它也可以工作。如何在不重命名目的地的情况下继续覆盖目的地数据库。

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

标签： sql-serverpowershell

Answer 1

我能够多次运行您的代码而没有任何问题。以下是稍微清理过的版本（结构变化）：

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

你得到了什么错误？

我注意到的一件事。如果克隆的数据库已经存在，脚本将失败。当您将对象复制到克隆的数据库时，您应该在$CopyDB.Create()语句周围出现异常，并且可能会出现另一个异常。

如果数据库存在，我会删除它，或者如果它存在则中止脚本执行。