Move database file: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

Moving and renaming SSIS Projects

Say I created a new ssis Project and allowed the project to created a subfolder .. say both the project and subfolder are called x123.

I now want to rename the project to "Mycompany.MyProject" and want to remove the subfolder.

Possible?

Trying to avoid this, I made a copy of the parent folder, and manually tried to copy and paste all objects .. but am getting errors with connection manager entries.

Thanks for any help or information!

I think you need to edit the .sln and .dtproj files to reflect the new folder structure and names.

May be it is easier if you create a new project, and the use add existing package/item option to add the packages and other objects you want to preserve.

|||

Thanks. Copy and pasting finally worked. One quirk, if you paste in connections from another project it don't like that , but if you paste and then copy from new project and paste again and rename and then reset all all objects using the connections that seemed to work.

I'll try changing content of those files, but do suspect it will not be clean. It would be great if there were a utility or something.

sql

Monday, March 26, 2012

Moving a database from SQL Server to MSDE

Hi,
I have created quite a large database project that the client now wants to
run locally for demo purposes.
What is the best (& easiest for non-programmer client) way to transport a
snapshot of this database from one to the other - with the data still
intact? The VS.Net tools appear to only move the structure and stored
procedures etc..
Thanks in adavance,
StuStu
I prefer BACKUP/RESTORE commands
"Stu Lock" <s.lock@.cergis.com> wrote in message
news:eCF$cGyyEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have created quite a large database project that the client now wants to
> run locally for demo purposes.
> What is the best (& easiest for non-programmer client) way to transport a
> snapshot of this database from one to the other - with the data still
> intact? The VS.Net tools appear to only move the structure and stored
> procedures etc..
> Thanks in adavance,
> Stu
>|||How do you restore an SQL Server database to an MSDE instance that has no
database installed?
THnx.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23B1asKyyEHA.3840@.tk2msftngp13.phx.gbl...
> Stu
> I prefer BACKUP/RESTORE commands
>
>
> "Stu Lock" <s.lock@.cergis.com> wrote in message
> news:eCF$cGyyEHA.3336@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I have created quite a large database project that the client now wants
>> to
>> run locally for demo purposes.
>> What is the best (& easiest for non-programmer client) way to transport a
>> snapshot of this database from one to the other - with the data still
>> intact? The VS.Net tools appear to only move the structure and stored
>> procedures etc..
>> Thanks in adavance,
>> Stu
>>
>|||"Stu Lock" <s.lock@.cergis.com> wrote in message
news:%23gQ5rBzyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> How do you restore an SQL Server database to an MSDE instance that has no
> database installed?
>
Same way you'd restore it to any SQL Server. RESTORE DATABASE foo from
disk='\\foo\bar\baz.bak'
BOL will have more details.
> THnx.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23B1asKyyEHA.3840@.tk2msftngp13.phx.gbl...
> > Stu
> > I prefer BACKUP/RESTORE commands
> >
> >
> >
> >
> > "Stu Lock" <s.lock@.cergis.com> wrote in message
> > news:eCF$cGyyEHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >>
> >> I have created quite a large database project that the client now wants
> >> to
> >> run locally for demo purposes.
> >>
> >> What is the best (& easiest for non-programmer client) way to transport
a
> >> snapshot of this database from one to the other - with the data still
> >> intact? The VS.Net tools appear to only move the structure and stored
> >> procedures etc..
> >>
> >> Thanks in adavance,
> >>
> >> Stu
> >>
> >>
> >
> >
>|||Just be aware of the limitations of MSDE. Maximum DB size is 2GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:DVbmd.8248$qv5.8175@.twister.nyroc.rr.com...
> "Stu Lock" <s.lock@.cergis.com> wrote in message
> news:%23gQ5rBzyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > How do you restore an SQL Server database to an MSDE instance that has
no
> > database installed?
> >
> Same way you'd restore it to any SQL Server. RESTORE DATABASE foo from
> disk='\\foo\bar\baz.bak'
> BOL will have more details.
>
> > THnx.
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:%23B1asKyyEHA.3840@.tk2msftngp13.phx.gbl...
> > > Stu
> > > I prefer BACKUP/RESTORE commands
> > >
> > >
> > >
> > >
> > > "Stu Lock" <s.lock@.cergis.com> wrote in message
> > > news:eCF$cGyyEHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> Hi,
> > >>
> > >> I have created quite a large database project that the client now
wants
> > >> to
> > >> run locally for demo purposes.
> > >>
> > >> What is the best (& easiest for non-programmer client) way to
transport
> a
> > >> snapshot of this database from one to the other - with the data still
> > >> intact? The VS.Net tools appear to only move the structure and stored
> > >> procedures etc..
> > >>
> > >> Thanks in adavance,
> > >>
> > >> Stu
> > >>
> > >>
> > >
> > >
> >
> >
>

