unable to open physical file after move

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

unable to open physical file after move

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-03-20 : 12:11:27

Hi,

I want to move a pair of mdf/ldf within the same drive, but got this error at last step:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "G:\data\scat.mdf". Operating system error 5: "5(Access is denied.)".

I am running following in ssms in sql2012.

--mdf in G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
--ldf in F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
--move to G:\data and F:\logdata

alter database scat set offline
go
alter database scat
modify file (Name = scat, filename='G:\data\scat.mdf')
Go

alter database scat
modify file (Name = scat_Log, filename='F:\logdata\scat_log.ldf')
Go

--then copy the files to the new locations

alter database scat set online
go

What did I do wrong?

Thanks!
Hommer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-20 : 12:58:39

Is G:\Data a mount point or is Data just a folder on the G drive? If it's a mount point and if you didn't install SQL files onto it from the get-go, then you'll need to run the cacls command with the service account.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-03-20 : 13:11:24

It is just a folder.

And now it shows as Recovery Pending

and

select name, physical_name, state_desc from sys.master_files
where database_id =db_ID (N'scat');

has the new locations, state_desc=ONLINE

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-03-21 : 10:11:26

I was able to reverse back to the old locations, and it is up and running.

But it still baffles me why the last step failed.

I even tried to just move couple levels up but inside the same folder, i.e. under program files, and it gave me the same error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-21 : 12:38:27

Have you tried the old way using detach/copy files/attach?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-03-21 : 17:15:19

will give that a try next week. Also restore with move is another option.
Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-26 : 18:49:06

Does the SQL Service Account have rights to the G:\Data directory?

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources