SQL Server Performance

transfer database

Discussion in 'SQL Server DTS-Related Questions' started by DB King, Apr 4, 2006.

  1. DB King New Member


    I am trying to use DTS to copy databases using the transfer database object and after I select the locations tab EM freezes.

    I have 2 instances on the same machine named: RIZ and RIZB

    I am copying DB with one table from RIZ to RIZB . I am only doing this to test the process which I will then deploy onto our production box to a backup machine (for those wondering: this procedure is not meant to cover High Availabity).

    I just want to copy the databases from one server to the next.


  2. mmarovic Active Member

    How about backup/restore?
  3. dineshasanka Moderator

  4. DB King New Member

    mmarovic/dineshasanka: As these databases are on our live servers I do not want to bring them down unless there is no other choice.http://www.dbazine.com/sql/sql-articles/sharma3 provides these options but there is no mention of excluding indexes and triggers. Where did you get that information from ? Just need to know to convince my superiors
  5. dineshasanka Moderator

  6. DB King New Member

    ok, will do. What about the by right clicking the DB and going to Export you can select the object you wish to export to a the other server. Will that work ?
  7. dineshasanka Moderator

  8. DB King New Member

    ok, just managed to export a database and it has exported the indexed I created in a table in the source db.

    Am I doing something right/wrong here ?
  9. dineshasanka Moderator

  10. DB King New Member

    how are you checking it ? through EM or QA ?
  11. dineshasanka Moderator

  12. Adriaan New Member

    You mean the primary key constraint did not get created? Same for triggers, foreign key constraints, relationships ...
  13. dineshasanka Moderator

  14. DB King New Member

    Because this worked I intend to use a bas file to run the same process for all databases. I will write a vbs file which will use the code below. The only thing, I am running the vbs using the following command:

    cscript cdw.vbs

    but its throwing the following error:

    U:cdw.vbs(2, 21) Microsoft
    VBScript compilation error: Expected end of statement

    Any ideas ???

    DTS package VBS CODE:

    Option Explicit
    Public goPackageOld As New DTS.Package
    Public goPackage As DTS.Package2

    Private Sub Main()
    set goPackage = goPackageOld

    goPackage.Name = "New Package"
    goPackage.Description = "DTS package description"
    goPackage.WriteCompletionStatusToNTEventLog = False
    goPackage.FailOnError = False
    goPackage.PackagePriorityClass = 2
    goPackage.MaxConcurrentSteps = 4
    goPackage.LineageOptions = 0
    goPackage.UseTransaction = True
    goPackage.TransactionIsolationLevel = 4096
    goPackage.AutoCommitTransaction = True
    goPackage.RepositoryMetadataOptions = 0
    goPackage.UseOLEDBServiceComponents = True
    goPackage.LogToSQLServer = False
    goPackage.LogServerFlags = 0
    goPackage.FailPackageOnLogFailure = False
    goPackage.ExplicitGlobalVariables = False
    goPackage.PackageType = 0

    Dim oConnProperty As DTS.OleDBProperty

    ' create package steps information

    Dim oStep as DTS.Step2
    Dim oPrecConstraint as DTS.PrecedenceConstraint

    '------------- a new step defined below

    Set oStep = goPackage.Steps.New

    oStep.Name = "Copy SQL Server Objects"
    oStep.Description = "Copy SQL Server Objects"
    oStep.ExecutionStatus = 1
    oStep.TaskName = "Copy SQL Server Objects"
    oStep.CommitSuccess = False
    oStep.RollbackFailure = False
    oStep.ScriptLanguage = "VBScript"
    oStep.AddGlobalVariables = True
    oStep.RelativePriority = 3
    oStep.CloseConnection = False
    oStep.ExecuteInMainThread = False
    oStep.IsPackageDSORowset = False
    oStep.JoinTransactionIfPresent = False
    oStep.DisableStep = False
    oStep.FailPackageOnError = False

    goPackage.Steps.Add oStep
    Set oStep = Nothing

    ' create package tasks information

    '------------- call Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
    Call Task_Sub1( goPackage )

    ' Save or execute package

    'goPackage.SaveToSQLServer "(local)", "sa", ""
    tracePackageError goPackage
    'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
    set goPackage = Nothing

    set goPackageOld = Nothing

    End Sub

    ' error reporting using step.GetExecutionErrorInfo after execution
    Public Sub tracePackageError(oPackage As DTS.Package)
    Dim ErrorCode As Long
    Dim ErrorSource As String
    Dim ErrorDescription As String
    Dim ErrorHelpFile As String
    Dim ErrorHelpContext As Long
    Dim ErrorIDofInterfaceWithError As String
    Dim i As Integer

    For i = 1 To oPackage.Steps.Count
    If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
    oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
    ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
    MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
    End If
    Next i

    End Sub

    '------------- define Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
    Public Sub Task_Sub1(ByVal goPackage As Object)

    Dim oTask As DTS.Task
    Dim oLookup As DTS.Lookup

    Dim oCustomTask1 As DTS.TransferObjectsTask2
    Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
    oTask.Name = "Copy SQL Server Objects"
    Set oCustomTask1 = oTask.CustomTask

    oCustomTask1.Name = "Copy SQL Server Objects"
    oCustomTask1.Description = "Copy SQL Server Objects"
    oCustomTask1.SourceServer = "(LOCAL)"
    oCustomTask1.SourceUseTrustedConnection = True
    oCustomTask1.SourceDatabase = "saj01"
    oCustomTask1.DestinationServer = "rizvis"
    oCustomTask1.DestinationUseTrustedConnection = True
    oCustomTask1.DestinationDatabase = "saj01"
    oCustomTask1.ScriptFileDirectory = "C:program FilesMicrosoft SQL Server80Tools"
    oCustomTask1.CopyAllObjects = True
    oCustomTask1.IncludeDependencies = True
    oCustomTask1.IncludeLogins = True
    oCustomTask1.IncludeUsers = True
    oCustomTask1.DropDestinationObjectsFirst = True
    oCustomTask1.CopySchema = True
    oCustomTask1.CopyData = 1
    oCustomTask1.ScriptOption = -2146995969
    oCustomTask1.ScriptOptionEx = 4722704
    oCustomTask1.SourceTranslateChar = True
    oCustomTask1.DestTranslateChar = True
    oCustomTask1.DestUseTransaction = False
    oCustomTask1.UseCollation = True

    goPackage.Tasks.Add oTask
    Set oCustomTask1 = Nothing
    Set oTask = Nothing

    End Sub

  15. Adriaan New Member

    quote:Originally posted by dineshasanka



    Exactly. You won't see any constraints in an exported object. And you won't see them in an imported object either, for that matter. It's just not covered by DTS. You'll have to add the constraints through script.
  16. DB King New Member

    then why have microsoft added a feature which doesnt work at all in DTS ?
  17. DB King New Member

    Adriaan. I cant understand what you are saying becuase I have:

    Source instance (SI) with DB (A)
    Destination Instance (DI) with DB (B)

    within DB (A) I create 2 tables and make a 1:N relationship between them.

    I export SI.(A) to DI.(B) using DTS and all the constraints are still as they are in SI.(A)

    I have checked this using enterprise manager. This is contrary to your statement above ? or am I missing something again ?
  18. Adriaan New Member

    Hm, there's probably some settings that are not switched on by default. I remember the problem coming up in Enterprise Manager, if you went throught the DTS wizard for importing tables from one DB on the server into another - we missed most, if not all constraints.
  19. DB King New Member

    ok, but it works once you select the right stuff.

    I think the 3rd or 4th page of the wizard called: Select Object to Copy
    1) uncheck the box: Us default options
    2) click the options button to the right (enabled now)
    3) make sure the following are selected:
    Copy indexes
    Copy triggers
    Copy full text indexes
    Copy PRIMARYU and FOREIGN key
    4) click ok
    5) click Next
    6) click Finish

    Once the transfer has finished - its done !?
  20. FrankKalis Moderator

  21. Adriaan New Member

    DB King - thanks for pointing out the details of the wizard.

    I took the lesson learnt as a warning against using the wizard - just script the complete object, create the object in the target database, then copy over the data.
  22. DB King New Member


    I thought I pasted a similar article earlier. However, This one is better and thanks a lot. It is totaly comprehensively what I need.

    I like to present evidence to my superiors if im stuck with someone from a reputable source. MS .com and SQL-Server-performance in my mind are the most authentic sources.

    I too had a very bad experience with DTS. We has a junior developer copy object to his local instance, in the process which failed somehow, the transfer deleted all indexes and relationships from our production server and 1 week later when I found out, SH*T hit the fan !!!!! It took me an entire weekend to recover 99 % the data using manual techniques coz the data in the tables didnt balance.

    I can empathise.

    Thanks guys.

    Last thing: How do I run the above bas file in VBS ?
  23. Adriaan New Member

    DB King - no need to apologize!
  24. DB King New Member

    i didnt !!!!
  25. Adriaan New Member

    Well, you didn't. Still no need to apologize, then.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]

Share This Page