Moving a database from SQL Server to MSDE

moving a database from 2005 to 2000

Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
but wondering if you can run scripts in sql 2000 created in 2005 to recreate
the database?
thanks
--
Paul G
Software engineer.Yes. When you generate the script set the "Script for Server Version"
option to SQL Server 2000.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Apr 2008 15:13:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
>but wondering if you can run scripts in sql 2000 created in 2005 to recreate
>the database?
>thanks|||ok found the script type selection option! thanks.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> Yes. When you generate the script set the "Script for Server Version"
> option to SQL Server 2000.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 25 Apr 2008 15:13:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
> >but wondering if you can run scripts in sql 2000 created in 2005 to recreate
> >the database?
> >thanks
>sql

Moving a database diagram to another database

Hello, all. Was wondering if I can move a diagram I've created in Database
A to Database B, which is on the same SQL SERVER...with different db names
of course. I'm running SQL 2K. If this possible, please let me know
Tia
RozThe diagrams are stored within dtproperties. You might be able to move =
the data via DTS, BCP or some other method.
--=20
Keith
"Roz" <anonymous@.discussions.microsoft.com> wrote in message =
news:9B5F1E93-A3AE-4B51-8146-F4B0278C12D4@.microsoft.com...
> Hello, all. Was wondering if I can move a diagram I've created in =
Database A to Database B, which is on the same SQL SERVER...with =
different db names of course. I'm running SQL 2K. If this possible, =
please let me know
>=20
> Tia
> Roz
>=20
>|||Keith Kratochvil wrote:
> The diagrams are stored within dtproperties. You might be able to move th
e data via DTS, BCP or some other method.
>
I was wondering the same thing as Roz, I've just had a quick look into using
DTS
or BCP to copy the diagram over... it doesn't look like it's possible?
Have you done this or was it just a suggestion? I'd like to be able to do th
is.
Regards,
Craig.|||Found this right after posting...
Microsoft Knowledge Base Article - 320125
HOW TO: Move a Database Diagram
http://support.microsoft.com/?scid=kb;en-us;Q320125
Regards,
Craig.|||check this out, I found it at
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7225
SET IDENTITY_INSERT dtproperties
ON
INSERT
dtproperties
([id],objectid,property,value,
lvalue,version)
SELECT
[id],objectid,property,value,
lvalue,version
FROM
pubs..dtproperties
WHERE
pubs..dtproperties.objectid = 8
SET IDENTITY_INSERT dtproperties
OFF
Regards,
Craig.

Friday, March 23, 2012

Moving a database

I'm very new to SQL Server 2005, so please be kind.

We had a vendor in and they loaded their software and the database
that was created is sitting
on the root (c:) drive. On this server we have another drive where
the database should reside, is
there a way to move this database to the correct drive? Thank you for
any help that you could
provide.

Dave SchaefferDave (dave.schaeffer@.gmail.com) writes:

Quote:

Originally Posted by

I'm very new to SQL Server 2005, so please be kind.
>
We had a vendor in and they loaded their software and the database that
was created is sitting on the root (c:) drive. On this server we have
another drive where the database should reside, is there a way to move
this database to the correct drive? Thank you for any help that you
could provide.

