Move physical database files?

We have SQL server setup on one of our machines that is partitioned out. We
have the OS on drive C: and drive E: setup to house data. The problem is we
forgot that SQL server by default houses the actual data files on the C
drive in the SQL server directory. Our databases are already setup. What is
the best way to move those physical data files OFF the C drive and onto the
E drive. I tried through Enterprise Manager, but wasn't quite sure how. I
also tried stopping the services and making a copy of the files onto the E
drive and then creating a new database pointing to those copied files, but
that didn't work either. What's the best way to go about this. Bottom line,
we don't want the data files on the same partition as the OS. THANKS.You can do it with the help of sp_detach_db and sp_attach_db.
first use the sp_detach_db command to detach the database.
Eg
sp_detach_db 'pubs'
copy data and tran. log files to the destination drive.
after copying the files, you can use the sp_attach_db stored procedure to reattach the files to SQL
Server with the new location
See more help on this in BOL.
--
- Vishal|||Hi
Best way to do it is right click on the database you wnt
to move and say "detach" (you won't be able to detach
system databases but you can probably just leave them on
your C drive) . Then move the data and log files to the
drive where you want them. Then right click on Databases
in Enterprise manager and go to "all tasks", "Attach
database". Browse for the *.mdf in yournew location..the
rest is self explanatory.
Enjoy
-Jono|||The detach/attach thing worked perfectly. THANKS.
"Jono" <false@.me.co.nz> wrote in message
news:26d7301c38ebd$69c8db40$a601280a@.phx.gbl...
> Hi
> Best way to do it is right click on the database you wnt
> to move and say "detach" (you won't be able to detach
> system databases but you can probably just leave them on
> your C drive) . Then move the data and log files to the
> drive where you want them. Then right click on Databases
> in Enterprise manager and go to "all tasks", "Attach
> database". Browse for the *.mdf in yournew location..the
> rest is self explanatory.
> Enjoy
> -Jono|||In addition to the other posts, if you need to move files for the system databases, read article
224071.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Erich" <erich93063@.hotmail.com> wrote in message news:uQeaJrrjDHA.392@.TK2MSFTNGP11.phx.gbl...
> We have SQL server setup on one of our machines that is partitioned out. We
> have the OS on drive C: and drive E: setup to house data. The problem is we
> forgot that SQL server by default houses the actual data files on the C
> drive in the SQL server directory. Our databases are already setup. What is
> the best way to move those physical data files OFF the C drive and onto the
> E drive. I tried through Enterprise Manager, but wasn't quite sure how. I
> also tried stopping the services and making a copy of the files onto the E
> drive and then creating a new database pointing to those copied files, but
> that didn't work either. What's the best way to go about this. Bottom line,
> we don't want the data files on the same partition as the OS. THANKS.
>

Move DB from Sql Server 2000 to Sql Server 2005

Hi;
How can I move a database from Sql Server 2000 to Sql Server 2005 (different
machines, same domain)?
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmDavid Thielen wrote:
> Hi;
> How can I move a database from Sql Server 2000 to Sql Server 2005 (different
> machines, same domain)?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
You can do a backup/restore or detach/attach , both the same as you
would going from sql 2000 to another sql 2000 server.|||Hi,
Add on to that;
1. You can use Copy database wizard to copy database online (no downtime)
See the below article upgrade databases from SQl 2000 to sql 2005.
http://msdn2.microsoft.com/en-us/library/ms188664.aspx
Thanks
Hari
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1155595027.838838.10620@.h48g2000cwc.googlegroups.com...
> David Thielen wrote:
>> Hi;
>> How can I move a database from Sql Server 2000 to Sql Server 2005
>> (different
>> machines, same domain)?
>> --
>> thanks - dave
>> david_at_windward_dot_net
>> http://www.windwardreports.com
>> Cubicle Wars - http://www.windwardreports.com/film.htm
> You can do a backup/restore or detach/attach , both the same as you
> would going from sql 2000 to another sql 2000 server.
>|||Hi Dave,
Thanks for posting.
My understanding of your issue is: You want to move database from SQL 2000
to SQL 2005.
Ken & Hari provided great suggestions. Summary as:
1. Backup/Restore
2. Detach/Attach
3. Use Copy Database Wizard in SQL 2005.
These suggestions are accurate and I'd like to provide some additional
information as below:
How to: Move a Database Using Detach and Attach
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
How to: Upgrade a Database Using Detach and Attach
http://msdn2.microsoft.com/en-us/library/ms189625.aspx
How to: Upgrade to SQL Server 2005 with the Copy Database Wizard
http://msdn2.microsoft.com/en-us/library/ms190775.aspx
In addition, please don't forget to transfer the login if necessary.
Please refer to following article:
246133 How to transfer logins and passwords between instances of
SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;246133
Hope the information helps. If you have any other concerns or questions,
please feel free to let me know. I'm glad to provide assistance.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
>>Thread-Topic: Move DB from Sql Server 2000 to Sql Server 2005
>>thread-index: Aca/74Gu4a9R8Y7PR6m3MIFjbCbRow==>>X-WBNR-Posting-Host: 69.25.210.130
>>From: =?Utf-8?B?RGF2aWQgVGhpZWxlbg==?= <thielen@.nospam.nospam>
>>Subject: Move DB from Sql Server 2000 to Sql Server 2005
>>Date: Mon, 14 Aug 2006 15:18:02 -0700
>>Lines: 13
>>Message-ID: <41275E10-6591-43F1-9C82-CA1C2FC0F131@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:441928
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi;
>>How can I move a database from Sql Server 2000 to Sql Server 2005
(different
>>machines, same domain)?
>>--
>>thanks - dave
>>david_at_windward_dot_net
>>http://www.windwardreports.com
>>Cubicle Wars - http://www.windwardreports.com/film.htm
>>

Move Databases on SQL 2000 Machines

I need to move our production database to a brand new machine on our LAN.
Both machines are running SQL2K. I'd like to place the production database
in a directory on the destination machine with a slightly different path
name than the source SQL server.
How can I shrink the transaction log to the minimum size before I try the
move? What is the best way to move the database to the new machine? We
have Veritus Backup Exec.

Regards,
Charles MacLeanPut the database offline, copy the data file/s and the transaction log/s,
and attach the database file from the new path (sp_attach_db).
You can use the command for attaching a single data file recreating a very
small transaction log (sp_attach_single_file_db).

http://support.microsoft.com/kb/224071/EN-US/
Bye
Massimo

"Charles MacLean" <charlesmaclean@.sbcglobal.netha scritto nel messaggio
news:4zAFg.11171$9T3.4998@.newssvr25.news.prodigy.n et...

Quote:

Originally Posted by

I need to move our production database to a brand new machine on our LAN.
Both machines are running SQL2K. I'd like to place the production

database

Quote:

Originally Posted by

in a directory on the destination machine with a slightly different path
name than the source SQL server.
How can I shrink the transaction log to the minimum size before I try the
move? What is the best way to move the database to the new machine? We
have Veritus Backup Exec.
>
Regards,
Charles MacLean
>
>
>