Transfer DBs between SQL2K, SQL2K8

Question

Transfer DBs between SQL2K, SQL2K8

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

schleep SSChampion Points: 12587 More actions · Answer 1

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: 12587 More actions · Answer 2

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 ==================