SQL Server – Will It Work After Changing Drive Letter?

migrationsql server

I have SQL Server 2014 Enterprise Edition installed. Now I have some user databases created and being using. The data files and log files of them are placed on D: drive, which is SAS drive. Now I want to change the disk to use SSD drive. My plan is this:

  • Install the SSD drive and create volume E: on the SSD
  • Stop SQL Server service
  • Copy all SQL Server data and log files from D: to E:
  • Remove SAS drive from my computer
  • Change E: drive letter to D:
  • Start SQL Server service

Will this plan work? Actually I tried this in a test environment and I got errors. After this my test database is in recovery pending state and I got the following errors in the error log:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
    Jun 17 2016 19:14:09 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)
UTC adjustment: 8:00
(c) Microsoft Corporation.
All rights reserved.
Server process ID is 5544.
System Manufacturer: 'OpenStack Foundation', System Model: 'OpenStack Nova'.
Authentication mode is WINDOWS-ONLY.
Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
Registry startup parameters: 
     -d C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Command Line Startup Parameters:
     -s "MSSQLSERVER"
SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Detected 16383 MB of RAM. This is an informational message; no user action is required.
Using conventional memory in the memory manager.
Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
The maximum number of dedicated administrator connections for this instance is '1'
This instance of SQL Server last reported using a process ID of 3336 at 7/15/2018 3:35:10 AM (local) 7/14/2018 7:35:10 PM (UTC). This is an informational message only; no user action is required.
Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
Starting up database 'master'.
CLR version v4.0.30319 loaded.
Resource governor reconfiguration succeeded.
SQL Server Audit is starting the audits. This is an informational message. No user action is required.
SQL Server Audit has started the audits. This is an informational message. No user action is required.
Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
SQL Trace ID 1 was started by login "sa".
Server name is 'ECS-BDE2'. This is an informational message only. No user action is required.
Starting up database 'test1'.
Starting up database 'mssqlsystemresource'.
Starting up database 'msdb'.
Starting up database 'test2'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file d:\DATA\test2.mdf for file number 1.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "d:\DATA\test2.mdf". Operating system error 5: "5(Access is denied.)".
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file d:\DATA\test2_log.ldf for file number 2.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "d:\DATA\test2_log.ldf". Operating system error 5: "5(Access is denied.)".
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'd:\DATA\test2_log.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
The resource database build version is 12.00.5000. This is an informational message only. No user action is required.
A self-generated certificate was successfully loaded for encryption.
Server is listening on [ 'any' <ipv6> 1433].
Server is listening on [ 'any' <ipv4> 1433].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
Server is listening on [ ::1 <ipv6> 1434].
Server is listening on [ 127.0.0.1 <ipv4> 1434].
Dedicated admin connection support was established for listening locally on port 1434.
SQL Server is now ready for client connections. This is an informational message; no user action is required.
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ecs-bde2 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ecs-bde2:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
Starting up database 'model'.
Clearing tempdb database.
Software Usage Metrics is disabled.
Starting up database 'tempdb'.
The tempdb database has 1 data file(s).
The Service Broker endpoint is in disabled or stopped state.
The Database Mirroring endpoint is in disabled or stopped state.
Service Broker manager has started.
Recovery is complete. This is an informational message only. No user action is required.
Error: 18456, Severity: 14, State: 73.
Login failed for user 'ECS-BDE2\Administrator'. Reason: Failed to open the database 'test2' configured in the session recovery object while recovering the connection. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'ECS-BDE2\Administrator'. Reason: Failed to open the explicitly specified database 'test2'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'ECS-BDE2\Administrator'. Reason: Failed to open the explicitly specified database 'test2'. [CLIENT: <local machine>]
Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
Using 'xpstar.dll' version '2014.120.5000' to execute extended stored procedure 'xp_readerrorlog'. This is an informational message only; no user action is required.

If this plan is not flawless, what's the better way to do it? Thanks.

Best Answer

You are receiving errors since

Operating system error 5: "5(Access is denied.)".

You need to grant sql server service account to the "new/renamed" drive.

Once you grant proper access (read / write), your plan will work.