December 5, 2013 at 12:06 pm
Last night I needed to relocate some database log files to a drive with more space. When I attempted to bring the databses back online I got errors to the effect of "Access is denied" and "the file database_log.ldf already exists." Although the databases were no longer marked offline in SSMS, they were inaccessable.
What I figured out was that appearently if you need to relocate a log file and the database is using Simple Recovery Model, you don't need to move the Log file, as a new Log file with the specs described in the ALTER DATABASE command is created at the new location. If a file with that name already exists at that location, the SET ONLINE statement fails, even if the file is a copy of the file that existed at the old location. Once I ran SET OFFINE and moved the copied log files out of the new log folder, the SET ONLINE command worked and new log files were created.
I reread the article Moving Database Files in BOL and this was not mentioned. One of the log files was rather large, so I could have saved quite a bit of time if I knew this.
December 5, 2013 at 9:08 pm
Are you updating your experience here? then so nice of you
Regards
Durai Nagarajan
December 5, 2013 at 9:09 pm
One doubt have you tried Dettach and attach?
Regards
Durai Nagarajan
December 5, 2013 at 11:48 pm
dan-572483 (12/5/2013)
What I figured out was that appearently if you need to relocate a log file and the database is using Simple Recovery Model, you don't need to move the Log file, as a new Log file with the specs described in the ALTER DATABASE command is created at the new location.
Not true.
If you try to bring a DB online without a log, SQL can sometimes recreate the log, regardless of recovery model. This breaks the log chain if the DB was in full recovery model and is not guaranteed to succeed. If SQL cannot recreate the log file (and there are many reasons why it may not) then the DB goes into the recovery pending state and you'll either need to run an emergency mode repair (which can leave the DB transactionally and possibly even structurally inconsistent) or to restore from a full backup to get the DB back.
When moving a database, move all the files, data and log, regardless of recovery model. The log is not an optional file, it's a critical part of the database, it's what ensures the consistency and durability required of a relational database.
I reread the article Moving Database Files in BOL and this was not mentioned. One of the log files was rather large, so I could have saved quite a bit of time if I knew this.
No, it's not mentioned because it's not a supported, recommended or good practice. It's a risky thing to do, kinda russian roulette with your database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2013 at 8:26 am
Then why would my simple recovery model databases not come online with copies of the original log files in the new location?
December 6, 2013 at 8:48 am
dan-572483 (12/6/2013)
Then why would my simple recovery model databases not come online with copies of the original log files in the new location?
does the startup account of SQL server have full permissions to the new folder location you identified in the ALTER DATABASE command?
what specific error did you get, that would help teh most to diagnose the problem.
Lowell
December 6, 2013 at 10:50 am
Attached a series of messages from SQL Log. All I did was take the DB offline, run an ALTER DATABASE command to change the location of the log file, copy the log file to the new location, and set the DB online again.
EDIT: Interesting, I repeated the same steps using a simple recovery mode DB on an instance I have at home for education & test preparation. The DB came back online just fine using a copy of the LDF. The only difference was that my home instance is SQL 2008 R2 SP2 and the instance at work where the issue appeared is 2008 SP1 (not R2). Maybe there's a bug at that specific upgrade level?
December 6, 2013 at 1:27 pm
dan-572483 (12/6/2013)
Then why would my simple recovery model databases not come online with copies of the original log files in the new location?
The reason is in the error log
Operating system error 5: Access is denied.
After moving a file, always check that the SQL Service account has full permissions on both the folder and the file before bringing the DB online.
Maybe there's a bug at that specific upgrade level?
Not a bug.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2013 at 5:09 am
dan-572483 (12/5/2013)
Last night I needed to relocate some database log files to a drive with more space. When I attempted to bring the databses back online I got errors to the effect of "Access is denied" and "the file database_log.ldf already exists." Although the databases were no longer marked offline in SSMS, they were inaccessable.What I figured out was that appearently if you need to relocate a log file and the database is using Simple Recovery Model, you don't need to move the Log file, as a new Log file with the specs described in the ALTER DATABASE command is created at the new location. If a file with that name already exists at that location, the SET ONLINE statement fails, even if the file is a copy of the file that existed at the old location. Once I ran SET OFFINE and moved the copied log files out of the new log folder, the SET ONLINE command worked and new log files were created.
I reread the article Moving Database Files in BOL and this was not mentioned. One of the log files was rather large, so I could have saved quite a bit of time if I knew this.
For the future, check my guide at the following link for help on moving SQL Server database files
Moving Database Files in SQL Server[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 9, 2013 at 5:15 am
dan-572483 (12/6/2013)
Then why would my simple recovery model databases not come online with copies of the original log files in the new location?
The first thing you should have done is STOP!!!!
Query sys.master_files and check the paths SQL Server has registered for the database, it's easy to mistype, SQL may be trying to use a path\file that does not exist.
Check the permissions on the folder you have moved the files to, does the SQL Server service account have permissions here?
Only after checking this info can you proceed further
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 9, 2013 at 10:23 am
Perry,
The steps I used were pretty much what you have in your article except that I used sp_helpdb <databasename> rather than sys.master_files. Does this make a difference?
I looked at my code multiple times for typos, but didn'r find one. Also, the errors didn't indicate a failure to locate a file, but that it couldn't create the file because it already existed.
Security may be a factor as this was a new drive created because the old drive used for SQL log files was no longer large enough. But how does it have rights to create a new LDF file but not rights to connect to an existing one, copied from from the drive the database was using before I took it offline?
The SQL Service Account is NT Authority\NETWORK SERVICE. Does this account interact with local security or SQL Server differently from regular user\service accounts?
December 9, 2013 at 2:10 pm
dan-572483 (12/9/2013)
Security may be a factor as this was a new drive created because the old drive used for SQL log files was no longer large enough. But how does it have rights to create a new LDF file but not rights to connect to an existing one, copied from from the drive the database was using before I took it offline?
See my earlier comment. Security WAS the cause, the error stated outright 'OS error 5(Access is denied)". SQL Server did not have permission on the file, and there are a couple possible reasons why. Since it did not have permissions, the online failed. If you'd corrected the permissions error (ensure SQL account had full rights on file and folder) the DB would have come online.
Network Service is a low permission account, it's no different from any other machine\user really, but it is low permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply