Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-15
9
Medium Priority
?
198 Views
Last Modified: 2012-05-12
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.)
0
Comment
Question by:BigSkyCountry
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36975073
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
 

Author Comment

by:BigSkyCountry
ID: 36976189
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976392
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976402
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36976412
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976425
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
 

Author Comment

by:BigSkyCountry
ID: 36976682
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36977255
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
 

Author Comment

by:BigSkyCountry
ID: 36977556
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question