locked
Reinstall SQL Server 2014 after installing SSDT and SSDT-BI RRS feed

  • Question

  • I have a new windows 10 computer and a fresh install of SQL Server 2014 Developer Edition, SSDT and SSDT-BI. I partitioned the HD to a C drive and D drive where sql server is installed on the C drive, but all the data directories are pointed to the D drive.

    However, today the situation has changed I will be adding a new physical hard drive (SSD) to the machine and this will become the new D drive. 

    Question #1, is it possible to copy all the data directories from the first D drive to the new D drive with out corrupting the SQL Server instance?  (I assume not).  I would first change the drive letter of the existing D drive to something else, then assign "D" to the new drive, then attempt to move all data directories to the new D drive.

    Question #2, if simply moving the data directories to another drive is not possible, then I must uninstall and reinstall sql server.  So the question is, do I need to uninstall SSDT and/or SSDT-BI also?  Exactly what order should I uninstall these things?

    Thanks!

    Thursday, August 13, 2015 4:15 PM

Answers

  • Hello,

    But you are not taking in consideration permissions. SQL Server service accounts should have full control permissions over the folder containing database files and the files.

    Please examine SQL Server error log.

    https://technet.microsoft.com/en-us/library/ms187885(v=sql.120).aspx



    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by moondaddy Friday, August 14, 2015 3:35 PM
    Friday, August 14, 2015 3:06 AM
  • Right, I suspected it was related to that, but at first glance everything looked the same.  with a closer look, when I right clicked on a folder and selected security, I saw the user/server instance "MSSQLSERVER" was assigned to all items in both the original and the new drive, but when I clicked on edit, for the new drive "Full control" was not selected.  upon further inspection I found MSSQLSERVER was not checked for a few of the permission levels and in some cases, MSSQLSERVER did not exist at all on the new drive where it did on the original drive.

    I tried to assign MSSQLSERVER as a user to the folder, but got the message that MSSQLSERVER could not be found.  This link gave me the correct syntax to find assign the server instance:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/345031e7-88c5-40e5-bfda-3880122045e0/error-17204-fcbopen-failed-how-to-set-persmission-correctly-on-a-datalog-file-for-sql-server?forum=sqldatabaseengine.

    the syntax was "NT Service\MSSQLSERVER" where "MSSQLSERVER" is the name of the server instance.  using this I was able to assign it to the remaining data folders where it was missing.  I ran repair again, and now things seem to work (although I haven't actually done anything yet, but SSMS was able to open the server and browse the its artifacts.).

    I think I'm OK now.

    Friday, August 14, 2015 3:43 AM

All replies

  • 1. Yes you can move data files of SQL Server user and system databases its totally supported and feasible. You need to refer to this link how to do it

    2. The uninstall and reinstall is only required of you want to completely move SQL Server from one drive to other. Remember sql server not the data files and log files.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, August 13, 2015 4:26 PM
  • Thanks for the quick response.  Just to be clear, the "data directories" include:

    D:\MSSQL\Backup

    D:\MSSQL\Data

    D:\MSSQL\OLAP

    D:\MSSQL\TempDB

    as well as system related directories such as

    D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA

    D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\FTData

    D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\JOBS

    etc...

    Are you saying all of these can be moved with no problem?

    Thanks again.

    Thursday, August 13, 2015 4:44 PM
  • Yes suppose old location of data file is

    1. D:\MSSQL\data

    You can make new location as

    E:\MSSQL\Backup or C:\MSSQL\Backup what ever you like

    Provided E drive exists. I guess this is what you are asking ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, August 13, 2015 4:52 PM
  • Yes, thanks.  I should receive it in a few hours and will post how it went.
    Thursday, August 13, 2015 5:37 PM
  • Hello,

    Alternatively, you can use detach and attach options on SQL Server Management Studio to move the database files using the following articles:

    1. First detach them https://msdn.microsoft.com/en-us/library/ms191491.aspx
    2. Cut and paste from the actual location to the new location.
    3. Finally attach them. https://msdn.microsoft.com/en-us/library/ms190209(v=sql.120).aspx


    At the end, you may want to change the default database location, so each time you create a new database it is created on the new location.

    https://msdn.microsoft.com/en-us/library/dd206993(v=sql.120).aspx



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com




    Thursday, August 13, 2015 6:27 PM
  • This did something bad to my instance of sql server.

    First, I stopped and then disabled all sql services.

    Then I rebooted the machine and changed the drive letter of the D drive to H, and the new drive to D.

    Then I copied ALL sql data directories to the new D drive.

    Then I set all the sql services back to Manual or Automatic according to the original configuration and I rebooted the computer.

    the service for the instance did not start.  When I tried to manually start it I got the error msg:

    Windows could not start the SQL Server (MSSQLSERVER) on Local Computer.  For more information, review the System Event Log.

    Wow, not good.  Any ideas how to resolve this?

    Thanks.

    Would it OK to run a SQL Server Repair?  I think the problem may have something to do with the type of permissions SQL Server assigns to it's system data directories.
    • Edited by moondaddy Friday, August 14, 2015 12:07 AM forgot some text
    Thursday, August 13, 2015 10:38 PM
  • What I'm trying to do is a little different than simply change the location of the data directories.  I'm swapping out the drive with a new drive, so all the paths will remain the same.  All data directories are on the D drive.  I renamed the D drive to something else, assigned "D" as the drive letter to the new drive, copied all the data directories to the new "D" drive, and started SQL Server again, although, this didn't work as I go the error shown in the screenshot above.
    Friday, August 14, 2015 2:16 AM
  • Hello,

    But you are not taking in consideration permissions. SQL Server service accounts should have full control permissions over the folder containing database files and the files.

    Please examine SQL Server error log.

    https://technet.microsoft.com/en-us/library/ms187885(v=sql.120).aspx



    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by moondaddy Friday, August 14, 2015 3:35 PM
    Friday, August 14, 2015 3:06 AM
  • Right, I suspected it was related to that, but at first glance everything looked the same.  with a closer look, when I right clicked on a folder and selected security, I saw the user/server instance "MSSQLSERVER" was assigned to all items in both the original and the new drive, but when I clicked on edit, for the new drive "Full control" was not selected.  upon further inspection I found MSSQLSERVER was not checked for a few of the permission levels and in some cases, MSSQLSERVER did not exist at all on the new drive where it did on the original drive.

    I tried to assign MSSQLSERVER as a user to the folder, but got the message that MSSQLSERVER could not be found.  This link gave me the correct syntax to find assign the server instance:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/345031e7-88c5-40e5-bfda-3880122045e0/error-17204-fcbopen-failed-how-to-set-persmission-correctly-on-a-datalog-file-for-sql-server?forum=sqldatabaseengine.

    the syntax was "NT Service\MSSQLSERVER" where "MSSQLSERVER" is the name of the server instance.  using this I was able to assign it to the remaining data folders where it was missing.  I ran repair again, and now things seem to work (although I haven't actually done anything yet, but SSMS was able to open the server and browse the its artifacts.).

    I think I'm OK now.

    Friday, August 14, 2015 3:43 AM
  • I am sure you did not completely referred to link I posted. Whenever you move data file always use alter database command you did not used it , you simply moved it so you are bound to get error messages.

    NT Service\MSSQLSERVER is Virtual service account which you cannot find as such. Instead of using this account you can run it with local system or NT Authority\system for time being and start sql server services


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, August 14, 2015 4:46 AM
  • Thanks.  SQL Server services are now running and I was able to do some tasks such as attach some customer databases using SSMS.  Does this mean things are now OK or are there still underling issues that will bite me later?
    Friday, August 14, 2015 4:52 AM
  • Thanks.  SQL Server services are now running and I was able to do some tasks such as attach some customer databases using SSMS.  Does this mean things are now OK or are there still underling issues that will bite me later?

    If sql server is online and running it is good. Whether error will come in near future depends on how you work with it. If you face any issue you can use forum for help

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, August 14, 2015 5:15 AM