1

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.

Improve this question
10
  • Hi, do the files "G:\SQL_DATA\TEST_NEW.mdf" & "G:\SQL_DATA\TEST_NEW_log.ldf" exist? Does the sql server service account have sufficient rights on this folder? – Randi Vertongen Apr 19 '19 at 8:45
  • Yes both the .mdf and .ldf exists on the path. There is no rights issue. – Karan.T. Apr 19 '19 at 9:08
  • I tried the DBCC TRACESTATUS it give me the following message. " DBCC execution completed. If DBCC printed error messages, contact your system administrator." – Karan.T. Apr 19 '19 at 9:28
  • The G:\ Is an external drive right? What kind of drive is it? – Randi Vertongen Apr 19 '19 at 9:42
  • Its a hard drive size around 1 TB. – Karan.T. Apr 19 '19 at 9:43
2

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
Improve this answer
0

thanks for all your support, I figured out the issue. Our Admin skipped one user. domain\Users was missing from the folders permission. I know it is a silly mistake. But because of this I got to learn more.

I added permission to the folder by adding the domain\Users, and the error disappeared.

Thanks again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.