Partition Switch to another database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Partition Switch to another database

Partition Switch to another database

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Wednesday, January 16, 2013 4:20 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1407735

Suresh B.

Posted Wednesday, January 16, 2013 5:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075, Visits: 5,114

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

Bhuvnesh

Posted Wednesday, January 16, 2013 5:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1407767

Suresh B.

Posted Wednesday, January 16, 2013 5:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075, Visits: 5,114

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 »

Permissions