Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partition Switch to another database Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 4:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
is it possible to switch the data from one database to another. See the below test code

USE [AdventureWorks2008R2]
GO
ALTER TABLE orders_part
SWITCH PARTITION 1 TO TailspinToys.dbo.orders_part_2

Error
Msg 4948, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The source table 'orders_part' is in database 'AdventureWorks2008R2' while the target table 'TailspinToys.dbo.orders_part_2' is in database 'TailspinToys'.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1407735
Posted Wednesday, January 16, 2013 5:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:19 AM
Points: 1,101, Visits: 5,271
No. It's not possible.

Not only in the same database, objects should be in the same filegroup.

BOL:
Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.
Post #1407763
Posted Wednesday, January 16, 2013 5:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Suresh B. (1/16/2013)
However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.
What does it mean ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1407767
Posted Wednesday, January 16, 2013 5:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:19 AM
Points: 1,101, Visits: 5,271
Bhuvnesh (1/16/2013)
Suresh B. (1/16/2013)
However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.
What does it mean ?

It means table and index can be reside on different filegroup.
But they must move to thier corresponding filegroup.

For example: Let's say Table1 is on filegroup Data1. And an index called IX_Table1_Name is on filegroup Index1.
They should move to thier corresponding filegroup on the same database.
Post #1407771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse