restoresql serversql-server-2016
Recently while restoring a database with memory optimized filegroup I start facing an issue with below error message:-
Msg 3156, Level 16, State 3, Line 2 File 'XYZ' cannot be restored to
'D:\FolderPath\Memory_Optimized_FolderName'. Use WITH MOVE to identify
a valid location for the file.
First I thought it might be syntax error but syntax was correct and I even checked the permission to filepath which was also fine. So what is the reason for this error?
"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.
You should verify the directory you are trying to place the database in. The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".
Edit
If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1
You should get results similar to this, highlighted the path for my instance:
You can also verify this path through the restore prompt in SSMS, clicking on the "files":
However also noting that the error message operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like
The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'
When you take a backup all information including the file path is included as part of the backup. As you found out if you just run restore database it will try to restore to the exact same location.
when you include with replace, the backup overwrites the existing .mdf and .ldf files. I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).
this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.
EDIT: Here is my normal restore command, whether on the same server or different. I like to specify exactly where things are going.
RESTORE DATABASE dbName
FROM DISK = N'Path\To\Backup.bak'
WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
REPLACE,
STATS = 10
By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.
Best Answer
I found the solution of my problem which was folder location. The folder gets created itself. So once I removed the last folder name which was in some format
And using only below path in my restoration script it starts working fine.