asked on

Microsoft SQL Restore Back issue

Trying to create a nightly backup restore of a SQL database. The first time I run it works fine. But when I try and run it again. I get the following error message:

Production-Differential Backup' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_2.mdf'. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_3.mdf'. [SQLSTATE 42000] (Error 3634) File 'company Production-Full Backup' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_3.mdf'. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_4.ldf'. [SQLSTATE 42000] (Error 3634) File 'company_NAV 2009_061009_Log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_4.ldf'. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156) Problems were identified while planning for the RESTORE statement. Previous messages provide details. [SQLSTATE 42000] (Error 3119) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I am using the following: (I do have a job to detach before this runs)
restore filelistonly from disk ='\\server\SQL Backups\Daily backups\Refresh\File_Refresh.bak'

-->logicalName
-->---------
-->company_NAV 2009_061009_Data
-->company Production-Differential Backup
-->company Production-Full Backup
-->company_NAV 2009_061009_1_Data
-->company_NAV 2009_061009_Log

Open in new window

-->test1_Log

restore database DWHSE from disk ='\\Server\SQL Backups\Daily backups\Refresh\File_Refresh.bak'
with

move 'company_NAV 2009_061009_Data' to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE.mdf',
move 'company Production-Differential Backup' to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_2.mdf',
move 'company Production-Full Backup' to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_3.mdf',
move 'company_NAV 2009_061009_1_Data' to 'E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_1.mdf',
move 'company_NAV 2009_061009_Log' to 'F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DWHSE_4.ldf',
replace

Open in new window

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jyoung127

ASKER

I agree and I figure that much. I have check the full permissions have allowed for that folder and all folder under it but still does not go.

SQLserveragent is running as the service for SQL server agent. which has full permissions.
MSSQLserver is running the SQL server. Also has full permissions.
Admin user that I used to create the job also has full permissions.

AM I missing any permission ?

Heiko Bialozyt

please add "with replace" to your restore statement.

Heiko

Raja Jegan R

Try starting SQL Server Service with some Windows Admin account and try once to check whether it is something to do with accessing the data and log files.

jyoung127

ASKER

Had to use a windows account and change it from the SQL Server default permissions. Even though the folder had the Correct permission but it would not work.