Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts

Monday, March 19, 2012

Move table from one file group to another

can I do it by just recreating the clustered index by
specifying the different file group on the statement?
Basically, I need to
1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right?
Any simpler approach?This is a multi-part message in MIME format.
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING =option.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
can I do it by just recreating the clustered index by specifying the different file group on the statement?
Basically, I need to 1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right? Any simpler approach?
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Steps 2 and 3 can be merged by =creating the index with the DROP_EXISTING option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:66cf01c377c3$66=886a10$a501280a@.phx.gbl...can I do it by just recreating the clustered index by specifying the =different file group on the statement?Basically, I need to 1) drop all nonclustered index2) drop the clustered index3) create the =clustered index in different group4) create all the nonclustered index in =differeng groupRight? Any simpler approach?

--=_NextPart_000_04DF_01C377A2.8F3183A0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't have to drop the reference. Consider the following example:
create table t1
(
id int constraint PK_t1 primary key
)
create table t2
(
id int references t1
)
go
create unique clustered index PK_t1 on t1 (id) with drop_existing
go
drop table t2, t1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
Thanks, Tom.
If the table is referenced by any other table, I will
have to drop the reference and then recreate it right?
Because I have to move the primary key index to different
file group too.
>--Original Message--
>Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING option.
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>can I do it by just recreating the clustered index by >specifying the different file group on the statement?
>Basically, I need to >1) drop all nonclustered index
>2) drop the clustered index
>3) create the clustered index in different group
>4) create all the nonclustered index in differeng group
>Right? >Any simpler approach?
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You don't have to drop the =reference. Consider the following example:
create table t1( id int constraint PK_t1 primary =key)
create table t2( id int references t1)go
create unique clustered index =PK_t1 on t1 (id) with drop_existinggo
drop table t2, =t1
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:015d01c377c5$b4=8e1960$a401280a@.phx.gbl...Thanks, Tom.If the table is referenced by any other table, I =willhave to drop the reference and then recreate it right?Because I have to move =the primary key index to differentfile group =too.>--Original Message-->Steps 2 and 3 can be merged by creating the index =with the DROP_EXISTING option.>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU=" =wrote in message news:66cf01c377c3$66=886a10$a501280a@.phx.gbl...>can I do it by just recreating the clustered index by >specifying the =different file group on the statement?>Basically, I need to =>1) drop all nonclustered index>2) drop the clustered index>3) =create the clustered index in different group>4) create all the =nonclustered index in differeng group>Right? >Any simpler approach?

--=_NextPart_000_0521_01C377A5.C1E5ADF0--|||Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different
group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the
following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with
drop_existing
>go
>drop table t2, t1
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message
news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with
>the DROP_EXISTING option.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message
>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by
>>specifying the different file group on the statement?
>>Basically, I need to
>>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right?
>>Any simpler approach?|||This is a multi-part message in MIME format.
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just following up, if you want to move the text/image data, you will =have to drop and recreate.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebEjUG9dDHA.1880@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the PK:
create table t1
(
id int constraint PK_t1 primary key nonclustered
,junk char (5) not null
)
create clustered index idx on t1 (junk)
go
create table t2
(
id int references t1
)
go
create clustered index idx on t1 (junk)
with drop_existing
go
create unique nonclustered index PK_t1 on t1 (id)
with drop_existing
go
drop table t2, t1
As for text/image, I'll have to look further.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:0d7701c377cd$ebd17680$a601280a@.phx.gbl...
Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with drop_existing
>go
>drop table t2, t1
>
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with >the DROP_EXISTING option.
>>-- >>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message >news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by >>specifying the different file group on the statement?
>>Basically, I need to >>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right? >>Any simpler approach?
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Just following up, if you want to move =the text/image data, you will have to drop and recreate.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau" = wrote in message news:ebEjUG9dDHA.1880=@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the =PK:
create table =t1( id int constraint PK_t1 primary key nonclustered,junk char (5) not null)create clustered index idx on t1 (junk)go
create table =t2( id int references t1)go
create clustered index idx on =t1 (junk)with drop_existinggocreate unique nonclustered index =PK_t1 on t1 (id)with drop_existinggo
drop table t2, =t1
As for text/image, I'll have to look further.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU" wrote in message news:0d7701c377cd$eb=d17680$a601280a@.phx.gbl...Thanks, Tom.But I can not use drop existing on the primary key =indexbecause it is not clustered. If I do so, the recreateclustered index will =recreate the primary key index too.It's a waste of time because I have to recreatethe primary key index again to move it to a different =group. Looks like it's a trade off here, right?One more question ( I =hope it is the last one:):If the table has field text/image which need to =moveto the different filegroup too, how can I do it?>--Original Message-->You don't have to drop the reference. Consider =the following example:>>create table t1>(> =id int constraint PK_t1 primary key>)>>create table t2>(> id int references t1>)>go>>create unique clustered index PK_t1 =on t1 (id) with drop_existing>go>>drop table t2, t1>>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU=" =wrote in message news:015d01c377c5$b4=8e1960$a401280a@.phx.gbl...>Thanks, Tom.>>If the table is referenced by any other table, I will>have to drop the reference and then recreate it right?>Because I have to move the primary key index to different>file group too.>>--Original Message-->Steps 2 and 3 can be merged by creating the =index with >the DROP_EXISTING option.>>-- >Tom>>--=--->-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"JZHU" =wrote in message =>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...>can I do it by just recreating the clustered index by >specifying =the different file group on the statement?>Basically, I need to >1) drop all nonclustered index>2) drop the =clustered index>3) create the clustered index in different =group>4) create all the nonclustered index in differeng group>Right? >Any simpler approach?

