SQL SVR2008 was installed on C drive along with databases (bad decision!) Best way to move to D?

We have a data server that was installed badly - MS SQL 2008 application with databases was installed on the C drive - now of course the C drive is filling = D drive is huge and empty.  Whats the best way to move the software and the database off C and over to D (hopefully with minimal down-time - 24/7 retail website.)
BigSkyCountryAsked:
Who is Participating?
 
Anthony PerkinsCommented:
And here is how you move a user database:
SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms345483.aspx

SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/ms345483(v=SQL.100).aspx

As you can see taking the databases offline and moving the files is trivial compared to the traditional detach/attach method.  Not to mention the fact that some database settings (DB_CHAINING, TRUSTWORTHY, etc.)  are cleared when doing this.
0
 
EyalCommented:
first you need to move the data/log files by detaching, moving and reattaching you databases.
the downtime will be the time that will take you to move the files to the new location on drive D

If the database engine it self will still be located in the system disk nothing bad will happen
0
 
BigSkyCountryAuthor Commented:
Thank you for your reply.  Is there an article/documentation available with the exact steps needed to accomplish this?  I found a section in the Administrator's guide showing the 'Copy/Move Wizard' -   also using Alter Database - and again with attache/move/detach (this seems to be the best method but would be helpful to get explicit steps)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Anthony PerkinsCommented:
First of all there is no need to detach/attach databases.  A better approach is to simply take them offline and move the data/log files.  If you are interested in that approach let me know and I can elaborate.

Secondly, while you can move the user databases without shutting down the service that is not true for the system databases.  This will require downtime, although it should not be more than 10 minutes.
0
 
Anthony PerkinsCommented:
In order to move your system databases use the instuctions from of the following documents:
SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.100).aspx

SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.105).aspx
0
 
Anthony PerkinsCommented:
One thing I would recommend is that you define a clear structure for the database files on your new drive, rather than dumping them all in the same folder.  Also, instead of using a long winded path as in the default installation you can use a folder structure like this:
SQLData
         System
SQLLog
         System
SQLErrorLog

So all your user data files would go in SQLData and the system data files in SQLData\System. Likewise all the Transaction Log files would go in SQLLog and the system Transaction Log files in SQLLog\System.  If you also decide to move your SQL Server error log that could go in a folder called SQLErrorLog.
0
 
BigSkyCountryAuthor Commented:
acperkins - Thanks for the info - and I am interested in moving the database without detach/attach if possible.  I can stop the data server from any access (totally isolate it from user)  so please elaborate - I prefer this method -   Also I will definitely setup a clear structure on the D drive - (already have the backups moved over there)  We anxiously await your reply- Thanks again
0
 
Anthony PerkinsCommented:
It looks like you have everything under control.

One other thing, while I agree that it is a good idea to have a SQL Backup folder, make sure you have a copy on another drive.  If you lose drive D you will have lost everything.  Not a good place to be.
0
 
BigSkyCountryAuthor Commented:
Yes - we keep a backup on the same drive plus an off-site as well. Had a drive fail years ago and learned the hard way.  - Thanks again for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.