February 11, 2009 at 8:34 am
Have a new SQL server instance we've spun up.
So I moved a few databases to it. Via detach, attach.
Normally when I attach a database without the log file if I remove the log file from the screen (using SSMS) or use ATTACH_REBUILD_LOG, the log file will be created in the Log directory as specified in server properties.
However, on my newest server it creates the logs in the data directories (wrong disk spindles).
If I create a new database, the logs are created in the right place, so I'm a bit stumped...
Any ideas?
February 11, 2009 at 10:35 am
For reasons like this I always use TSQL and specify the path. No UI surprises "ever"!
* Noel
February 11, 2009 at 10:52 am
That's great, unless you want to recreate the log.
CREATE DATABASE [name] ON (FILENAME = [path to mdf]) LOG ON (FILENAME = [path to ldf]) FOR ATTACH REBUILD LOG
will give an error if the log file isn't in place. When you're dealing with limited bandwidth, and sever time constraints, leaving the log files behind if you get a clean detach can be the best choice.
CREATE DATABASE [name] ON (FILENAME = [path to mdf]) FOR ATTACH_REBUILD_LOG
should create the log file in the default log store for the server, but it's putting it where the database is.
The last 4000 databases I moved this way put the logs in the right places.
February 11, 2009 at 11:09 am
I have to ask, why are you attaching a database without a log?
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
February 11, 2009 at 12:01 pm
Because that's what I was told to make happen. When I had to move 4000 databases, it worked great... this time I have less time to move about 200 of them, and rather than keep the existing logs, I'd like start these databases with fresh new ones that I can keep under control because I wrote the backup maintenance plans this time.
February 11, 2009 at 1:00 pm
You do realise that by discarding the logs you are risking the attached database been transactionally inconsistent or even suspect?
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
February 11, 2009 at 1:42 pm
In all honesty I hope you are not losing data but it is ***VERY*** bad to do that!
* Noel
February 11, 2009 at 1:57 pm
We're not losing data doing this.
If the database is in a transactionally inconsistent state, sql won't attach it and we retrieve the logs as necessary. Out of the last 4000 databases we did this to, this meant we had to copy 3 log files and attach 3 databases manually.
I'm not doing this in emergency mode or forcing the attach, if there's an error on the attach we retrieve the logs, this is an expediency measure, nothing more.
We also take full backups before we do this task. In case we need them. So far we're 4002 out of 4002.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply