0

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.

2
  • 2
    It looks like SQL Server doesn't recognize D:\DB_backup\uni.bak as a full path and take it as a relative path from your default backup folder. Did you try to retype de RESTORE statement? Maybe there is something unusual in the string that doesn't show up. Feb 16, 2023 at 19:24
  • There is a space after the drive letter. Feb 16, 2023 at 21:23

1 Answer 1

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.

2
  • But casa is working fine Feb 17, 2023 at 9:32
  • So that file may not have a non-printable character in it... Feb 17, 2023 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.