User Database On Different Drive 1

[edsuk]

I have read a number of threads on this, however any clarification of the following would be very much appreciated:

I have been asked to move a database to a new drive on the same server, and am aware this can be done using sp_detach_db and sp_attach_db.

The question is once this has been done - do I or should I also move the system databases, or is it OK for these to remain on the current drive?

Also once the database has been moved do I need to perform any other tasks to ensure users and applications can still connect - Again I assume not as database name has not changed - but confirmation would be very much appreciated.

Obviously the maintenance plan would need modifying - but is there anything else I am missing?

As you can guess fairly new to SQL Server Administration!

Thanks Mark...

 

[ptheriault]

>> do I or should I also move the system databases,
That depends. Are you trying to get all your data files (.mdf) on one array and your log files (.ldf) on another? Are you having disk space issues and that is why you are moving the other database? You don't have to move the system databases.

>>Also once the database has been moved do I need to perform any other tasks to ensure users and applications can still connect.

You may to take this opertunity to update statsistics. But other than that you don't have to do anything. And if your not changing the name of your db you don't have to change your maint plans.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[edsuk]

Paul

Thanks for the speedy response, the reason for moving the db is not really a space issue, more a logical separation of the databases and logs. Which is why I wasn't sure whether this impacted on the systems db's.

As mentioned fairly new to the administrative side of things, so wasn't 100% sure - thanks for the advice will give it a go later on in the week when less activity (after backing up first).

A star for your advice.

Mark...

 

[mrdenny]

Obviously the maintenance plan would need modifying - but is there anything else I am missing?

Actually it doesn't. The maintenance plan works off of database names. It doesn't care where the database is stored.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com