You could try the Copy Database Wizard. Right-click the database in
Object Explorer, and select Tasks and then Copy Database in the
submenu. Select local server for both target and destination. Make
sure you select the attach/detach method (it's selected by default).
At one point you get a database-selection dialog. You can select Move here
instead of copy. Make sure that you have SQL Server Agent.

I will have admit that I give this advice with some hesitation. To wit,
the Copy Database Wizard is very buggy. But attach/detach in the same
server, is so simple so it cannot reasonably not fail. Personally, I
prefer to do this operation with backup/restore, but since you are
very new, you are probably more comfortable with the Wizard for now.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

movie db query problems

Hi everyone!

this is the first time I use this forum although I already got plenty of help from it!

My problem is the following. I have created a movie database. I am using Oracle iSQL*Plus.

One table is called 'movie_t' another one 'person_t' and inside this last table is nexted a table called 'castmembers'.

one of my query is : "For all movies,list the leading actress, i.e. the first billed actress (in the order of credits). Show the movie Title, Genre, Director and Name of the actress ".

I found a complex solution that works:

SELECT title, name, creditorder "CREDIT ORDER"
FROM (select RANK() OVER (partition by m.title ORDER BY c.creditorder) rankcredit, m.title, p.name, c.creditorder
FROM person_t p, table(p.castmembers) c, movie_t m
WHERE c.movie_ref = ref(m) and p.gender = 'F') T WHERE RANKCREDIT = 1;

However, I am pretty sure that there must be a simple solution without using a method and partition.

I tried the following statement:

select m.title, m.director.name, m.genre, p.name, c.creditorder
from movie_t m, person_t p, table(p.castmembers)c
where ref(m) = c.movie_ref
and c.creditorder in
(Select min(d.creditorder)
from movie_t n, person_t q, table(q.castmembers)d
where m.title=n.title and m.director.name=n.director.name and

m.genre=n.genre and p.name=q.name and p.gender = 'F'
group by n.title, n.director.name, n.genre )

This unfortunately does not work, as it returns me all actresses!

please check my .doc file, it makes it easier to understand!

if anyone could help me a little bit, I would greatly appreciate, I have spent my whole sunday on that and it's getting realy frustrating for me!

thanks all,
Gaetansorry guys, my file did not atached, I try one more time :-)|||i finally found the solution :-)

SELECT c.movie_ref.title, p.name, c.creditorder, m.genre, m.director.name
FROM movie_t m, person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F' and (c.creditorder) = all
( select min(c.creditorder) from person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F');

if any of u think about anything else let me know, maybe I can also use the "having" function?

gaetan :-)sql

Wednesday, March 21, 2012

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

Monday, March 19, 2012

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

Monday, March 12, 2012

move row data

I have a row that contains invalid information. I have created another row
with the correct data. each row has a sequence number on it. The other two
fields in each row contain binary data. how can I move the binary data from
one row into the incorrect row and maintain the sequence number that is
already involved?--Record the sequence number of the row with incorrect information, and then
--delete it. Assume incorrect your identified by sequence number 12345
DELETE FROM [YourTable] WHERE [SequenceColumn] = 12345
--Copy the information from the row with the corrected values. Assume row
--with corrected values identified by sequence number 67890
SET INDENTITY_INSERT [YourTable] ON
INSERT INTO [YourTable]
([SequenceColumn] , [Other Columns...]) --must list all columns
SELECT 12345, [Other Columns....]
FROM [YourTable]
WHERE [SequenceColumn] = 667890
SET IDENTITY_INSERT [YourTable] OFF
--Delete the row where corrected values where copied from
DELETE FROM [YourTable] WHERE [SequenceColumn] = 67890
"Dgragg" wrote:

> I have a row that contains invalid information. I have created another ro
w
> with the correct data. each row has a sequence number on it. The other t
wo
> fields in each row contain binary data. how can I move the binary data fr
om
> one row into the incorrect row and maintain the sequence number that is
> already involved?

Move reports created by Report Builder

