Forum Discussion

Jonas_B2235's avatar
Jonas_B2235
Copper Contributor
Mar 09, 2021

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

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

  • Robin Shanab's avatar
    Robin Shanab
    Iron Contributor
    This article is good enough to understand the solution for this error: https://sqlbackupandftp.com/blog/how-to-solve-operating-system-error-3
  • Jonas_B2235's avatar
    Jonas_B2235
    Copper Contributor
    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 ''
    GO
    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
    GO
    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

    GO

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


    EXECUTE master.dbo.xp_create_subdir N'F:\DBBackup\DW_ARKIV'
    GO
    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
    GO

    Is it a bug in the Studio? I know there are newer ones and I have to try them out.
    • Jonas_B2235's avatar
      Jonas_B2235
      Copper Contributor
      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.
      • Jonas_B2235's avatar
        Jonas_B2235
        Copper Contributor
        Nothing new ... find this afterwards
        https://www.sqlservercentral.com/forums/topic/is-this-a-bug-in-sql-server-a-trailing-space-in-a-database-name-backups-fail
        and this
        https://www.sqlservercentral.com/forums/topic/space-given-in-db-name-and-backup-failure

Resources