Forum Discussion

manny213's avatar
manny213
Brass Contributor
Dec 31, 2024

Issue migrating SQL Server 2019 db to a new drive

Hi everyone

I am a rookie and need some help.  I bought a new drive to store my sql server 2019 db.  The old drive is C-drive and the new drive is F-drive. I followed the 5 step example from here:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-ver16

I tried to bring the db back online and when I did I got this error:

(2 rows affected)
Msg 5120, Level 16, State 101, Line 16
Unable to open the physical file "F:\SQL Server Data\OptionsDB.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
File activation failure. The physical file name "F:\SQL Server Data\OptionsDB_log.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 16
Could not restart database "OptionsDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 16
ALTER DATABASE statement failed.

I ran this code to see where the files are being stored:

SELECT name,
       physical_name AS CurrentLocation,
       state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'OptionsDB')
--      AND type_desc = N'LOG';
GO

The output shows the new location:

name	CurrentLocation	state_desc
OptionsDB	F:\SQL Server Data\OptionsDB.mdf	ONLINE
OptionsDB_log	F:\SQL Server Data\OptionsDB_log.ldf	ONLINE

Yet, when I go to the F-drive to see if the files there are there are no files there.  I even checked the show hidden file option in case the files are hidden.  Nothing there.  I think this is the issue but I have no clue how to fix it.  I am totally lost.  Can someone please help me?

Thank you

 

 

8 Replies

  • Hi Manny, 

    You must give access rights to service account for "F:\SQL Server Data" folder. You can see your service accounts from sql server configuration manager

    • manny213's avatar
      manny213
      Brass Contributor

      Hi Yunus_Emre_ISIK 

      I figured out the permission portion.  It now has access.  What is the next step to complete the migration?

      Thank you

    • manny213's avatar
      manny213
      Brass Contributor

      Hi Yunus_Emre_ISIK 

      Thank you.  I am new to this. How exactly do I give access rights?  I took a look at configuration manager.  Is this the service account  "NT Service\MSSQLSERVER"?

    • manny213's avatar
      manny213
      Brass Contributor

      Hi Yunus_Emre_ISIK 

      Thank you for your reply.  I am completely new to this.  How exactly do I do this?  I checked the configuration manner.  Would the service account be "NT Service\MSSQLSERVER"?  Can you please give example on how to give appropriate permission?

      Thank you

  • manny213's avatar
    manny213
    Brass Contributor

    Hi.  Is someone able to help me with this issue?  Thank you

  • SDTripathi's avatar
    SDTripathi
    Copper Contributor

    Looks like you missed Step-3: 

    Move the file or files to the new location

     

    Please copy files or move them. Since you have already altered database, just set database offline and then set it online. 

  • SDTripathi's avatar
    SDTripathi
    Copper Contributor

    Looks like you missed the step-3: 

    Move the file or files to the new location

     

    Please copy files yourself or move them. Since you have already altered database, after you finish copying files, just set database offline and then set it online. 

Resources