locked
How to troubleshoot database restoring error? RRS feed

  • Question

  • Here's my sql. Any help is much appreciated.

    USE [master]
    RESTORE DATABASE [ContinuingDB] FROM  DISK = N'C:\Temp\ContinuingDB_full_daily_backup.bak'
    WITH  FILE = 1,  
    MOVE N'ContinuingDev' TO N'E:\DATABASES\ContinuingDB-data.mdf',  
    MOVE N'ContinuingLog' TO N'E:\DATABASES\ContinuingDB-log.ldf',  
    NOUNLOAD,  REPLACE,  STATS = 5
    GO

    And the error:

    Msg 3634, Level 16, State 1, Line 10
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\DATABASES\ContinuingDB-data.mdf'.
    Msg 3156, Level 16, State 5, Line 10
    File 'ContinuingDev' cannot be restored to 'E:\DATABASES\ContinuingDB-data.mdf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3634, Level 16, State 1, Line 10
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\DATABASES\ContinuingDB-log.ldf'.
    Msg 3156, Level 16, State 5, Line 10
    File 'ContinuingLog' cannot be restored to 'E:\DATABASES\ContinuingDB-log.ldf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 10
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 10
    RESTORE DATABASE is terminating abnormally. 


    • Edited by Charlie2 Wednesday, September 18, 2019 12:16 PM
    Wednesday, September 18, 2019 12:16 PM

All replies

  • Your error:  error '5(Access is denied.)

    Indicates the SQL Server service account does not have access to the file/directory.  The restore does not run as the user running the command, but the service account user.


    Wednesday, September 18, 2019 12:35 PM
  • Hi Charlie2,

    >>The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\DATABASES\ContinuingDB-data.mdf'

    The reasons for the error may be the following:

    • The account that sql server is running under does not have access to the location where you are trying to restore the database to. Make sure service account have sufficient permission or try to use local system instead.
    • If the database is encrypted, you must have access to the certificate or asymmetric key used to encrypt the database. Without that certificate or asymmetric key, you cannot retore the database.

    Please refer to https://stackoverflow.com/questions/3494407/mssql-5access-is-denied-error-during-restoring-database for more details.

    >>File 'ContinuingDev' cannot be restored to 'E:\DATABASES\ContinuingDB-data.mdf'. Use WITH MOVE to identify a valid location for the file.

    Did you verify that directory E:\DATABASES\ContinuingDB_full_daily_backup.mdf exists, and there is enough space to hold the files?

    Best Regards,

    Amelia Gu


    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.

    Thursday, September 19, 2019 6:56 AM
  • The "E" drive have 47GB free space. I have the MS SQLSERVER account in this folder. What other account should I put in here?

    Thursday, September 19, 2019 3:47 PM
  • Hi  Charlie2,

    Are there other instances on your machine?

    Did you use the MSSQLSERVER account to backup the files?

    Best Regards,

    Amelia Gu


    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.

    Friday, September 20, 2019 7:06 AM