Using SS2000 SP4. I've done this many times before without problem so I don'
t
know why I'm having trouble this time. I'm running out of space on a drive s
o
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
--
Dan D.Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I do
n't
> know why I'm having trouble this time. I'm running out of space on a drive
so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem
.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them yo
u
> will need to detach the database again.
> How did you move the files?
> John|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but sin
ce
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any probl
em.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/Sh...303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may b
e
your permissions rather than the service accounts if you backup/restore work
s
ok.
John
John|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/Sh...303056&SiteID=1 wher
e
> the service accounts permissions on the directory has an impact, so it may
be
> your permissions rather than the service accounts if you backup/restore wo
rks
> ok.
> John
> John
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Monday, March 26, 2012
moving a database causes it to be read-only
Using SS2000 SP4. I've done this many times before without problem so I don't
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
--
Dan D.Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > Using SS2000 SP4. I've done this many times before without problem so I don't
> > know why I'm having trouble this time. I'm running out of space on a drive so
> > I'm moving some databases to another drive. I've tried this with EM and QA
> > using this query:
> > EXEC sp_detach_db 'jg', 'false'
> > EXEC sp_attach_db @.dbname = 'jg',
> > @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> > @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> >
> > After attaching the database, the database comes up read-only. Can someone
> > tell me why?
> >
> > Thanks,
> > --
> > Dan D.
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > I didn't get an error. I did this twice on two different databases and had
> > the same problem both times. I didn't check the readonly attribute but since
> > I moved the files using drag-and-drop, I wouldn't expect the attribute to
> > change.
> >
> > I had a backup of the database and I restored it and didn't have any problem.
> >
> > Thanks,
> > --
> > Dan D.
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
--
Dan D.Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > Using SS2000 SP4. I've done this many times before without problem so I don't
> > know why I'm having trouble this time. I'm running out of space on a drive so
> > I'm moving some databases to another drive. I've tried this with EM and QA
> > using this query:
> > EXEC sp_detach_db 'jg', 'false'
> > EXEC sp_attach_db @.dbname = 'jg',
> > @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> > @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> >
> > After attaching the database, the database comes up read-only. Can someone
> > tell me why?
> >
> > Thanks,
> > --
> > Dan D.
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
--
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
> > I didn't get an error. I did this twice on two different databases and had
> > the same problem both times. I didn't check the readonly attribute but since
> > I moved the files using drag-and-drop, I wouldn't expect the attribute to
> > change.
> >
> > I had a backup of the database and I restored it and didn't have any problem.
> >
> > Thanks,
> > --
> > Dan D.
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John
moving a database causes it to be read-only
Using SS2000 SP4. I've done this many times before without problem so I don't
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
Dan D.
Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John
|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John
|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John
|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John
sql
know why I'm having trouble this time. I'm running out of space on a drive so
I'm moving some databases to another drive. I've tried this with EM and QA
using this query:
EXEC sp_detach_db 'jg', 'false'
EXEC sp_attach_db @.dbname = 'jg',
@.filename1 = 'S:\SQL\Data\jg_Data.mdf',
@.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
After attaching the database, the database comes up read-only. Can someone
tell me why?
Thanks,
Dan D.
Hi Dan
"Dan D." wrote:
> Using SS2000 SP4. I've done this many times before without problem so I don't
> know why I'm having trouble this time. I'm running out of space on a drive so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
Do you get an error when you try to make them not read-only?
Have you checked the readonly attribute on these files? To uncheck them you
will need to detach the database again.
How did you move the files?
John
|||I didn't get an error. I did this twice on two different databases and had
the same problem both times. I didn't check the readonly attribute but since
I moved the files using drag-and-drop, I wouldn't expect the attribute to
change.
I had a backup of the database and I restored it and didn't have any problem.
Thanks,
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> Do you get an error when you try to make them not read-only?
> Have you checked the readonly attribute on these files? To uncheck them you
> will need to detach the database again.
> How did you move the files?
> John
|||Hi Dan
"Dan D." wrote:
> I didn't get an error. I did this twice on two different databases and had
> the same problem both times. I didn't check the readonly attribute but since
> I moved the files using drag-and-drop, I wouldn't expect the attribute to
> change.
> I had a backup of the database and I restored it and didn't have any problem.
> Thanks,
> --
> Dan D.
If you didn't get an error changing the database from readonly the file
attributes must be ok. I did find this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
the service accounts permissions on the directory has an impact, so it may be
your permissions rather than the service accounts if you backup/restore works
ok.
John
John
|||Hi,
I know that in SQL 2005 when attaching databases they come up in a state of
reduced functinality. This is a security measure to make sure that someone
cannot attach a database that gives them more permsissions than they might
otherwise have. Maybe some of this concept made it into SQL2000 SP4.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:5F9EE66C-31AC-4AC6-A971-7613346DBE1D@.microsoft.com...
> Using SS2000 SP4. I've done this many times before without problem so I
> don't
> know why I'm having trouble this time. I'm running out of space on a drive
> so
> I'm moving some databases to another drive. I've tried this with EM and QA
> using this query:
> EXEC sp_detach_db 'jg', 'false'
> EXEC sp_attach_db @.dbname = 'jg',
> @.filename1 = 'S:\SQL\Data\jg_Data.mdf',
> @.filename2 = 'E:\SQL\LOGS\jg_log.ldf'
> After attaching the database, the database comes up read-only. Can someone
> tell me why?
> Thanks,
> --
> Dan D.
|||It was the security on the folder that I was moving the data to. The user
didn't have enough permissions.
Thanks,
Dan D.
"John Bell" wrote:
> Hi Dan
> "Dan D." wrote:
>
> If you didn't get an error changing the database from readonly the file
> attributes must be ok. I did find this
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303056&SiteID=1 where
> the service accounts permissions on the directory has an impact, so it may be
> your permissions rather than the service accounts if you backup/restore works
> ok.
> John
> John
sql
Monday, February 20, 2012
Moving indexes to another filegroup trouble...
I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.
But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.
So I will not get too happy about this.
1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?
This all is in SQL Server 2005 Developer and using Management Studio.
Thanks in advance!
You can move an index by using the DROP_EXISTING clause.
Code Snippet
CREATE TABLE myTable
(ID int, NC varchar(3))
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
sp_help myTable
DROP TABLE myTable
GO
For constraints check out the MOVE TO clause for the DROP constraint statement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.
Moving indexes to another filegroup trouble...
I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.
But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.
So I will not get too happy about this.
1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?
This all is in SQL Server 2005 Developer and using Management Studio.
Thanks in advance!
You can move an index by using the DROP_EXISTING clause.
Code Snippet
CREATE TABLE myTable
(ID int, NC varchar(3))
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
GO
CREATE CLUSTERED INDEX myCIX ON myTable (ID)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
CREATE NONCLUSTERED INDEX myIX ON myTable (NC)
WITH (DROP_EXISTING=ON) ON [OtherFG]
GO
sp_help myTable
DROP TABLE myTable
GO
For constraints check out the MOVE TO clause for the DROP constraint statement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.
Subscribe to:
Posts (Atom)