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:
CREATE DATABASE FG5_TEST
( 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 )
( NAME = FG5_log,FILENAME = 'H:\SQL\DATA\FG5log_1.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) ;
CREATE DATABASE FG51_TEST
( 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 )
( NAME = FG51_log,FILENAME = 'H:\SQL\DATA\FG51log_1.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) ;
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.
As said above, please find the code to create multiple MDF files in a single database.
CREATE DATABASE MyDB1
( NAME = MyDB1_dat1,FILENAME = 'H:\SQL\DATA\MyDB1.mdf',SIZE = 3),
( NAME = MyDB1_dat2,FILENAME = 'H:\SQL\DATA\MyDB2.mdf',SIZE = 2)
( NAME = MyDB1_log1,FILENAME = 'H:\SQL\DATA\MyDB1.ldf', SIZE = 2MB ),
( NAME = MyDB1_log2,FILENAME = 'H:\SQL\DATA\MyDB2.ldf', SIZE = 2MB )
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
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
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
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.
Apart from the Regular ETL load and Object transfers. do we have any better way to get the data into a single database.
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.
- Edited by Alex Volok Friday, April 27, 2012 1:50 PM