Moving a DB from SQL 2005 to SQL 2000

Hello all,
This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
SSMS, on the SQL 2005 server, I change the compatibility level for the DB to
SQL Server 2000(80). Then I detach the DB from SQL 2005, copy files over to
the SQL 2000 server and try to reatach the DB on that server. I keep
getting the following error:
TITLE: Microsoft SQL Server Management Studio
--
Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Could not find row in sysindexes for database ID 8, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 602)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
--
BUTTONS:
OK
--
Why will this not work? I look at the system tables in the DB on the 2005
server before detaching and the sysindexes table does not exist. There is
only one single table in the system tables and that is sysdiagrams.
What is the point of having a compatibility level if they are not
compatible?
Thanks for any help anyone can provide,
Conan KellyConan,
The SQL2005 database physical format is not compatible with SQL 2000.
You will need to generate scripts to create the database and all the objects
in it. Right click on your 2005 database and:
Script Database as
Create to
Then again:
Tasks
Generate Scripts...
Once you have created the database and necessary objects
Tasks
Export Data ...
Be sure that you set scripting options to SQL Server 2000. In SP2 (at
least) you can set the scripting to be in SQL Server 2000 mode.
RLF
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:ms%7i.87779$p47.73245@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
> SSMS, on the SQL 2005 server, I change the compatibility level for the DB
> to SQL Server 2000(80). Then I detach the DB from SQL 2005, copy files
> over to the SQL 2000 server and try to reatach the DB on that server. I
> keep getting the following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Could not find row in sysindexes for database ID 8, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
> (Microsoft SQL Server, Error: 602)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
> --
> BUTTONS:
> OK
> --
> Why will this not work? I look at the system tables in the DB on the 2005
> server before detaching and the sysindexes table does not exist. There is
> only one single table in the system tables and that is sysdiagrams.
> What is the point of having a compatibility level if they are not
> compatible?
> Thanks for any help anyone can provide,
> Conan Kelly
>|||Russell,
Thank you for the feed back.
So even if you have the "Compatibility Level" in the DB's properties set to
SQL Server 2000 (80), one can't "detach-copy files-attach" from 2005 to
2000? What's the point of Compatibility Level then?
Thanks again for all of your help,
Conan
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OzEWJcJpHHA.1776@.TK2MSFTNGP05.phx.gbl...
> Conan,
> The SQL2005 database physical format is not compatible with SQL 2000.
> You will need to generate scripts to create the database and all the
> objects in it. Right click on your 2005 database and:
> Script Database as
> Create to
> Then again:
> Tasks
> Generate Scripts...
> Once you have created the database and necessary objects
> Tasks
> Export Data ...
> Be sure that you set scripting options to SQL Server 2000. In SP2 (at
> least) you can set the scripting to be in SQL Server 2000 mode.
> RLF
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:ms%7i.87779$p47.73245@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
>> SSMS, on the SQL 2005 server, I change the compatibility level for the DB
>> to SQL Server 2000(80). Then I detach the DB from SQL 2005, copy files
>> over to the SQL 2000 server and try to reatach the DB on that server. I
>> keep getting the following error:
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
>> --
>> ADDITIONAL INFORMATION:
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Could not find row in sysindexes for database ID 8, object ID 1, index ID
>> 1. Run DBCC CHECKTABLE on sysindexes.
>> Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
>> (Microsoft SQL Server, Error: 602)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
>> --
>> BUTTONS:
>> OK
>> --
>> Why will this not work? I look at the system tables in the DB on the
>> 2005 server before detaching and the sysindexes table does not exist.
>> There is only one single table in the system tables and that is
>> sysdiagrams.
>> What is the point of having a compatibility level if they are not
>> compatible?
>> Thanks for any help anyone can provide,
>> Conan Kelly
>|||https://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/26/what-s-the-difference-between-database-version-and-database-compatibility-level.aspx
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:_L18i.88375$p47.25092@.bgtnsc04-news.ops.worldnet.att.net...
> Russell,
> Thank you for the feed back.
> So even if you have the "Compatibility Level" in the DB's properties set
> to SQL Server 2000 (80), one can't "detach-copy files-attach" from 2005 to
> 2000? What's the point of Compatibility Level then?
> Thanks again for all of your help,
> Conan
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OzEWJcJpHHA.1776@.TK2MSFTNGP05.phx.gbl...
>> Conan,
>> The SQL2005 database physical format is not compatible with SQL 2000.
>> You will need to generate scripts to create the database and all the
>> objects in it. Right click on your 2005 database and:
>> Script Database as
>> Create to
>> Then again:
>> Tasks
>> Generate Scripts...
>> Once you have created the database and necessary objects
>> Tasks
>> Export Data ...
>> Be sure that you set scripting options to SQL Server 2000. In SP2 (at
>> least) you can set the scripting to be in SQL Server 2000 mode.
>> RLF
>> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
>> news:ms%7i.87779$p47.73245@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
>> SSMS, on the SQL 2005 server, I change the compatibility level for the
>> DB to SQL Server 2000(80). Then I detach the DB from SQL 2005, copy
>> files over to the SQL 2000 server and try to reatach the DB on that
>> server. I keep getting the following error:
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
>> --
>> ADDITIONAL INFORMATION:
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Could not find row in sysindexes for database ID 8, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>> Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
>> (Microsoft SQL Server, Error: 602)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
>> --
>> BUTTONS:
>> OK
>> --
>> Why will this not work? I look at the system tables in the DB on the
>> 2005 server before detaching and the sysindexes table does not exist.
>> There is only one single table in the system tables and that is
>> sysdiagrams.
>> What is the point of having a compatibility level if they are not
>> compatible?
>> Thanks for any help anyone can provide,
>> Conan Kelly
>>
>

Moving a clustered index to a different partition scheme

Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>> Hello,
>> I am trying to use SSMS to move a clusted index to a new partitioned
>> scheme. I have done everything msdn said to do:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
>> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
>> When I change the partition scheme in the index properties (storage
>> tab) and hit ok or try to script it I get this error:
>> You cannot perform operation Remove on an object in state Existing.
>> (Microsoft.SqlServer.Smo)
>> I can not find this error anywhere.
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
>> Any help would be greatly appreciated,
>> Randy
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';sql

Moving a clustered index to a different partition scheme

Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLSe...=3199
24
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =
26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';