December 11, 2014 at 1:09 pm
-- SELECT @@ServerName
-- I'm trying to move D:\MSSQL\Data\Staging_log.ldf' to L:\MSSQL\Log\Staging_log.ldf'
-- 1. Set Database OFFLINE
ALTER DATABASE Staging SET OFFLINE;
-- 2.Move the file or files to the new location.
-- 3.For each file moved, run the following statement.
ALTER DATABASE Staging MODIFY FILE ( NAME = Staging_log, FILENAME = 'L:\MSSQL\Log\Staging.ldf' );
-- 4.Run the following statement.
ALTER DATABASE Staging SET ONLINE;
I get the following error:
Msg 5120, Level 16, State 101, Line 37
Unable to open the physical file "L:\MSSQL\Log\Staging.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "L:\MSSQL\Log\Staging.ldf" may be incorrect.
New log file 'D:\MSSQL\Data\Staging_log.ldf' was created.
[/code]
I do not get it. The first Database moved the log file just fine, however this one does not work?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2014 at 1:14 pm
You might ensure the physical file has the correct ACL. I have at times had to do this to ensure the ACL matches up on each individual file. I generally just say inherit permissions for the file if I have other files in that directory that work fine, this usually fixes it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 11, 2014 at 1:38 pm
Shawn Melton (12/11/2014)
You might ensure the physical file has the correct ACL. I have at times had to do this to ensure the ACL matches up on each individual file. I generally just say inherit permissions for the file if I have other files in that directory that work fine, this usually fixes it.
I was looking at that.
It looked ok but I'm not sure on what exactly to do?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2014 at 1:56 pm
Could it be that the file name is different?
L:\MSSQL\Log\Staging_log.ldf'
VS
L:\MSSQL\Log\Staging.ldf'
December 11, 2014 at 2:02 pm
The file names are the same. Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 11, 2014 at 2:08 pm
Welsh Corgi (12/11/2014)
The file names are the same. Thanks.
Yeah, just caught that. They are not the same according to the code you added. Your file path is missing the "_log" in the log file name.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 11, 2014 at 2:10 pm
Very Strange. I get the error but the Log File has moved.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'Staging');
name CurrentLocation state_desc
CypressStaging D:\MSSQL\Data\Staging.mdf ONLINE
CypressStaging_logL:\MSSQL\Log\Staging.ldf ONLINE
It crashed and burned when I tried to restore to the new log file location.
It is a big database.
Weird.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply