Moving DB from one drive to another

Hi!

I'm using SQL Server 2005.

The DB has one table partitioned on 50 Filegroups (One per state)

The DB is right now on my drive G: as are the filegroups (But in
separate directories), and for a number of reasons, I have to move the
DB to drive F: but I need to keep the filegroups (With the bulk of the
data) on drive G:

Question is: How can I move the DB to another drive, leaving the
filegroups where they are?

Thank you!!!

P

Re: Moving DB from one drive to another

> Question is: How can I move the DB to another drive, leaving the
> filegroups where they are?

Exactly what do you mean by "move the DB"? You can detach the database,
move some or all of the files and then reattach using CREATE DATABASE...FOR
ATTACH.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Piero 'Giops' Giorgi" wrote in message
news:1190675053.287080.189090@d55g2000hsg.googlegroups.com.. .
> Hi!
>
> I'm using SQL Server 2005.
>
> The DB has one table partitioned on 50 Filegroups (One per state)
>
> The DB is right now on my drive G: as are the filegroups (But in
> separate directories), and for a number of reasons, I have to move the
> DB to drive F: but I need to keep the filegroups (With the bulk of the
> data) on drive G:
>
> Question is: How can I move the DB to another drive, leaving the
> filegroups where they are?
>
> Thank you!!!
>
> P
>

Re: Moving DB from one drive to another

Piero 'Giops' Giorgi (giorgi.piero@gmail.com) writes:
> I'm using SQL Server 2005.
>
> The DB has one table partitioned on 50 Filegroups (One per state)
>
> The DB is right now on my drive G: as are the filegroups (But in
> separate directories), and for a number of reasons, I have to move the
> DB to drive F: but I need to keep the filegroups (With the bulk of the
> data) on drive G:
>
> Question is: How can I move the DB to another drive, leaving the
> filegroups where they are?

This link in Books Online should be useful:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13f b-457d-996a-66ffc2d55b79.htm


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Moving DB from one drive to another

On Sep 25, 12:59 am, Erland Sommarskog wrote:

> > Question is: How can I move the DB to another drive, leaving the
> > filegroups where they are?
>
> This link in Books Online should be useful:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13f b-457d-996a-66ffc2d55b79.htm

Looks like it is EXACTLY what I needed.

Im' going to try (as soon as the backup is done)

Thank you!

Piero

Re: Moving DB from one drive to another

On Sep 25, 12:59 am, Erland Sommarskog wrote:

> This link in Books Online should be useful:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13f b-457d-996a-66ffc2d55b79.htm

.... Am I right? (Just to be sure...)

The files NOW are in G:\CrimDB\ and I need them in F:\CrimDB\
The Filegroups are in G:\CrimDB\FileGroups

Basically I have to :

1) Detach the Database

use master
go
sp_detach_db 'crimdb'
go

---------------------------------------------------
2) manually move the two files

G:\CrimDB\CrimDB.mdf
G:\CrimDB\CrimDB_Log.ldf

to the new F:\CrimDB\Data\ path]
---------------------------------------------------

3) reattach the Database

use master
go
sp_attach_db 'mydb','F:\CrimDB\Data\CrimDB.mdf','F:\CrimDB\Data
\CrimDB_Log.ldf'
go

The Filegroups will be seen without any other change, right?
(I just want to be sure, given that the Db has 350M records, I don't
want to break it...)

Thank you!

Piero

Re: Moving DB from one drive to another

Piero 'Giops' Giorgi (giorgi.piero@gmail.com) writes:
> On Sep 25, 12:59 am, Erland Sommarskog wrote:
>
>> This link in Books Online should be useful:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13f b-457d-996a-
66ffc2d55b79.htm
>
> ... Am I right? (Just to be sure...)
>
> The files NOW are in G:\CrimDB\ and I need them in F:\CrimDB\
> The Filegroups are in G:\CrimDB\FileGroups
>
> Basically I have to :
>
> 1) Detach the Database

