1

When I tried to restore a backup an error occurred showing access denied. The error message is as follows:

Restore failed for Server 'VAIO\sqlexpress'. (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ihrd_mvk_newspaperboy.mdf'. (Microsoft.SqlServer.Smo).

3

3 Answers 3

1

Did you check if you are writing to correct location? enter image description here

0

When you restore a database, the restore process (RESTORE DATABASE ...) is running in the context of your account.

Your account might not have access to the C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ directory.

This is because when you install SQL Server, the directories are created in the context of an internal SQL Server Login / Account. The account is normally in the form: NT Service\MSSQL$<INSTANCE_NAME> (e.g. in your case NT Service\MSSQL$MSSQLSERVER)

If you run the following commands in an Administrative CMD Shell against the last directory level you can access in Windows Explorer, then you can see which accounts have access to the DATA directory:

C:\> cd "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\"

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL> cacls DATA

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA 
                           CREATOR OWNER:(OI)(CI)(IO)F
                           NT AUTHORITY\SYSTEM:(OI)(CI)F
                           BUILTIN\Administrators:(OI)(CI)F
                   ! -->   NT SERVICE\MSSQL$<INSTANCE_NAME>:(OI)(CI)F

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL>

This is a list of accounts and/or Windows Groups that are allowed to access the DATA directory of your SQL Server instance. (On your laptop you might see other accounts and some accounts may be missing.)

Solution

In Windows Explorer click through the directories until you are presented with a pop-up informing you that you don't have access to the directory:

Explorer Window - You don't currently have permission to access this folder - Click Continue to permanently get access to this foler

If you click on Continue in this message, then your Windows account will be added to the list of accounts that are allowed to access the DATA directory/folder.

Note: You will have to do the same procedure for the TLOG / DATA directory to be allowed to restore the *.ldf file to the required sub-directory.

Your RESTORE DATABASE ... command should then work.

-1

Open "SQL Server Configuration manager"=>set "SQL Server" logon as=>Local System.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.