July 14, 2017 at 5:14 am
I have a main Database (name: sale) which have following types of data in all tables. Each type have difference ID type like (Finance =FIN, Sale=SAL, Supplier=SLR)
1. Finance
2. Sale
3. Supplier
Now my task is to create a database on another server from this existing database which should have only Finance data. I use SSIS object "Transfer SQL Server Objects" but it transfer all data which takes large space. Is this possible to apply where condition on data to transfer only Finance data. What is the possible solution. My goals is to transfer all objects (view,store procedure etc.) but table data should be filtered.
Thanks,
Faizur Rahman
July 14, 2017 at 5:20 am
If you transfer or restore a database, then you do exactly that; the whole database is used. You can't restore part of a database, as that's not how it works.
When you're transferring, what is your full goal. That they just tables, or do you have views, procedures, functions, etc that need to go with it. What does the data need to look like at the other end? Does it need to be exactly the same, with Indexes, constraints, etc, or is simply having the data contained within them "good enough"?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 14, 2017 at 5:29 am
My goals to transfer all objects (view,SP, tables etc.) but table should have only filter data.
July 14, 2017 at 5:32 am
Ahh, I think I misunderstood slight,it's not specific objects that you want to restore, but only specific data. Using a Restore/Transfer, the only way you could achieve this would be to delete the non-relevant data afterwards. if you have Foreign keys, constraints, etc, you'll need to ensure you delete them in the correct order.
There may be a third party tool that someone could recommend, however, which may come with a cost.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 14, 2017 at 5:36 am
As Thom A says either delete the data after it has been created or set the Copy Data property of the Transfer task to be False so only the objects are created then setup data flow tasks which only populate the finance data into the created objects.
July 14, 2017 at 5:37 am
Is it possible to create a parent package which create only complete schema of database and then copy selected data? But how ?
July 14, 2017 at 5:41 am
faiz_ku - Friday, July 14, 2017 5:37 AMIs it possible to create a parent package which create only complete schema of database and then copy selected data? But how ?
It's easy enough to create a DACPAC from an existing database and then use that to create an empty version. But if you're using source control, you should be able to do this already without needing to touch the target database.
After that, use SSIS data flow tasks to populate the tables whose data you want to transfer, in the correct order (so as to honour any FK constraints).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 14, 2017 at 5:47 am
My source and destination server are different. Does data flow task works with different server on a network?
July 14, 2017 at 5:52 am
faiz_ku - Friday, July 14, 2017 5:47 AMMy source and destination server are different. Does data flow task works with different server on a network?
Define "different". Different SQL Server version, different RDBMS, different servers (physically)?
If their different versions, provided you aren't using any tools that have been removed/added between the version, you're fine. Different servers (physically) doesn't make any difference. If the engine is different, that depends on your source/destination, we'd need more info.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 14, 2017 at 6:30 am
Different server mean machine are different. I will check and update. Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply