Move SQL database to a different volume

[purefreshair]

Hello,

I have a task at my new job where I need to move an SQL 2000 database from one volume on the server (running Windows 2003 server, standard edition) to another. The vendor that originally created the database (before I started here at this job) set the database up on the c volume which is now 3/4 full.

I obtained information from Microsoft's site on how to do it, but I wanted to ask folks in this forum whether anyone has done it before and what to watch for. I only need to move one database.
Thanks

 

[SQLBill]

Which database are you moving? I've moved all the databases at one time or another. Moving User databases are different than system databases. Moving Master is different than any other database.

-SQLBill

Posting advice: FAQ481-4875

 

[purefreshair]

SQLBill,

This would be a User database that was set up from a vendor application program that runs as a client/server app.

 

[SQLBill]

You can do it one of two easy ways:

1. In Enterprise Manager, expand until you find the database. Right click on it, go to All Tasks > Detach database. Once it is detached, move the .mdf and .ldf to where they need to be. Go back to EM, right click on Databases, select All Tasks> Attach database. Browse to the new location of the .mdf and click on that. MAKE SURE YOU SET THE OWNER TO THE CORRECT OWNER.

2. In Query Analyzer, run the sp_detach_db command. Move the files. Run the sp_attach_db. You will have to include the location of the files. Refer to the BOL for the syntax.

-SQLBill

Posting advice: FAQ481-4875

 

[purefreshair]

One other question for you SQLBill,

You write about making sure that I set the owner to the correct owner; how/where is this done?

 

[SQLBill]

In Enterprise Manager, when you attach the database, at the bottom of the attach window there is a dropdown box to select the database owner.

In Query Analyzer, your login will become the owner, but you can change it with sp_changedbowner.

-SQLBill

Posting advice: FAQ481-4875

 

[purefreshair]

Thanks Bill,
I did this task over the holiday break and it worked fine. One other question though; If I look at the database in enterprise manager using the taskpad view, it shows that the database is 126MB in size, with 125.69 used and .31MB free. If I look at the database properties, it is selected to grow in size automatically by 10 percent, with unrestricted file growth. The database is now on a large volume with plenty of space; why do I only see/have .31MB size left?

 

[SQLBill]

Because it hasn't grown yet.

-SQLBill

Posting advice: FAQ481-4875