谁会用dtc进行数据备份与还原了，普通的数据库备份与还原太慢了！

[xingxingxiangrong]

rt!

[xingxingxiangrong]

我拿sql导出一个bas文件，但不知道怎么用了

[xingxingxiangrong]

UP

[cqq_chen]

看明白引用了什么文件，把包导入到工程中就可以使用了。

[xingxingxiangrong]

引用了，但生成的bas文件运行没没反应！

[cqq_chen]

Private Sub Main()
-->
Public Sub Main()

在from中调用 main就行，试看看。

[xingxingxiangrong]

[Quote=引用 5 楼 cqq_chen 的回复:]
Private Sub Main()
-->
Public Sub Main()

在from中调用 main就行，试看看。
[/Quote]
试过了，没反应，也不提示错误

[cqq_chen]

怪!我试过，都可行!!

[xingxingxiangrong]

是么，那你给我打包一个工程发到我的邮箱 [e-mail] ，谢谢了

[cqq_chen]

把你的bas代码copy看看!

[xingxingxiangrong]

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
        Set goPackage = goPackageOld

        goPackage.Name = "新建包"
        goPackage.Description = "DTS 包描述"
        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 connection information
'---------------------------------------------------------------------------

Dim oConnection As DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("User ID") = "sa"
        oConnection.ConnectionProperties("Initial Catalog") = "te"
        oConnection.ConnectionProperties("Data Source") = "(local)"
        oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"
        
        oConnection.Name = "连接1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "(local)"
        oConnection.UserID = "sa"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "te"
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("User ID") = "sa"
        oConnection.ConnectionProperties("Initial Catalog") = "te"
        oConnection.ConnectionProperties("Data Source") = "(local)"
        oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"
        
        oConnection.Name = "连接2"
        oConnection.ID = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "(local)"
        oConnection.UserID = "sa"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "te"
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' 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 Data from test to [te].[dbo].[test] 步骤"
        oStep.Description = "Copy Data from test to [te].[dbo].[test] 步骤"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from test to [te].[dbo].[test] 任务"
        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 Data from test to [te].[dbo].[test] 任务 (Copy Data from test to [te].[dbo].[test] 任务)
Call Task_Sub1(goPackage)

[xingxingxiangrong]

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
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


'------------- define Task_Sub1 for task Copy Data from test to [te].[dbo].[test] 任务 (Copy Data from test to [te].[dbo].[test] 任务)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask

        oCustomTask1.Name = "Copy Data from test to [te].[dbo].[test] 任务"
        oCustomTask1.Description = "Copy Data from test to [te].[dbo].[test] 任务"
        oCustomTask1.SourceConnectionID = 1
        oCustomTask1.SourceSQLStatement = "select [id],[name] from [te].[dbo].[test]"
        oCustomTask1.DestinationConnectionID = 2
        oCustomTask1.DestinationObjectName = "[te].[dbo].[test]"
        oCustomTask1.ProgressRowCount = 1000
        oCustomTask1.MaximumErrorCount = 0
        oCustomTask1.FetchBufferSize = 1
        oCustomTask1.UseFastLoad = True
        oCustomTask1.InsertCommitSize = 0
        oCustomTask1.ExceptionFileColumnDelimiter = "|"
        oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
        oCustomTask1.AllowIdentityInserts = True
        oCustomTask1.FirstRow = 0
        oCustomTask1.LastRow = 0
        oCustomTask1.FastLoadOptions = 2
        oCustomTask1.ExceptionFileOptions = 1
        oCustomTask1.DataPumpOptions = 0
        
Call oCustomTask1_Trans_Sub1(oCustomTask1)
                
                
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DirectCopyXform"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("id", 1)
                        oColumn.Name = "id"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 16
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("name", 2)
                        oColumn.Name = "name"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 104
                        oColumn.Size = 50
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("id", 1)
                        oColumn.Name = "id"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 16
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("name", 2)
                        oColumn.Name = "name"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 104
                        oColumn.Size = 50
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

                
        Set oTransProps = Nothing

        oCustomTask1.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub

[xingxingxiangrong]

运行还没没动静

[cqq_chen]

Private Sub Main()
你没有修改啊!兄弟仔细一些

[cqq_chen]

看第5楼的回答，再试一次。

[嗷嗷叫的老马]

汗,楼上很久没出现了.......

[xingxingxiangrong]

[Quote=引用 14 楼 cqq_chen 的回复:]
看第5楼的回答，再试一次。
[/Quote]
改了Public Sub Main()运行完还是没动静，到底是为什么了


Private Sub Command1_Click()
Main
End Sub


或是直接设sub main 为启动项都不行

[cqq_chen]

跟踪得到吗？我的和你的很类似，我是用来导入大量Access中的数据的，没有关系。

[xingxingxiangrong]

我是sql--〉sql 一个数据库上导出了
不是sql-->access,而且是在一个数据库上导出，相当于备份，不知道我理解的对么

[cqq_chen]

我的是Access->sql
这根本就不是问题了。

如果你把这理解成备份，问题就大了。

数据结构，比如索引、主键、check之类的用这种方式是不能copy的。特别是自动ID更是大有问题。

[cqq_chen]

不能再回答了，以后很长一段时间内不上csdn了。

老版的csdn没事还可以看看，新版的怎么看怎么不舒服。


其实已经很长时间没有做技术，这两天有空才上来看看，结果一个老朋友也没看到，看来大家都已经不上了，哎!

做技术挺累人的，不过问题解决了，也很有乐趣。

老了，现在应该是年轻人的天下了，加油啊!年青人!!!