Move databases to a different drive

I am looking at moving our databases to a different drive as our C:
drive is getting full.
We have added a 200MB drive that we are going to use for Data and want
to move our data only to that drive. We want to leave the program files
on the C: drive and move the data to our D: drive.
How best would we do this. I know we can't just move the data folder.
Thanks,
Tom.
Hi,
There are 2 options to you:-
a. Keep the system databases in same drive and move the user databases (both
LDF and MDF) to new drive
b. Move all the system and user databases (MDF and LDF) to new drive
a.
1. Use the procedure SP_DETACH_DB <dbname> to detach the databases
2. Create a new folder in new drive and copy the MDF and LDF to new folder
3. Use sp_attach_db
'dbname','driveletter\folder\dbname.mdf','drivelet ter\folder\dbname.ldf'
I feel that since you have added only 200 MB to new drive it is safe to keep
the System databases in C drive itself.
If you need to mave then then have a look into the below article to move all
databases:-
http://support.microsoft.com/default...b;EN-US;224071
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408F1C0D.800@.deltanet.com...
> I am looking at moving our databases to a different drive as our C:
> drive is getting full.
> We have added a 200MB drive that we are going to use for Data and want
> to move our data only to that drive. We want to leave the program files
> on the C: drive and move the data to our D: drive.
> How best would we do this. I know we can't just move the data folder.
> Thanks,
> Tom.
>
|||Detach and Attach is probably the best bet.
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:408F1C0D.800@.deltanet.com...
> I am looking at moving our databases to a different drive as our C:
> drive is getting full.
> We have added a 200MB drive that we are going to use for Data and want
> to move our data only to that drive. We want to leave the program files
> on the C: drive and move the data to our D: drive.
> How best would we do this. I know we can't just move the data folder.
> Thanks,
> Tom.
>
|||For user databases, yes, I would recommend the same thing. Unfortunately,
that won't move master/model/msdb/tempdb, nor will it make the new location
the default for newly-created databases.
The cleanest way would probably be to do this:
(a) detach your user databases
(b) reinstall SQL Server and all SPs/patches, customizing the data folder
location
(c) move the detached MDF files to the new data location
(d) re-attach
If you can't afford downtime, then the safest thing to do would be to leave
the system databases where they are, and re-attach the user databases from
the new location.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Peterson" <no1@.nunya.com> wrote in message
news:OagiiANLEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Detach and Attach is probably the best bet.
|||Aaron Bertrand [MVP] wrote:

> For user databases, yes, I would recommend the same thing. Unfortunately,
> that won't move master/model/msdb/tempdb, nor will it make the new location
> the default for newly-created databases.
Can't I use the same detach/attach procedures for these databases, also?
Do I have to have my Server programs on the data drive to get it to be
the default location for newly-created databases?
I was thinking it would be good to have the data on a different drive
than my program files.

> The cleanest way would probably be to do this:
> (a) detach your user databases
> (b) reinstall SQL Server and all SPs/patches, customizing the data folder
> location
How do I customise the data folder location and why can't I do that with
the old install?

> (c) move the detached MDF files to the new data location
> (d) re-attach
> If you can't afford downtime, then the safest thing to do would be to leave
> the system databases where they are, and re-attach the user databases from
> the new location.
>
|||> Can't I use the same detach/attach procedures for these databases, also?
No, you can't just detach master, tempdb, etc.

> How do I customise the data folder location and why can't I do that with
> the old install?
You can do this during setup. I've never tried to change the default once
SQL Server has been installed; it's likely to be a painful exercise.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Check out the following article for detailed instructions on how to move the
system databases:
http://support.microsoft.com/default...&Product=sql2k
As for changing the default location for databases, that is easy. In
Enterprise Manager, right click on the server and open properties, on the
Database Settings tab down near the bottom you can specify a default
location for new databases.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OE2qXdWLEHA.620@.TK2MSFTNGP10.phx.gbl...
> No, you can't just detach master, tempdb, etc.
>
> You can do this during setup. I've never tried to change the default once
> SQL Server has been installed; it's likely to be a painful exercise.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>