transfer database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

transfer database

hi 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. help. Regards
How about backup/restore?
from DTS, it will not create indexes and trigeers if exists. so better to use restore option —————————————-
http://spaces.msn.com/members/dineshasanka
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
Just try it and see. It wont be that difficult. —————————————-
http://spaces.msn.com/members/dineshasanka
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 ?
it is same as DTS —————————————-
http://spaces.msn.com/members/dineshasanka
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 ?
i just chekced and for me I didn;t get in primary indexes!! —————————————-
http://spaces.msn.com/members/dineshasanka
how are you checking it ? through EM or QA ?
through EM —————————————-
http://spaces.msn.com/members/dineshasanka
You mean the primary key constraint did not get created? Same for triggers, foreign key constraints, relationships …
YES —————————————-
http://spaces.msn.com/members/dineshasanka
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", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
‘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
quote:Originally posted by dineshasanka YES —————————————-
http://spaces.msn.com/members/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.
then why have microsoft added a feature which doesnt work at all in DTS ?
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 ?
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.
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 !?
I wonder why nobody mentionedhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;314546 yet? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
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.
FrankKalis: 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. Adriaan:
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 ?
DB King – no need to apologize!
i didnt !!!!
Well, you didn’t. Still no need to apologize, then.[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
]]>