--=_NextPart_000_0026_01C3783F.CB8BDEC0--
Posted by curiositywerd at 10:43 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Monday, March 12, 2012

Move Row from One table to Another

Hi,
Is there any way i can move a single row to from one table to another [same database].
Basically what i am trying to achive is this. I allow the user to delete a row, but instead of actuall deleting the row, i want to place it into another table [like a recycle bin], which can be restored later.

I know i can simply use insert into statement to add it to the 2nd table and perform the delete statement on table1, but it becomes very tedious becaus there are a lot of fields in my table. I am looking for some other efficient way, if its possible at all.

Any ideas and help is appriciated.

Thanks

DanialOriginally posted by Danial
Hi,
Is there any way i can move a single row to from one table to another [same database].
Basically what i am trying to achive is this. I allow the user to delete a row, but instead of actuall deleting the row, i want to place it into another table [like a recycle bin], which can be restored later.

I know i can simply use insert into statement to add it to the 2nd table and perform the delete statement on table1, but it becomes very tedious becaus there are a lot of fields in my table. I am looking for some other efficient way, if its possible at all.

Any ideas and help is appriciated.

Thanks

Danial

I think the only way to do this to do something like this:

INSERT INTO table2 SELECT * FROM table1 where row = row id.

this will allow you to select all fields without having to type them all. I think this is the most efficent way.|||Thanks, Thats exactly what i was looking for.

Danial
Posted by curiositywerd at 5:41 AM 0 comments
Labels: , , , , , , , , , ,

Saturday, February 25, 2012

move database from 1 instance to another

