BUG: DTS Import/Export Wizard or TransferObjectsTask Fails to Maintain Filegroup Settings for Transferred Objects

Article translations Article translations
Article ID: 300192 - View products that this article applies to.
This article was previously published under Q300192
BUG #: 354301 (SHILOH_BUGS)
BUG #: 101735 (SQLBUG_70)
Expand all | Collapse all

On This Page

SYMPTOMS

If you use the Data Transformation Services (DTS) Import Wizard or the DTS Export Wizard to transfer objects from one database to another, the Filegroup settings for the object are not maintained even if the destination database does have the same filegroups.

Programs scripted using the TransferObjectsTask object from the DTS Package Object Library also fail to maintain the Filegroup settings for objects.

CAUSE

The wizards use the DTS Package Object Library to transfer objects between databases. The methods used are:
  • DTS.TransferObjectsTask2

    -and-

  • DTS.TransferObjectsTask.
Both of these methods, with or without the ScriptOptionEx property set to DTSTransfer_ScriptEx_NoFG, place the object on the primary Filegroup.

WORKAROUND

To work around this behavior, script out the objects on the source database and apply the scripts to the destination database. The Filegroup settings for the objects are now preserved. Use DTS to copy the data over from the source to the destination.

The Copy Database Wizard does maintain the Filegroup settings for all the objects in the database. However, using the Copy Database Wizard may not be convenient for the following reasons:

  • The Copy Database Wizard requires you to transfer the whole database to the destination server.

  • You cannot copy the database to the same server.

