none
Attach Multiple MDF files to a Single database

    Question

  • Hi,

    I have a two databases, All I want to do is attach all the filegroups one each of these database into another new database. Want to rebuild the log files.

    Please help me with a workaround if the below logic is incorrect.

    Please find the code used and the error displayed:

    USE master
    GO

    CREATE DATABASE FG5_TEST
    ON
    ( NAME = FG5_dat,FILENAME = 'H:\SQL\DATA\FG5dat_1.mdf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ),
    ( NAME = FG5_dat_1,FILENAME = 'H:\SQL\DATA\FG5dat_1.ndf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 )
    LOG ON
    ( NAME = FG5_log,FILENAME = 'H:\SQL\DATA\FG5log_1.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) ;


    CREATE DATABASE FG51_TEST
    ON
    ( NAME = FG51_dat,FILENAME = 'H:\SQL\DATA\FG51dat_1.mdf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ),
    ( NAME = FG51_dat_1,FILENAME = 'H:\SQL\DATA\FG51dat_1.ndf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 )
    LOG ON
    ( NAME = FG51_log,FILENAME = 'H:\SQL\DATA\FG51log_1.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) ;


    SP_Detach_db FG5_TEST
    GO
    SP_Detach_db FG51_TEST
    GO

    SP_attach_DB 'FG5',
    'H:\SQL\DATA\FG5dat_1.mdf',
    'H:\SQL\DATA\FG5dat_1.ndf',
    'H:\SQL\DATA\FG51dat_1.mdf',
    'H:\SQL\DATA\FG51log_1.ldf'

    Msg 5173, Level 16, State 2, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    Wednesday, April 25, 2012 3:18 PM

All replies

  • You can only have one .mdf file for a database.

    Regards, Steve @dataonwheels http://www.dataonwheels.com

    Wednesday, April 25, 2012 8:17 PM
  • Hi Steve,

    We can create multiple MDF files for a single database in SQL server 2008. The clarification I need is when we are able to create a database with multiple MDF files why cant we attach multiple mdf files.

    Friday, April 27, 2012 7:14 AM
  • Hi Steve,

    As said above, please find the code to create multiple MDF files in a single database.

     CREATE DATABASE MyDB1
    ON
    ( NAME = MyDB1_dat1,FILENAME = 'H:\SQL\DATA\MyDB1.mdf',SIZE = 3),
    ( NAME = MyDB1_dat2,FILENAME = 'H:\SQL\DATA\MyDB2.mdf',SIZE = 2)
    LOG ON
    ( NAME = MyDB1_log1,FILENAME = 'H:\SQL\DATA\MyDB1.ldf', SIZE = 2MB ),
    ( NAME = MyDB1_log2,FILENAME = 'H:\SQL\DATA\MyDB2.ldf', SIZE = 2MB )
    GO

    Friday, April 27, 2012 7:31 AM
  • Hi Sowjanya,

    You CANNOT ATTACH data files from different databases together to create new one .

    What you can do is:

    1) Create new empty database

    2) Create/Load tables from both original databases using any BULK load methods

    or

    1) Copy first database [FG5_TEST] to the some new database

    2) Create/Load  tables from the second database in the recently created db


    • Edited by Alex Volok Friday, April 27, 2012 8:28 AM
    Friday, April 27, 2012 8:27 AM
  • Thanks Alex. Do you say that this is a SQL server glitch or we have this addressed in the coming versions of SQL server.

    Can you please explain me the reason or the SQL behaviour when we try to attach data files from two different databases.

    Friday, April 27, 2012 10:01 AM
  • Can you please explain me the reason or the SQL behaviour when we try to attach data files from two different databases.

    The main reason is consistency.

    SQL Server provides ACID way of functionality.

    Inside of MDF files stored not just regular data pages, but also objects to enforce consistency like primary, foreign key, check constraints etc.

    How you can be sure that database still in consistent state if you attach MDF files from different databases?

    How SQL Server should treat objects with the same names?

    How SQL Server should handle system tables, that exists in both databases?

    Well, there are dozens similar questions why such way of attachment not possible.

    Don't be confused with Oracle Segment Attachment.. SQL Server is different product and different story..


    • Edited by Alex Volok Friday, April 27, 2012 11:47 AM
    Friday, April 27, 2012 11:22 AM
  • Thanks Alex. This cleared the cloud.

    Now, I have different tables coming up from multiple sources, if I go for the regular ETL load to gather all the tables from these heterogenous sources to my single staging database it effects the performance.Even if I achieve paralleism here It takes a lot of time. Apart from the Regular ETL load and Object transfers. do we have any better way to get the data into a single database.

    Thanks,

    Sowjanya

    Friday, April 27, 2012 11:58 AM
  • Apart from the Regular ETL load and Object transfers. do we have any better way to get the data into a single database.

    General recomendation

    1) Presize destination database files to avoid unnecessary file grows during the load

    2) Set Simple Recovery Mode

    3) Load data to HEAPs, in other words, no indexes should be defined

    4) Consider use SSIS Dataflow task, also make sure that you use FAST-LOAD options of Destination Adapters. Use OleDB or SQL Server Destination adapters

    5) If you work with heterogeneous sources, make sure that you use the most efficient drivers. In case of Oracle: Attunity data providers are your choice.

    For instance: http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspx

    And, in general: http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx


    • Edited by Alex Volok Friday, April 27, 2012 1:50 PM
    Friday, April 27, 2012 1:49 PM
  • Sowjanya, 

    I think, that answer the  question of this topic: Attach Multiple MDF files to a Single database

    was provided. 

    Other your questions deserves separate topics, but I think even search of similar previous discussions should be enough.

    Monday, April 30, 2012 9:56 AM