Moving an SQL Server 2000 database

When I crated my first SQL Server 2000 database I overlooked the database
lcoation on the harddisk - which by default points to the C: drive. I need
to move it all over to the d: drive and place in the appropriate directory.
What is the best way to accomplish this? Can I use DTS for this or is there
a better way to do this?
Once moved, is there any thing else that I need to do manually to make sure
that everything related to the database did get moved propery and all the
privs are set etc... etc..
Thanks, Brad
If it is going to remain on the same SQL Instance then you have two very
easy options.
1. Detach the files, move them and reattach them
2. Restore a full backup using the WITH MOVE option to change the file
locations.
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Brad Pears (bradp@.truenorthloghomes.com) writes:
> When I crated my first SQL Server 2000 database I overlooked the
> database lcoation on the harddisk - which by default points to the C:
> drive. I need to move it all over to the d: drive and place in the
> appropriate directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
Definitely. Run sp_detach_db, use Explorer to move the file to the desired
location, and then use sp_attach_db to make SQL Server aware of the new
location. (When you detach it, the database is gone as far as SQL Server
is concerned).
See Books Online for exact details on parameters etc.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the comments. I used the detach / attach and it worked like a
top. Although I did hear that using the detach/attach option can sometimes
corrup the DB? Have either of you ever had this problem?
Thanks, Brad
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Not if you do it correctly. If you simply copy over the files without first
detaching them you can have issues.
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:O36bt81sHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Thanks for the comments. I used the detach / attach and it worked like a
> top. Although I did hear that using the detach/attach option can sometimes
> corrup the DB? Have either of you ever had this problem?
> Thanks, Brad
> "Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
> news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
>

Move SQL2005 from Default Instance to Named Instance

I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?

Bryan

SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.

|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier

Move SQL2005 from Default Instance to Named Instance

I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?

Bryan

SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.

|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
Me
If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.
|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move Log Files From C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG To E:\MSSQL Logs

How do I move the default log files placed in: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG to a different location? I want to place them in a partition I have setup which stores log files? Reason being is that this log directory is getting quite large, 600MB and I want to conserve space on my C:\ partition.

Thanks.

You can move the files using sp_detach_db and sp_attach_db. You can find the steps explained in the following article:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071

-Sue

|||

Sue:

Acutally I dont want to move any database files, what I want to move is simply the log files, just errors, activity and such. The files reside in the /log directory not the /data directory.

Thanks for the help.

|||

For the SQL error log, open up SQL Server Configuration Manager. Select the SQL Services node, double click on SQL Server in the right pane and go to the advanced tab. View the startup parameters. The path following -e is for the error log. See the books online topic: Using the SQL Server Service Startup Options.

You can use Configuration Manager to change the path for SQL Agent as well. Select the service node, double click on SQL Server agent and go to the advanced tab. Change the path for the dump directory.

In terms of the default trace, that will default to your installation directory. You can create your own trace if you want another location.

-Sue

|||

Thanks Sue that did the trick.

Changed dump directory to: E:\MSSQL and startup parameters e to E:\MSSQL\ERRORLOG and restarted the service.