Sql-server – Database recovery pending on external drive

sql serversql-server-2016

I have database with mdf and ldf file residing on external drive. There was a long powercut and the database restarted. Now the database has gone for recovery pending. The database is around 600 gb.

I have tried the following commands

ALTER DATABASE ‘DATBASE NAME’ SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE ‘DATBASE NAME’ SET ONLINE WITH ROLLBACK IMMEDIATE

but i am getting the following error

Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file
"G:\SQL_DATA\TEST_NEW.mdf". Operating system error 5: "5(Access is
denied.)". File activation failure. The physical file name
"G:\SQL_DATA\TEST_NEW_log.ldf" may be incorrect. Msg 5181, Level 16,
State 5, Line 4 Could not restart database "TEST_NEW". Reverting to
the previous status. Msg 5069, Level 16, State 1, Line 4 ALTER
DATABASE statement failed.

I have tried all options.

Do any body has a working solution. Please share it will save my day.

Best Answer

As previously mentioned, make sure the SQL service account has permissions on the files. Here is a quick snippet to help you do that:

-- From SQL:
-- get full path for master
SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

-- get the service accounts
SELECT servicename, service_account FROM sys.dm_server_services

# From Powershell (run as admin) and replace with values you got from the previous step 

# replace with master db path
$masterDBLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL1\MSSQL\DATA\'

# replace with SQL service account
$serviceAccount = 'DOMAIN\svc_DatabaseEngine'

$mdf = 'G:\SQL_DATA\TEST_NEW.mdf'
$ldf = 'G:\SQL_DATA\TEST_NEW_log.ldf'

$Acl = Get-Acl $masterDBLocation
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($serviceAccount, "FullControl", "Allow")
$Acl.SetAccessRule($Ar)

Set-Acl -Path $mdf -AclObject $Acl
Set-Acl -Path $ldf -AclObject $Acl
Related Question