Moving a Database

Hi

I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)

I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder of the source PC to the destination PC (Same folder).

I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

If you are moving the db;s you need to'detach' them first, then copy the files to the new location, then'attach' them back. check out BOL on how to attach/detach DB's.|||

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='pramsetc',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?FONT>

|||

Apologies correction:

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='blog',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?

|||Did you detach the DB before attaching it? If you did, refresh your databases list and you could probably see the new db in the list.|||

No I didnt know I needed to do that when I copied them across. I will do that now, just as a matter of interest why does that make a difference, what does the detach do that affects the file being attached locally?

Thanks

|||I am puzzled as how it let you copy the files without getting an "Access denied" or "File in use" error. (2) On the server where you are restoring, refresh the databases on the enterprise maanger. Do you see the 'Blob' db? If you already have one, you need to give a different name while restoring.|||I will try detaching it first, thanks

Move SQL table to another file group

Hi, I have huge SQL table (5 mil records), currently it is on primary file group, is there any way to move it to another file group?
Thanks.Try this link for Microsoft created tutorial to move the table. Hope this helps.
http://sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk|||Thanks

Move primary data and log files

I need to move all data from drive e: to another drive due
to space limitations.
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
e:\log1.ldf
f:\log2.ldf
I would like to consolidate the data files into 1 file and
the log files into 1 file.
The files on drive E: are primary filesThis is a multi-part message in MIME format.
--=_NextPart_000_010D_01C3CDF1.B3E7EF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
See my answer to your previous post.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:080c01c3ce19$9019cca0$a101280a@.phx.gbl...
I need to move all data from drive e: to another drive due
to space limitations.
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
e:\log1.ldf
f:\log2.ldf
I would like to consolidate the data files into 1 file and
the log files into 1 file.
The files on drive E: are primary files
--=_NextPart_000_010D_01C3CDF1.B3E7EF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

See my answer to your previous =post.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:080c01c3ce19$90=19cca0$a101280a@.phx.gbl...I need to move all data from drive e: to another drive due to space limitations.My database consists of 2 data files and 2 log files:e:\data1.mdff:\data2.mdfe:\log1.ldff:\log2.=ldfI would like to consolidate the data files into 1 file and the log =files into 1 file.The files on drive E: are primary files

---

--=_NextPart_000_010D_01C3CDF1.B3E7EF40--|||I did that but SQL Server will not let me remove the
primary file. That is the drive I'm out of space on.
I guess I could move all the data files to antoher drive
first and then drop do the DBCC Shrinkile and Alter Remove
File.
>--Original Message--
>See my answer to your previous post.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:080c01c3ce19$9019cca0$a101280a@.phx.gbl...
>I need to move all data from drive e: to another drive due
>to space limitations.
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>e:\log1.ldf
>f:\log2.ldf
>I would like to consolidate the data files into 1 file and
>the log files into 1 file.
>The files on drive E: are primary files
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0152_01C3CDF4.0A5AE830
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You're not removing the primary file. by detaching the database, you're
temporarily removing it from SQL Server. At that point, you can move the
files to a new location, using a regular file copy. Once in the new
location, just reattach.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:078301c3ce1c$ae22aca0$a301280a@.phx.gbl...
I did that but SQL Server will not let me remove the
primary file. That is the drive I'm out of space on.
I guess I could move all the data files to antoher drive
first and then drop do the DBCC Shrinkile and Alter Remove
File.
>--Original Message--
>See my answer to your previous post.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:080c01c3ce19$9019cca0$a101280a@.phx.gbl...
>I need to move all data from drive e: to another drive due
>to space limitations.
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>e:\log1.ldf
>f:\log2.ldf
>I would like to consolidate the data files into 1 file and
>the log files into 1 file.
>The files on drive E: are primary files
>
--=_NextPart_000_0152_01C3CDF4.0A5AE830
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You're not removing the primary =file. by detaching the database, you're temporarily removing it from SQL =Server. At that point, you can move the files to a new location, using a regular =file copy. Once in the new location, just reattach.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:078301c3ce1c$ae=22aca0$a301280a@.phx.gbl...I did that but SQL Server will not let me remove the primary file. =That is the drive I'm out of space on.I guess I could move all the data =files to antoher drive first and then drop do the DBCC Shrinkile and Alter =Remove File.>--Original Message-->See my answer to =your previous post.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:080c01c3ce19$9019cca0$a101280a@.phx.gbl...>=I need to move all data from drive e: to another drive due>to space limitations.>>My database consists of 2 data files and 2 =log files:>>e:\data1.mdf>f:\data2.mdf>>e:\=log1.ldf>f:\log2.ldf>>I would like to consolidate the data files into 1 file and>the log =files into 1 file.>>The files on drive E: are primary files>

---

--=_NextPart_000_0152_01C3CDF4.0A5AE830--

Move existing tables to a new Filegroup!

Hi All,
Is there any tools that I can use OR is there any way
that I can move existing tables in PRIMARY group to a new
filegroup programmingly. I have about 500 tables with
data on them that need to be moved to a new filegroup.
Thanks,
tom
If the tables have a clustered index (and most should) you can drop the CI
and recreate it specifying the other Filegroup and it will move the data
over. If you want to move the non-clustered indexes you will have to drop
and recreate them as well. This can all be done programmatically. But be
prepared for it to take a while if there is a lot of data and make sure your
log file can handle the load.
Andrew J. Kelly SQL MVP
"tt" <anonymous@.discussions.microsoft.com> wrote in message
news:480001c47363$df1294f0$a301280a@.phx.gbl...
> Hi All,
> Is there any tools that I can use OR is there any way
> that I can move existing tables in PRIMARY group to a new
> filegroup programmingly. I have about 500 tables with
> data on them that need to be moved to a new filegroup.
> Thanks,
> tom