Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 15 July 2016

Unable to Open Physical File Operating System Error Access is Denied Could not Restart Database Alter Database Statement FailedFixed Issue While Moving DATABASE to Different Drive in SQL Server

Unable to Open Physical File Operating System Error Access is Denied Could not Restart Database Alter Database Statement Failed 
 
Fixed Issue While Moving DATABASE to Different Drive in SQL Server

T-SQL to create Database on Different Drive:

Use master

CREATE DATABASE [TESTDIFFERENTDRIVEDB]
ON
( NAME = TESTDIFFERENTDRIVEDB_data,
    FILENAME = 'D:\SQL\Data Files\MDF\TESTDIFFERENTDRIVEDB_mdf.mdf' )
LOG ON
( NAME = TESTDIFFERENTDRIVEDB_log,
    FILENAME = 'E:\SQL\Log\TESTDIFFERENTDRIVEDB_ldf.ldf')
        GO



T-SQL to create default C:/Drive:

Use master

CREATE DATABASE [
ProductDB]
ON
( NAME = TESTDIFFERENTDRIVEDB_data,
    FILENAME = '
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\ProductDB.mdf' )
LOG ON
( NAME = TESTDIFFERENTDRIVEDB_log,
    FILENAME = '
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\ProductDB_log.ldf')
        GO


or
Create database ProductDB



Above Case there is no issue with FOLDER on Data Files it will create on Drive with no issueson Create Database Command

But Alter database dbname modify file Command case it will give you error due to Permissions Issue
let me explain in detail

Above is create statement so it is Succeed but now you try to do it on same with alter statement would give you error kindly see below due to Permissions Issue on Data Files Folder

 Lets create database on default location



now see its physical file using sp_helpdb command


Now we try to move MDF & LDF file in Which have space on Data Files letter see below

D:\SQL\Data Files\MDF\TESTDIFFERENTDRIVEDB_mdf.mdf

and

D:\SQL\Data Files\LDF\TESTDIFFERENTDRIVEDB_ldf.ldf

After that we need to set offline database bring database as offline

    USE MASTER

    ALTER DATABASE [TESTDIFFERENTDRIVEDB] SET OFFLINE WITH ROLLBACK IMMEDIATE

Now go to the default location MDF & LDF file just cut and paste to new location

default location see below


 Now we need to move this file to new location as see below


MDF file as see below



LDF file as see below



Now we need to alter database dbname modify command to alter it

It gives some information messages

After that ALTER DATABASE dbname SET ONLINE gives error due to that Space Error as see below


Solution

so let make db to be offline

    ALTER DATABASE [TESTDIFFERENTDRIVEDB] SET OFFLINE WITH ROLLBACK IMMEDIATE


We need to check Whether Drive have enough permissions or not




 
So After give/select ALLOW Enough permissions on Data Files Folder it will work Fine.

After moving that ISSUE ALTER DATABASE MODIFY FILE command



Now you can see db was moved to new files as see below




So Kindly if it gives Error you should check/give enough allow permissions on that folder then whether

It should not have Read only permission



That is it.