Suddenly unable to backup a new database, os path error 3

Copper Contributor

Hi, I have two MS SQL Server 2016 Std Service Pack 2 instance running on two outsourced OS machine. One Windows Server 2016 and one Windows Server 2012 R2. Then I recently added a new database and modified my existing maintenance job but then I got like "cannot find the path specified, OS error 3 and the path is the backup destination path and file. 
Windows SQLServerAgent service is run by SQLSERVERAGENT and "NT Service\SQLSERVERAGENT" has full or modify access there. It doesnt matter if I create a new maintenance job it still the same problem. The problem also exist on both server, but only on the new databases, with the existing databases there are no problem.

Unfortunately, becuase the two OS servers are outsourced I dont know what happens in the "background" but I have an account with administrative rights.
I cheated and tried to put both "Nt Service\MSSQLSERVER" and Nt Service\SQLSERVERAGENT" in the local administrators group but nothing helps. Any idé whats wrong?

I also have a service request with the outsourced firm where I still wait for help.

4 Replies
When I look at the history log and choose to View T-SQL done by MS SQL Mgmt Studio then I found a lot of errors in it. When I manually fix that code the backup are working ...
The MS SQL Mgmt Studio version is 18.6.
Here is the automatically created WRONG/Not working code (extra space and dots):
EXECUTE master.dbo.xp_create_subdir N''F:\DBBackup\DW_ARKIV ''
BACKUP DATABASE [DW_ARKIV ] TO DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH NOFORMAT, NOINIT, NAME = N''DW_ARKIV _backup_2021_03_10_134006_2704819'', SKIP, REWIND, NOUNLOAD, STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''DW_ARKIV '' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''DW_ARKIV '' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''DW_ARKIV '''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND


and here are the simple code I test/corrected to and that works

EXECUTE master.dbo.xp_create_subdir N'F:\DBBackup\DW_ARKIV'
BACKUP DATABASE [DW_ARKIV ] TO DISK = N'F:\DBBackup\DW_ARKIV\DW_ARKIV_backup_2021_03_10_134006_2704819.bak' WITH NOFORMAT, INIT, NAME = N'DW_ARKIV_backup_2021_03_10_134006_2704819', SKIP, REWIND, NOUNLOAD, STATS = 10

Is it a bug in the Studio? I know there are newer ones and I have to try them out.
18.8, the latest MS SQL Mgmt Studio did not work either. It was the 18.6 which create the database-name with an extra space on the end. Now you think I'm crazy and did it my self ... oh yes but then that I should do this exact same misstake on two different instances which I do not believe.
I have been working with MS SQL Mgmt Studio for over 20 years and never had this kind of bug, which I beleive it must be.
Case closed.
This article is good enough to understand the solution for this error: