SQL database restores of non-snapshot backups of the system root directory fails with an error

Article:TECH208390  |  Created: 2013-07-09  |  Updated: 2014-01-20  |  Article URL http://www.symantec.com/docs/TECH208390
Article Type
Technical Solution


Issue



SQL database restores of non-snapshot backups of the system root directory fail with an error. If the SQL Server Database Engine is running under the default SQL service account such as the Network Service account or Virtual Accounts (NT Service\MSSQLSERVER for the default instance and NT Service\MSSQL$InstanceName for the named instance), the restore from a non-snapshot backup job to the system root directory fails with an error.


Error



The operating system returned the error '5 (Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '<drive letter>:\<database name>.mdf'


Cause



The restore may fail for the following reasons:

  • The default SQL service account does not have write permission to the root of the drive.
  • Non-snapshot backups of the SQL Server Database Engine have to restore the databases on the drive while running under the service account.

Solution



Grant the appropriate permissions such as full control to the service account.

To grant Network Service account permissions:

  1. In Windows Explorer, right-click the drive that you want to grant permissions, and then click Properties.
  2. On the Security tab, click Edit, and then click Add.
  3. Click Locations, and then select your computer and then click OK.
  4. Click Advanced,and then click Find Now.
  5. Select NETWORK SERVICE, and then click OK.
  6. Click OK.
  7. In the Group or user names list, select NETWORK SERVICE, and then in the Permissions for list click Allow for Full control permissions.
  8. Click OK.

To grant Virtual Accounts (NT Service\MSSQLSERVER for the default instance and NT Service\MSSQL$InstanceName for the named instance) permissions:

  1. In Windows Explorer, right-click the drive that you want to grant permissions, and then click Properties.
  2. On the Security tab, click Edit, and then click Add.
  3. In Enter the object names to select box, type NT Service\MSSQLSERVER or/and NT Service\MSSQL$InstanceName.
  4. Click Check Names.
  5. Click OK.
  6. In the Group or user names list, select MSSQLSERVER and MSSQL$InstanceName, and then in the Permissions for list click Allow for Full control permissions.
  7. Click OK.

For more information about Configuring Windows service accounts and Permissions, see http://technet.microsoft.com/en-us/library/ms143504(v=sql.110).aspx


Supplemental Materials

ValueV-79-57344-33938


Article URL http://www.symantec.com/docs/TECH208390


Terms of use for this information are found in Legal Notices