Forum Discussion
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';
GOThe 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 ONLINEYet, 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
- manny213Brass Contributor
I figured out the permission portion. It now has access. What is the next step to complete the migration?
Thank you
- manny213Brass Contributor
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"?
Yes, this account
- manny213Brass Contributor
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
- manny213Brass Contributor
Hi. Is someone able to help me with this issue? Thank you
- SDTripathiCopper 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.
- SDTripathiCopper 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.