0

I am trying to restore a database from a backup (.bak) file which has four different files. I am trying point each file into a different disk drive on the server. The server has following four drives:

  1. Data (G:\)
  2. Index (H:\)
  3. Changes (I:\)
  4. Log (J:\)

I'm getting the following error when I try to save the file into the root folder (for example: I have pointed the index file to H:\DbRestore_Test-index.mdf), but if I point to a subfolder in the same disk drive it is working without an issue (for example: H:\Indexes\DbRestore_Test-index.mdf).

TITLE: Microsoft SQL Server Management Studio

Restore of database 'DbRestore_Test' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------ ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'H:\DbRestore_Test-index.mdf'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&LinkId=20476

I have taken a look at this Stackoverflow page: Ticking the Relocate all files to folder checkbox doesn't help as I need to save files into four different drives and there is an option to specify only two drives.

As I mentioned above, I can resolve this by simply pointing/saving the file into a subfolder within that drive, but curious to know the reason why I wasn't able to restore it into the root folder itself. Any help/advise would be much appreciated.

Environment:

SQL Server 2012

Windows Server 2012 R2 Standard

Community
  • 1
  • 1
Sathish
  • 1,936
  • 4
  • 28
  • 38
  • 2
    Check the permissions of the user which SQL Server runs under (see config manager/services etc. to find out). Failing that, could it be User Account Control (UAC)? – Bridge Mar 30 '15 at 13:46
  • 1
    The root folder of a drive tends to require administrative privilege, while subfolders tend to not have this restriction. If you need the backup to go to the root folder of the drives, then you need to be sure the SQL Server user has privileged access to the root folder of the drives. – Russ Mar 30 '15 at 13:58

1 Answers1

1

This worked for me. After you select your database to restore, select file option and check realocate all files to folder

enter image description here

anil shrestha
  • 2,136
  • 2
  • 12
  • 26