I can already create a database but when I use the code below, it transfers all the tables without any records. Additionally how would I code that only a single specific table will be transferred?
Dim conn As New ServerConnection("NPSS-OJT", "sa", "rms")
Dim srv As New Server(conn)
Dim db As Database = srv.Databases("bos_primary_db")
Dim dbCopy As Database = New Database(srv, Main.dbName)
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllTables = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.TransferData()
Updated Code:
Shared Sub transferTables()
Dim conn As New ServerConnection("NPSS-OJT", "sa", "rms")
Dim srv As New Server(conn)
Dim db As Database = srv.Databases("bos_primary_db")
Dim dbCopy As Database = New Database(srv, Main.dbName)
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllTables = False
xfr.Options.WithDependencies = False
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.CopyData = True
xfr.Options.DriAll = False
xfr.Options.DriDefaults = True
xfr.Options.DriIndexes = True
xfr.Options.DriPrimaryKey = True
xfr.Options.DriUniqueKeys = True
xfr.Options.DriForeignKeys = False
xfr.ObjectList.Add("Accounting")
xfr.TransferData()
End Sub
You should set
xfr.CopyAllTables = False
xfr.Options.WithDependencies = False
also you should transfer keys
xfr.Options.DriAll = False
xfr.Options.DriDefaults = True
xfr.Options.DriIndexes = True
xfr.Options.DriPrimaryKey = True
xfr.Options.DriUniqueKeys = True
xfr.Options.DriForeignKeys = False
and then
EDIT (I changed adding of the table in the list of transfered objects)
xfr.ObjectList.Add(db.Tables("table_name"))
xfr.TransferData()
To transfer specified object between databases you need to add this object to ObjectList
but not just the object's name.