Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / November 2005

Tip: Looking for answers? Try searching our database.

migrating 2000 partitionned view to 2005 partitionned table?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jéjé - 21 Nov 2005 02:40 GMT
Hi,

I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)

I want to test to migrate to 2005 and I want to use the new partitionned
table options.

how to do this easely?

thanks.

Jerome.
Darren Gosbell - 21 Nov 2005 06:19 GMT
I have not had to do this yet, so this is all theory from my
perspective.

But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."

Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.

In a nutshell I think the process would go something like this:

1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints

I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)

HTH

Signature

Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

> Hi,
>
[quoted text clipped - 9 lines]
>
> Jerome.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.