In the copy of Books Online I have it suggests ALTER DATABASE SET OFFLINE.
Does it really say sp_detach_db in yours?

This may be possible if you have an old version. I see now that the page
was updated on 5 December 2005, and the Change History says "Corrected the
steps in all procedures.".

See my signature for a link do download the updated Books Online.

Here is what my Books Online says:

To move a data or log file as part of a planned relocation, follow these
steps:

Run the following statement.

ALTER DATABASE database_name SET OFFLINE

Move the file or files to the new location.

For each file moved, run the following statement.

ALTER DATABASE database_name MODIFY FILE
( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

Run the following statement.

ALTER DATABASE database_name SET ONLINE

Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'');

> The Filegroups will be seen without any other change, right?

I don't know, and I don't have a multi-filegroup database to try on.

In any case, what I had in mind was ALTER DATABASE SET OFFLINE. Since
I did not remember the procedures exactly, I found link and posted
that. Sorry if that lead you to an old version that was incorrect.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Moving DB from one drive to another

On Sep 25, 1:50 pm, Erland Sommarskog wrote:

> In the copy of Books Online I have it suggests ALTER DATABASE SET OFFLINE.
> Does it really say sp_detach_db in yours?

Not anymore... :-)

Is this right? (Files will go in the F:\Main_CrimDB path)

Start with

ALTER DATABASE CrimDB SET OFFLINE

Then move the CrimDB.mdf and CrimDB_log.ldf files to F:\Main_CrimDB
and F:\Main_CrimDB\Log directories

And then

ALTER DATABASE CrimDB MODIFY FILE ( NAME = CrimDB, FILENAME = 'F:
\Main_CrimDB\' )
ALTER DATABASE CrimDB MODIFY FILE ( NAME = CrimDB_Log, FILENAME = 'F:
\Main_CrimDB\Log' )

ALTER DATABASE CrimDB SET ONLINE

And after that, the script to show the result

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'CrimDB');


.... right?

P

Re: Moving DB from one drive to another

Piero 'Giops' Giorgi (giorgi.piero@gmail.com) writes:
> Not anymore... :-)
>
> Is this right? (Files will go in the F:\Main_CrimDB path)
>
> Start with
>
> ALTER DATABASE CrimDB SET OFFLINE
>
> Then move the CrimDB.mdf and CrimDB_log.ldf files to F:\Main_CrimDB
> and F:\Main_CrimDB\Log directories
>
> And then
>
> ALTER DATABASE CrimDB MODIFY FILE ( NAME = CrimDB, FILENAME = 'F:
> \Main_CrimDB\' )
> ALTER DATABASE CrimDB MODIFY FILE ( NAME = CrimDB_Log, FILENAME = 'F:
> \Main_CrimDB\Log' )
>
> ALTER DATABASE CrimDB SET ONLINE
>
> And after that, the script to show the result
>
> SELECT name, physical_name AS CurrentLocation, state_desc
> FROM sys.master_files
> WHERE database_id = DB_ID(N'CrimDB');
>
>
> ... right?

If it's says so in Books Online, I guess it's OK. I haven't this
very often myself.

If you are uncertain of the procedures, create a toy database first
and play with that one.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Moving DB from one drive to another

On Sep 25, 3:09 pm, Erland Sommarskog wrote:

> If it's says so in Books Online, I guess it's OK. I haven't this
> very often myself.
>
> If you are uncertain of the procedures, create a toy database first
> and play with that one.

All right!

Cross my fingers and GO! (Got a backup... )

It's going to be LONG... CrimDB.mdf is 39,433,797,632 bytes...

Thanks!

P

Re: Moving DB from one drive to another

On Sep 25, 3:09 pm, Erland Sommarskog wrote:

> If it's says so in Books Online, I guess it's OK. I haven't this
> very often myself.

Ok!

It worked perfectly, the mail table is on another drive and the
filegroups are where they were before.
NO Problem whatsoever.

We can safely say that moving the table does NOT destroy the
connection tables>partitions>filegroups

P