使用 PowerShell 脚本复制 SQL Server 数据库 [英] Copy SQL Server database with PowerShell script

问题描述

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

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

推荐答案

我能够多次运行您的代码，没有任何问题.以下是稍微清理过的版本(结构变化):

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

你得到了什么错误?

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

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.

这篇关于使用 PowerShell 脚本复制 SQL Server 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！