Transfer DBs between SQL2K, SQL2K8

• schleep

  SSChampion

  Points: 12586

  More actions

  April 18, 2011 at 7:57 am

  #1312796

  I guess at the end of the day, I want to understand

  1) why the transfer method fails on 2 out of 53 databases, given that the syscontraints view exists in all of them. (I've checked the status in sysobjects, it's identical across the board)

  2) As a system object, why is it being scripted at all?

  3) How can explicitly exclude it in my Powershell script?

• schleep

  SSChampion

  Points: 12586

  More actions

  April 20, 2011 at 1:36 pm

  #1314185

  I got it working, finally!!!

  I thought I would share what I've learned the hard way about transferring data between a SQL 2K sp4 source server and a SQL 2K8 R2 destination server. The $MovingBox variable from the example posted on the MSDN site was invaluable.

  1) $xfr.Options.AllowSystemObjects = $false

  In my case, the transfer class still tried to copy sysconstraints, which caused errors on the destination.

  2) $dstdb.DatabaseOptions.QuotedIdentifiersEnabled = $true

  This was not enough to allow the creation of a unique constraint on a computed column.

  Neither was setting the value as a default connection option on the destination server.

  I had to create the constraint in a script post-transfer. This is apparently a known bug.

  (Also, had to drop the constraint in a script pre-transfer, and re-create at the source post-transfer.

  #================= BEGIN CODE ==================

  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Database")

  $SrcSrv = "<SourceServer>"

  $DstSrv = "<DestinationServer>"

  $Ssrv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $SrcSrv

  $Dsrv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $DstSrv

  $dbExceptions = "[<AnyDBYouDontWantTransferred]",...

  #$ssrvdbs = $Ssrv.Databases | Where-Object {$_.name -eq "<SingleDBToTranfer>"}

  $ssrvdbs = $Ssrv.Databases # All DBs.

  $sdb = $null

  # "Option Explicit"

  set-psdebug -strict

  foreach($sdb in $ssrvdbs) {

  if (($dbExceptions -contains $sdb) -eq $false) # Skip DBs in the exception list.

  {

  $tTime = Get-Date

  $dbname = "$sdb"

  $dbname = $dbname.Replace("[", "")

  $dbname = $dbname.Replace("]", "")

  Write-host -BackgroundColor Red $tTime ": Starting DB: "$dbname

  #If destination db does not exist, create it

  $dstdb = $dsrv.Databases["$dbname"]

  if (!$dstdb)

  {

  $dbCopy = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Database -argumentlist $dsrv, "$dbname"

  $dbCopy.Create()

  $dstdb = $dsrv.Databases["$dbname"]

  }

  # Set recovery mode to simple for the transfer

  $dstdb.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple

  # Set Quoted Identifiers

  $dstdb.DatabaseOptions.QuotedIdentifiersEnabled = $true

  $dstdb.DatabaseOptions.AnsiNullsEnabled = $true

  $dstdb.DatabaseOptions.AnsiWarningsEnabled = $true

  $dstdb.DatabaseOptions.ArithmeticAbortEnabled = $true

  $dstdb.DatabaseOptions.ConcatenateNullYieldsNull = $true

  # Apply new settings

  $dstdb.Alter()

  # Here, do anything that needs to be done Pre-transfer on Source Server and/or Destination Server

  # For example, I had an unique constraint on a computed column. For whatever reason, this transfer class

  # connects with QuotedIdentifier OFF, which prevents the Unique constraint from being created (also, crashes

  # the transfer. So I drop the constraint at the source, and re-create it post transfer using Invoke-SqlCmd

  # on a SQL script.

  #Define a Transfer object and set the required options and properties.

  $xfr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -argumentlist $sdb

  #Define a container for the objects to transfer.

  $moving_box = New-Object -TypeName "System.Collections.ArrayList"

  # Set Destination options

  $xfr.DestinationDatabase = $dbname

  $xfr.DestinationServer = $Dsrv.Name

  $xfr.DestinationLoginSecure = $true

  #Transfer object properties

  $xfr.PreserveDbo = $true

  $xfr.DropDestinationObjectsFirst = $true

  $xfr.CopyAllObjects = $false

  # $xfr.CopyAllDatabaseTriggers = $true

  # $xfr.CopyAllDefaults = $true

  # $xfr.CopyAllStoredProcedures = $true

  # $xfr.CopyAllTables = $true

  # $xfr.CopyAllUserDefinedFunctions = $true

  # $xfr.CopyAllUserDefinedTableTypes = $true

  # $xfr.CopyAllViews = $false

  # $xfr.CopyData = $true

  # $xfr.CopyAllRoles = $true

  # $xfr.CopyAllSchemas = $false

  # $xfr.CopyAllLogins = $false

  # $xfr.CopyAllUsers = $false

  # $xfr.CopySchema = $true

  # Transfer object options

  $xfr.Options.DriAll = $true

  $xfr.Options.DriForeignKeys = $true

  $xfr.Options.DriUniqueKeys = $true

  $xfr.Options.DriChecks = $true

  $xfr.Options.DriUniqueKeys = $true

  $xfr.Options.Triggers = $true

  $xfr.Options.Permissions = $true

  $xfr.Options.ClusteredIndexes = $true

  $xfr.Options.NonClusteredIndexes = $true

  $xfr.Options.AllowSystemObjects = $false

  # $xfr.Options.IncludeDatabaseRoleMemberships = $false

  $xfr.Options.ContinueScriptingOnError = $true

  $xfr.Options.WithDependencies = $false

  # This section was necessary for a couple of reasons: simply setting the transfer object properties (the lines above

  # which are commented out, and begin with $xfr.CopyAllxxx) resulted in objects being scripted in the wrong order,

  # i.e., functions failing on create because the underlying tables had not yet been created.

  #Objects to copy

  $sdb.Roles | where {$_.IsFixedRole -eq $false -and $_.name -ne "public"} | foreach {$null = $moving_box.Add($_)}

  # $sdb.Tables | where {$_.IsSystemObject -eq $false -and ($_.name -eq "Keywords" -or $_.name -eq "ContLang_Type")} | foreach {$null = $moving_box.Add($_)}

  $sdb.Tables | where {$_.IsSystemObject -eq $false} | foreach {$null = $moving_box.Add($_)}

  $sdb.UserDefinedFunctions | where {$_.IsSystemObject -eq $false} | foreach {$null = $moving_box.Add($_)}

  $sdb.StoredProcedures | where {$_.IsSystemObject -eq $false} | foreach {$null = $moving_box.Add($_)}

  $sdb.Views | where {$_.IsSystemObject -eq $false} | foreach {$null = $moving_box.Add($_)}

  $xfr.ObjectList = $moving_box

  $xfr.TransferData()

  # Scripting Data Transfer

  # $xfr.Options.ToFileOnly = $true

  # $xfr.Options.FileName = "C:\XFer.sql"

  # $xfr.ScriptTransfer()

  # Do the transfer

  # $xfr.TransferData()

  # Set dst db compatibility level to 8.

  #$Command = "EXEC sp_dbcmptlevel '$dbname', 100"

  #Invoke-sqlcmd -ServerInstance $DstSrv -Database $dbname -Query $Command

  # Any post-transfer stuff goes here, for instance, my dropped Unique constraint from above,

  # re-created on Source and Destination DBs.

  # Set recovery model back to Full, other DB options

  $dstdb.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full

  $dstdb.Alter()

  $tTime = Get-Date

  Write-host -BackgroundColor Green $tTime ": Completed DB: "$dbname

  }

  }

  #================= END CODE ==================