Using the SQL Server 2000 Enterprise Manager you can change the Filegroup settings for tables. Changing the Filegroup settings involves creating a new table with the correct Filegroup settings and then moving the data back into the table. Note that this may be too resource intensive a workaround for large tables.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a database named MYDB1 with Filegroups named [one] and [two]:
    CREATE DATABASE [mydb1]  ON (NAME = N'mydb1', 
    	FILENAME = N'c:\mydb1.MDF' , SIZE = 1, FILEGROWTH = 10%) 
    	LOG ON (NAME = N'mydb1_Log', FILENAME = N'c:\mydb1_Log.LDF' ,
    	SIZE = 1, FILEGROWTH = 10%)
    GO
    
    ALTER DATABASE [mydb1] ADD FILEGROUP [one] 
    GO
    ALTER DATABASE [mydb1] ADD FILE(NAME = N'mydb1_fgone', 
    	FILENAME = N'c:\mydb1_fgone_Data.NDF' , SIZE = 1, 
    	FILEGROWTH = 10%) TO FILEGROUP [one]
    GO
    
    ALTER DATABASE [mydb1] ADD FILEGROUP [two] 
    GO
    ALTER DATABASE [mydb1] ADD FILE(NAME = N'mydb1_fgtwo', 
    	FILENAME = N'c:\mydb1_fgtwo_Data.NDF' , SIZE = 1, 
    	FILEGROWTH = 10%) TO FILEGROUP [two]
    GO
  2. Create another database with the same structure:
    CREATE DATABASE [mydb2]  ON (NAME = N'mydb2', 
    	FILENAME = N'c:\mydb2.MDF' , SIZE = 1, FILEGROWTH = 10%) 
    	LOG ON (NAME = N'mydb2_Log', FILENAME = N'c:\mydb2_Log.LDF' ,
    	SIZE = 1, FILEGROWTH = 10%)
    GO
    
    ALTER DATABASE [mydb2] ADD FILEGROUP [one] 
    GO
    ALTER DATABASE [mydb2] ADD FILE(NAME = N'mydb2_fgone', 
    	FILENAME = N'c:\mydb2_fgone_Data.NDF' , SIZE = 1, 
    	FILEGROWTH = 10%) TO FILEGROUP [one]
    GO
    
    ALTER DATABASE [mydb2] ADD FILEGROUP [two] 
    GO
    ALTER DATABASE [mydb2] ADD FILE(NAME = N'mydb2_fgtwo', 
    	FILENAME = N'c:\mydb2_fgtwo_Data.NDF' , SIZE = 1, 
    	FILEGROWTH = 10%) TO FILEGROUP [two]
    GO
  3. Create the source TABLE to be copied by DTS on Filegroup [one].
    USE mydb1
    GO
    
    CREATE TABLE [dbo].[Table1] (
    	[x] [char] (10) NOT NULL ,
    	[y] [char] (10) NULL 
    ) ON [one]
    GO
  4. You do not have to complete this step, but here is some test data:
    INSERT INTO Table1 Values('a','x')
    INSERT INTO Table1 Values('b','w')
    INSERT INTO Table1 Values('c','v')
  5. Run the following Microsoft Visual Basic script from Visual Basic and then proceed to step 12. The Visual Basic code uses Trusted Connections so make sure this is not a problem.

    NOTE: For instructions about how to use the Import/Export Wizard to copy Table1 from mydb1 to mydb2 proceed to step 6.

    Private Sub RunTransfer(ByVal objPackage)
    Dim objStep         As DTS.Step
    Dim objTask         As DTS.Task
    Dim strServerName   As String
    Dim objXferObj
    
    'Create step and task
    Set objStep = objPackage.Steps.New
    Set objTask = objPackage.Tasks.New("DTSTransferObjectsTask")
    Set objXferObj = objTask.CustomTask
    
    strServerName = InputBox("Name of the source server ex. myserv1\instance1:")
    'Configure transfer objects task
    With objXferObj
        .Name = "XferObjTask"
        .SourceServer = strServerName
        .SourceUseTrustedConnection = True
        .SourceDatabase = "mydb1"
        .DestinationServer = strServerName
        .DestinationUseTrustedConnection = True
        .DestinationDatabase = "mydb2"
        .ScriptFileDirectory = "c:\winnt\temp"
        .CopyAllObjects = False
        .IncludeDependencies = False
        .IncludeLogins = False
        .IncludeUsers = False
        .DropDestinationObjectsFirst = True
        .CopySchema = True
        .CopyData = DTSTransfer_ReplaceData
        If MsgBox("Use the No Filegroup for replication option?", vbYesNo) = vbYes Then
            .ScriptOptionEx = DTSTransfer_ScriptEx_NoFG
        End If
        .AddObjectForTransfer "Table1", "dbo", DTSSQLObj_UserTable
    End With
    
    'Link step to task
    objStep.TaskName = objXferObj.Name
    objStep.Name = "XferObjStep"
    objPackage.Steps.Add objStep
    objPackage.Tasks.Add objTask
    End Sub
    
    
    Private Sub Form_Load()
    Dim x
    
    If MsgBox("Select Yes for TransferObjectsTask2 or No for TransferObjectsTask." & _
        vbCrLf & "Note: Choosing Yes requires the 8.0 DTS Package Object Library", _
        vbYesNo) = vbYes Then
        Set x = New DTS.Package2
    Else
        Set x = New DTS.Package
    End If
    
    x.Name = "testpkgforfg"
    Call RunTransfer(x)
    x.Execute
    End Sub
  6. Open the DTS Import/Export Wizard and connect to the server that has mydb1 and mydb2, which were created previously.
  7. Select mydb1 as the source and select mydb2 as the destination. Click Next.
  8. Select the Copy table(s) and view(s) from the source database option, and then click Next.
  9. Select Table1 from mydb1 in the Source column. The default should be Table1 on mydb2. Click Next
  10. Click Next. The default should have Run Immediately selected. Click Next.
  11. Click Finish. The package should execute and transfer Table1 to mydb2. Click Done.
  12. Select the properties for Table1. On the source and the destination the Filegroup setting is not preserved.

Properties

Article ID: 300192 - Last Review: October 29, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbpending KB300192

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com