Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATE DATABASE FOR ATTACH_REBUILD_LOG Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 8:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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?




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #654911
Posted Wednesday, February 11, 2009 10:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 6,259, Visits: 2,031
For reasons like this I always use TSQL and specify the path. No UI surprises "ever"!



* Noel
Post #655076
Posted Wednesday, February 11, 2009 10:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #655099
Posted Wednesday, February 11, 2009 11:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
I have to ask, why are you attaching a database without a log?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #655119
Posted Wednesday, February 11, 2009 12:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #655153
Posted Wednesday, February 11, 2009 1:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #655178
Posted Wednesday, February 11, 2009 1:42 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 6,259, Visits: 2,031
In all honesty I hope you are not losing data but it is ***VERY*** bad to do that!



* Noel
Post #655213
Posted Wednesday, February 11, 2009 1:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #655227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse