Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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).
Open "SQL Server Configuration manager"=>set "SQL Server" logon as=>Local System.
Did you check if you are writing to correct location?
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.)
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:
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.
