Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\‪D:\DB_backup\uni.bak'

Question

I have 2 database backup files casa.bak (25 GB) and uni.bak (176 GB) in the same folder. I'm using the same script to restore the database for both files previously both were working successfully. Now casa.bak is restoring successfully but uni.bak is failing the scripts are mentioned below:

casa.bak script which is successfully working:

USE [master]
GO

ALTER DATABASE casa
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE casa FROM 
DISK = 'D:\DB_backup\casa.bak' WITH REPLACE

uni.bak script which is failing:

USE [master]
GO

ALTER DATABASE uni
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE uni FROM 
DISK = '‪D:\DB_backup\uni.bak' WITH REPLACE

I get this error:

Executed as user: NT SERVICE\SQLSERVERAGENT. Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000]
(Message 5060) Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\‪D:\DB_backup\uni.bak'. Operating system error 123 (The filename, directory name, or volume label syntax is incorrect.).
[SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

Please suggest.

I've tried using changing folder, granting permissions to folder, changing .mdf and .ldf files paths.

Changing script to:

USE [master]
GO

ALTER DATABASE uni
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE uni FROM 
DISK='D:\DB_backup\uni.bak' 
WITH MOVE  N'uni_Latest' TO N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\uni_New1.mdf',

MOVE N'uni_Latest_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\uni_New1.ldf',
REPLACE

Nothing is working.

Answer 1

The issue looks (with the data provided) like an issue with a non-printable character (or other such character issues) being using in a directory junction (folder link).

This is fairly simple to reproduce:

1. Create a directory junction. I used:

   mklink /J "C:\Backups" "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup"

2. Craft a restore command.

3. Put in a non-printable or otherwise invalid character in the path. I put a space before C:.

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ C:\Backups\fginit.bak'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

I would type in the location and not copy and paste items that may have non-printable or otherwise invisible characters in the data.