I have created a number of reports using Report Builder, and I would
like to move them to a new report server 2005 instance (I have
redeployed the underlying model to the new report server) and
associated model. Any easy way to do this?
Thanks,
Paul.Reporting Services Scripter v2
http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul" <prayner@.matrixbancorp.com> wrote in message
news:1134682199.968299.138840@.o13g2000cwo.googlegroups.com...
>I have created a number of reports using Report Builder, and I would
> like to move them to a new report server 2005 instance (I have
> redeployed the underlying model to the new report server) and
> associated model. Any easy way to do this?
> Thanks,
> Paul.
>|||Thanks very much Jasper! Much appreciated.
Paul.

Friday, March 9, 2012

Move linked reports to new server

I am moving reporting services to a new server and I want to move the
reports, file structure, and linked reports over as well. I have created a
few parameterized base reports that I can simply re-deploy, but there are
numerous linked reports - they run specific parameters on a scheduled basis
and distribute via subscriptions - that were created on the server itself.
Is there any simple way to move the linked reports over to the new server?
Perhaps go into the old server and just grab certain files and copy them over?The easiest way is to write a script file to do this (or .Net program using
SOAP). There is no easy way using the UI.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Echinodermata" <Echinodermata@.discussions.microsoft.com> wrote in message
news:14E5B132-F351-42D1-8B74-92152FB5E60C@.microsoft.com...
>I am moving reporting services to a new server and I want to move the
> reports, file structure, and linked reports over as well. I have created
> a
> few parameterized base reports that I can simply re-deploy, but there are
> numerous linked reports - they run specific parameters on a scheduled
> basis
> and distribute via subscriptions - that were created on the server itself.
> Is there any simple way to move the linked reports over to the new server?
> Perhaps go into the old server and just grab certain files and copy them
> over?
>|||Does anyone know of any example scripts on how to copy reports to a server? I
have a product that I am distributing - part of it utilizes reports in
reporting services - I would like to be able to install those reports with a
setup program - has anyone done this? If someone could point me in the right
direction, it would be greatly appreciated.
"Daniel Reib [MSFT]" wrote:
> The easiest way is to write a script file to do this (or .Net program using
> SOAP). There is no easy way using the UI.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Echinodermata" <Echinodermata@.discussions.microsoft.com> wrote in message
> news:14E5B132-F351-42D1-8B74-92152FB5E60C@.microsoft.com...
> >I am moving reporting services to a new server and I want to move the
> > reports, file structure, and linked reports over as well. I have created
> > a
> > few parameterized base reports that I can simply re-deploy, but there are
> > numerous linked reports - they run specific parameters on a scheduled
> > basis
> > and distribute via subscriptions - that were created on the server itself.
> >
> > Is there any simple way to move the linked reports over to the new server?
> > Perhaps go into the old server and just grab certain files and copy them
> > over?
> >
>
>

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,
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...
>

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 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 , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 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 migra
te
> 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_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
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 , MA
XSIZE = 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...
>|||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...[vbcol=seagreen]
> 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_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> 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 exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>

Move from Cursor to Temp table alot slower

I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
Thanks
When people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP

Move from Cursor to Temp table alot slower

I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

Friday, March 30, 2012

moving clustered indexes

Moving and renaming SSIS Projects

Monday, March 26, 2012

Moving a database from SQL Server to MSDE

Moving a database from SQL Server to MSDE

Moving a database from SQL Server to MSDE

moving a database from 2005 to 2000

Moving a database diagram to another database

Friday, March 23, 2012

Moving a database

movie db query problems

Wednesday, March 21, 2012

Move Table to different filegroup.

Move Table to different filegroup.

Monday, March 19, 2012

Move Table to different filegroup.

Monday, March 12, 2012

move row data

Move reports created by Report Builder

Friday, March 9, 2012

Move linked reports to new server

Move indexes to new filegroup

Move indexes to new filegroup

Move indexes to new filegroup

Move from Cursor to Temp table alot slower

Move from Cursor to Temp table alot slower

Move database file

Blog Archive

About Me