Hi,
One of my colleague has a SQL server 2k5 on C:\Program files\...
i have basically installed all instances and program files of SQL server
2k5 on D: drive.
I've recreate his instance on D: drive and he gave me all files MDF,
LDF... of his instance (quick and dirty backup :-( )
when i stop the instance process and copy all his files on my D: drive
into the right instance folder, my instance process does not restart and
i got the following error into log events :
The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE )
cannot be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information
is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system
cannot find the path specified.).
so this is clear that the instance path is stored into database files.
how can i do to make this instance files (dirty back) work on my D:
drive instance ?
thanks a lot,
A.Did you restore his master, model, msdb perhaps? If so, do not do this.
Just restore his user databses. There will be issues with logins, however.
You will need to recreate them and sync the login/database user information
if you care about that.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Alain R." <noemail@.nospam.com> wrote in message
news:%23RlvthdNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi,
> One of my colleague has a SQL server 2k5 on C:\Program files\...
> i have basically installed all instances and program files of SQL server
> 2k5 on D: drive.
> I've recreate his instance on D: drive and he gave me all files MDF,
> LDF... of his instance (quick and dirty backup :-( )
> when i stop the instance process and copy all his files on my D: drive
> into the right instance folder, my instance process does not restart and i
> got the following error into log events :
> The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE ) cannot
> be found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. You may be able to use the /AUXSOURCE= flag to retrieve this
> description; see Help and Support for details. The following information
> is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system cannot
> find the path specified.).
> so this is clear that the instance path is stored into database files.
> how can i do to make this instance files (dirty back) work on my D: drive
> instance ?
> thanks a lot,
> A.|||I already only restored his DB file, but after my instance service was
not able to restart :-(
so any other suggestion ?
TheSQLGuru wrote:
> Did you restore his master, model, msdb perhaps? If so, do not do this.
> Just restore his user databses. There will be issues with logins, however.
> You will need to recreate them and sync the login/database user information
> if you care about that.
>|||The files for master db is pointed to in the registry. Resource db need to be in the same folder.
All the other databases are pointed to from master. Problem is that if you fix registry path for
master and make sure resource db is in same folder, then SQL Server will get a bit further, but when
it tires to open model, it won't find the files and won't start (can't live without tempdb and can't
create tempdb without model). There's a section in BOL 2005 on how to move system databases and also
KB 224071 can be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alain R." <noemail@.nospam.com> wrote in message news:uGUuU%23dNIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I already only restored his DB file, but after my instance service was not able to restart :-(
> so any other suggestion ?
> TheSQLGuru wrote:
>> Did you restore his master, model, msdb perhaps? If so, do not do this. Just restore his user
>> databses. There will be issues with logins, however. You will need to recreate them and sync the
>> login/database user information if you care about that.
Posted by curiositywerd at 12:40 AM 0 comments
Labels: , , , , , , , , , , , , ,

move database from 1 instance to another

Hi,
One of my colleague has a SQL server 2k5 on C:\Program files\...
i have basically installed all instances and program files of SQL server
2k5 on D: drive.
I've recreate his instance on D: drive and he gave me all files MDF,
LDF... of his instance (quick and dirty backup :-( )
when i stop the instance process and copy all his files on my D: drive
into the right instance folder, my instance process does not restart and
i got the following error into log events :
The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE )
cannot be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information
is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system
cannot find the path specified.).
so this is clear that the instance path is stored into database files.
how can i do to make this instance files (dirty back) work on my D:
drive instance ?
thanks a lot,
A.Did you restore his master, model, msdb perhaps? If so, do not do this.
Just restore his user databses. There will be issues with logins, however.
You will need to recreate them and sync the login/database user information
if you care about that.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Alain R." <noemail@.nospam.com> wrote in message
news:%23RlvthdNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi,
> One of my colleague has a SQL server 2k5 on C:\Program files\...
> i have basically installed all instances and program files of SQL server
> 2k5 on D: drive.
> I've recreate his instance on D: drive and he gave me all files MDF,
> LDF... of his instance (quick and dirty backup :-( )
> when i stop the instance process and copy all his files on my D: drive
> into the right instance folder, my instance process does not restart and i
> got the following error into log events :
> The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE ) cannot
> be found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. You may be able to use the /AUXSOURCE= flag to retrieve this
> description; see Help and Support for details. The following information
> is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system cannot
> find the path specified.).
> so this is clear that the instance path is stored into database files.
> how can i do to make this instance files (dirty back) work on my D: drive
> instance ?
> thanks a lot,
> A.|||I already only restored his DB file, but after my instance service was
not able to restart :-(
so any other suggestion ?
TheSQLGuru wrote:
> Did you restore his master, model, msdb perhaps? If so, do not do this.
> Just restore his user databses. There will be issues with logins, however
.
> You will need to recreate them and sync the login/database user informatio
n
> if you care about that.
>|||The files for master db is pointed to in the registry. Resource db need to b
e in the same folder.
All the other databases are pointed to from master. Problem is that if you f
ix registry path for
master and make sure resource db is in same folder, then SQL Server will get
a bit further, but when
it tires to open model, it won't find the files and won't start (can't live
without tempdb and can't
create tempdb without model). There's a section in BOL 2005 on how to move s
ystem databases and also
KB 224071 can be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alain R." <noemail@.nospam.com> wrote in message news:uGUuU%23dNIHA.1188@.TK2MSFTNGP04.phx.gb
l...[vbcol=seagreen]
>I already only restored his DB file, but after my instance service was not
able to restart :-(
> so any other suggestion ?
> TheSQLGuru wrote:
Posted by curiositywerd at 12:40 AM 0 comments
Labels: , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)