none
unable to backup/restore database with SQL Server 2019 - works ok with 2016 RRS feed

  • Question

  • We have an application which is currently deployed using SQL Server 2016.

    As part of its features a user connected to a Support website is able to backup and restore the database.

    The webservice relays the request to a Windows Service, running as SYSTEM, which actually performs the task. The backup is saved to a file named with the date and time of the backup. Prior to the restore, the database is placed into Single User Mode.

    In SQL Server 2016 this works as expected.

    We now want to port to 2019.

    Backups are performed OK, but when we want to restore the database the Windows Service gets the error: User does not have permission to RESTORE database 'XXX'

    The file identified as the source of the restore does exist and has the following security:

    • SYSTEM : Allow everything
    • MSSQLSERVER : allow everything
    • Administrators : allow everything
    • UserX (what I log onto the machine as) : allow everything

    I tried using SQL Management Studio (running as Administrator) and connecting with Windows Authentication using UserX (so not quite the same as the Windows Service running as SYSTEM, but should be close enough)

    I attempt the query:

    restore database WoofPACS from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\fulldatabase2019.11.25.16.57.24.bak'

    and get the error

    Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\fulldatabase2019.11.25.16.57.24.bak'. Operating system error 2(The system cannot find the file specified.).

    Monday, November 25, 2019 7:49 PM

Answers

  • Solved it!

    I had created a user for my database, given it full permissions and mapped it to the SYSTEM login.

    But the NT AUTHORITY\SYSTEM login was lacking sufficient permissions.

    As it was when I was failing to restore, SYSTEM had the following roles:

    By adding "sysadmin", the restore was successful.

    Interestingly, the "sysadmin" role is not set for SYSTEM in my SQL Server 2016 and restores run successfully on it.


    • Marked as answer by grsuggitt Tuesday, November 26, 2019 8:26 PM
    • Edited by grsuggitt Tuesday, November 26, 2019 8:55 PM
    Tuesday, November 26, 2019 8:26 PM

All replies

  • As a test, can you try and change the account running the Windows Service (the account actually doing the restore) to some other account other than "SYSTEM", then, whatever account you changed it to, ensure that account has permissions inside Sql Server to do a restore. This sounds more like an issue within Sql Server security than with an issue with the security of the backup file you are trying to restore.
    Monday, November 25, 2019 9:25 PM
  • Hi grsuggitt,

     

    >>Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\fulldatabase2019.11.25.16.57.24.bak'. Operating system error 2(The system cannot find the file specified.).

     

    There are generally two reasons for this error. One is that the path does not exist, and the other is that the service account of the SQL server does not have read and write permissions on the folder. Would you please check the privileges on C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup ?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 26, 2019 5:35 AM
  • Thank-you for your suggestions, but as I stated in my posting the file definitely does exist and I have also provided the security permissions on the file.

    As you can see the file allows MSSQLSERVER complete control of the file. MSSQLSERVER is identified as the owner of the file (after all, it created it!)


    • Edited by grsuggitt Tuesday, November 26, 2019 2:35 PM
    Tuesday, November 26, 2019 2:27 PM
  • I don't see anywhere in SQL Server for "restore" permissions. I see db_backupoperator. where do I look for "restore" permissions?

    Tuesday, November 26, 2019 2:33 PM
  • To restore a database you need either of these in Sql Server:

    Server Role:  sysadmin

    DB role:  db_owner

    Tuesday, November 26, 2019 3:17 PM
  • I create the database with SQL scripts that are executed by sqlcmd

    In the script I create a user for the database like so:

    CREATE USER TheSystem FOR LOGIN [NT AUTHORITY\SYSTEM];
    EXEC sp_addrolemember 'db_datareader',     'TheSystem';
    EXEC sp_addrolemember 'db_datawriter',     'TheSystem';
    EXEC sp_addrolemember 'db_accessadmin',    'TheSystem';
    EXEC sp_addrolemember 'db_backupoperator', 'TheSystem';
    EXEC sp_addrolemember 'db_ddladmin',       'TheSystem';
    EXEC sp_addrolemember 'db_owner',          'TheSystem';
    EXEC sp_addrolemember 'db_securityadmin',  'TheSystem';

    Tuesday, November 26, 2019 5:28 PM
  • Solved it!

    I had created a user for my database, given it full permissions and mapped it to the SYSTEM login.

    But the NT AUTHORITY\SYSTEM login was lacking sufficient permissions.

    As it was when I was failing to restore, SYSTEM had the following roles:

    By adding "sysadmin", the restore was successful.

    Interestingly, the "sysadmin" role is not set for SYSTEM in my SQL Server 2016 and restores run successfully on it.


    • Marked as answer by grsuggitt Tuesday, November 26, 2019 8:26 PM
    • Edited by grsuggitt Tuesday, November 26, 2019 8:55 PM
    Tuesday, November 26, 2019 8:26 PM
  • @rvsc48 I found that having the user with db_owner role was insufficient. I needed to add "sysadmin" to the Server Role for NT AUTHORITY\SYSTEM
    • Edited by grsuggitt Tuesday, November 26, 2019 8:31 PM
    Tuesday, November 26, 2019 8:30 PM