BigSkyCountry
asked on
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.)
ASKER
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)
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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.
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.
ASKER
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.
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