we often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table list dynamic every time using a meta data table. When i started to develop the SSIS to accomplish this task i realised the "Transfer SQL Server Objects Task" does not accept variable of Object data type with the table list as expression. So i decided to use SMO to copy SQL server objects using xfr.TransferData() function which has more flexible option.
You can change various options to the below script, I have created the script to create a table list and migrate to destination with data.
First you'll need to add references to the three SMO assemblies (found under Microsoft SQL Server\90\SDK\Assemblies):
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim dbSourceName As String
Dim dbDestName As String
Dim tbl_name1 As String
Dim SQLServername As String
'Set Source and destination database
dbSourceName = Dts.Variables("sourcedb_name").Value.ToString()
dbDestName = Dts.Variables("destdb_name").Value.ToString()
'Connect to the local, default instance of SQL Server.
SQLServername = Dts.Variables("SQL_Name").Value.ToString()
Dim srv As Server
srv = New Server(SQLServername)
'Reference the source database
Dim db As Database
db = srv.Databases(dbSourceName)
'Reference the destination database.
Dim dbCopy As Database
dbCopy = New Database(srv, dbDestName)
'Table name
tbl_name1 = Dts.Variables("Tbls_name").Value.ToString()
'Define a Transfer object and set the required options.
'MsgBox(tbl_name1)
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllDatabaseTriggers = False
xfr.CopyAllDefaults = False
xfr.CopyAllLogins = False
xfr.CopyAllObjects = False
xfr.CopyAllPartitionFunctions = False
xfr.CopyAllPartitionSchemes = False
xfr.CopyAllRoles = False
xfr.CopyAllRules = False
xfr.CopyAllSchemas = False
xfr.CopyAllSqlAssemblies = False
xfr.CopyAllStoredProcedures = False
xfr.CopyAllSynonyms = False
xfr.CopyAllTables = False
xfr.CopyAllUserDefinedAggregates = False
xfr.CopyAllUserDefinedDataTypes = False
xfr.CopyAllUserDefinedFunctions = False
xfr.CopyAllUserDefinedTypes = False
xfr.CopyAllUsers = False
xfr.CopyAllViews = False
xfr.CopyAllXmlSchemaCollections = False
xfr.CreateTargetDatabase = False
xfr.DropDestinationObjectsFirst = False
xfr.PrefetchObjects = False
xfr.SourceTranslateChar = False
'Add Table to the List
xfr.ObjectList.Add(db.Tables(tbl_name1))
xfr.Options.WithDependencies = True
xfr.Options.Indexes = True
xfr.Options.DriAll = True
xfr.CopySchema = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srv.Name
'Include data
xfr.CopyData = True
'Execute the transfer
xfr.TransferData()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
Thanks a lot! Your article is pretty good. I like it
ReplyDeleteSQL server will definitely be very helpful to face these type of situations very easily. coursework point | buy coursework online | do my coursework
ReplyDeleteExcellent and Awonderful read! Your article is the best one I haave learnt.
ReplyDeleteFantastic Post.thanks for share..a lot more wait ..
ReplyDeleteThanks for useful information.
ReplyDeleteHi, after using this method I realised the table schema is not specified. Do you know how to specify the schema?
ReplyDeleteThanks!
Get the best essay help right now online!
ReplyDeleteWhat if I want to copy Different tables to different target MDF files..
ReplyDeleteThanks,
Saurabh
I am really glad to see this post. thanks. customessayonline.com/buy-essay
ReplyDeleteMM, interesting idea to use xfr. for servers. Thank for detailed explaining on how to do that right! Keep on writing! If some day you need help in writing then go to http://www.papers-writings.com
ReplyDeletewonderful blog but i often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table list dynamic every time using a meta data table. When i started to develop the SSIS to accomplish this task i realised the "Transfer SQL Server Objects Task" does not accept variable of Object data type with the table list as expression. So i decided to use SMO to copy SQL server objects using xfr.TransferData() function which has more flexible option.
ReplyDeletecoursework Writing Service
Social Media Optimization (SMO) is one of the methods in website optimization which involves a set of activities which are implemented by the business houses to attract new visitors by driving traffic to the websites from other sources, other than search engines.
ReplyDeleteGreat that you described it is very important to be interested in it.
ReplyDeleteWatch all movie and TV show with Pluto TV App on your Smartphone for free.
ReplyDeleteUtilizing diverse social media SEO techniques local seo services in gurgaon is a decent method to grow your compass without applying much as far as assets and endeavors.
ReplyDeleteI really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. 192.168.l.l
ReplyDeleteGbwhatsapp is the best messenger application which is used on all the smartphones.
ReplyDeleteGbwhatsapp
The gaudy and an excessive amount of ornamentation has never been a quality of the moderate website plan.Exclusive Web Ltd
ReplyDeleteA Business to business exchange is a tremendous worth exchange as buy amount is very high where as business to customer exchange is a little esteem exchange.fluencer
ReplyDeleteOn the off chance that an obscure SEO business was going to trick, at that point I am certain this is the place they would begin, with tributes.Webdesign
ReplyDelete