Sql-Server

嘗試使用 SMO 傳輸對象將數據庫從一個實例傳輸到另一個實例

  • April 27, 2020

模式表由 dbo 和另一個模式名稱擁有。下面的程式碼不會在任何表格上移動。我還需要添加其他內容以使其轉移表格嗎？沒有需要移動的儲存過程或視圖。

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

目前錯誤堆棧如下：

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 : {}

由於我不確切知道您遇到了什麼錯誤，所以我只會根據您的程式碼和我選擇測試的數據庫讓您知道我遇到了什麼錯誤。

我會注意到您正在使用的大部分屬性在 SQL Server 2012 SMO 中無效（至少在我的機器上）

我用於腳本的最終結果在最後。在我的情況下，我遇到了一些不同的錯誤：

使用“0”參數呼叫“TransferData”的異常：“傳輸數據時發生錯誤。有關詳細資訊，請參閱內部異常。” 在行：46 字元：1

如果您收到此錯誤，您可以檢查Exception屬性$error並找到原因： 在此處輸入圖像描述

我收到上述錯誤是因為我已經執行過一次並且它部分創建了對象。

我得到的唯一其他錯誤本質上是相似的，我必須檢查Exception屬性，但其他錯誤與我選擇測試的數據庫過程中的程式碼有關。我更改為我擁有的數據庫，它只有兩個包含數百行數據的表，並且完成時沒有錯誤。

所以最後，如果你想編輯你的問題以提供你可能得到的確切錯誤，如果我能弄清楚任何事情，我可以調整答案。

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

編輯

@alroc 的評論說服我使用SqlServer模組而不是SQLPS模組，因為後者早已被棄用。在我做出改變之後，我注意到現在可以從一個Microsoft.SqlServer.Management.SMO.Transfer我以前沒有管理過的對象創建數據庫。我不明白為什麼，甚至可能無關，我只是幸運。該SqlServer軟體包可以通過以下命令安裝：

Install-Module -Name SqlServer -AllowClobber

因此，我正在使用工作程式碼更新我的答案，它比我之前的答案（在這篇文章的底部）更具可讀性、更優雅和更高效。

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

我在實現這個時遇到了類似的錯誤。從字面上嘗試了一切，它就是行不通。對我有用的是通過該ScriptTransfer方法生成腳本，創建新數據庫，然後通過Invoke-SqlCmd. 該腳本使用該SQLPS包。

通過按以下順序將 4 個參數傳遞給腳本，可以在本地呼叫我共享的程式碼：

  1. 伺服器名稱
  2. 數據庫名稱
  3. 登錄
  4. 密碼

它也可以用在管道上。我通過組變數設置這 4 個參數，在 Azure DevOps 上使用它。

我將附加.Staging到源數據庫名稱，這就是我給新數據庫的名稱。如果在此過程中出現問題，我會刪除新數據庫，以防它已經創建。

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

引用自：https://dba.stackexchange.com/questions/122092

相關問答

Sql-Server

為什麼 C# SMO 看不到列上的擴展屬性，但 Powershell SMO 可以？

  • August 13, 2017
檢視問答
Sql-Server

將數據庫設置為 SINGLE_USER 時，Powershell SMO 從 master 更改數據庫上下文

  • December 6, 2021
檢視問答
Sql-Server

用於以 HTML 格式發送帶有 SQL 結果的電子郵件的 PowerShell 腳本

  • May 7, 2020
檢視問答
Sql-Server

顯示執行標誌但目前未執行的作業

  • September 26, 2019
檢視問答
Sql-Server

SMO、Powershell 和登錄失敗

  • August 22, 2019
檢視問答
Sql-Server

將 SQL Server 結果轉換為 PowerShell 可以訪問和使用的文本文件

  • April 19, 2019
檢視問答