Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>

Move DTS Package

Dear Sirs,
How can I move a DTS package from one server to another?
Regards,
YahyaHi,
Right click DTS which you want to move and chose Design Package. Click
Package then Save AS and chose server in which you want to save DTS.
Or you can save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
"Yahya Saad" wrote:
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>
>|||Try saving it as a .dts file rather than to SQL Server. Then move the file
and open it from the new server. You may strike some issues with ownership
and mappings if you use any flat files in the package, but they can be
worked around and re-mapped.
"Yahya Saad" <yahyasaad@.hotmail.com> wrote in message
news:uwR78bsCFHA.3528@.TK2MSFTNGP10.phx.gbl...
> Dear Sirs,
> How can I move a DTS package from one server to another?
> Regards,
> Yahya
>