January 22, 2015 at 8:44 am
So I'm brushing up on my T-SQL and came across the ever forgettable "FOR ATTACH_REBUILD_LOG" option for the CREATE DATABASE statement. I have never used this option and it occurs to me to wonder, in what real life circumstance would someone honestly want to use this?
Has anyone had to use it? Would anyone actually recommend using it? What are your thoughts on this option?
To me, it seems to be a "last resort" kind of option (burning bridges, etc.). I think that's why I keep forgetting it exists.
January 22, 2015 at 11:02 am
I've done this when creating AdventureWorks I think, where the ldf is absent.
Having said that, I see no other useful purpose or reason. Attaching or creating a database without a Log does not make sense to me. Unless! ... the DB was shut down cleanly before the ldf file removed. But why someone would remove the ldf file anyway? Besides an "accidental" deletion.
The other situation is in disaster recovery scenarios, but that would be with ATTACH, not CREATE and there is a risk of data loss or introduce integrity issues.
January 23, 2015 at 8:51 am
I had a client who took a database offline and deleted the transaction log file for one of their production databases (someone recommended they do this, and I can't recall the reason). I believe for them creating the database with the FOR ATTACH_REBUILD_LOG option worked fine. If the database was shut down cleanly then this usually works, if it wasn't then you have to resort to the emergancy-mode repair. Paul Randal has a good article on this.
January 26, 2015 at 5:35 am
brendan woulfe (1/23/2015)
I had a client who took a database offline and deleted the transaction log file for one of their production databases (someone recommended they do this, and I can't recall the reason). I believe for them creating the database with the FOR ATTACH_REBUILD_LOG option worked fine. If the database was shut down cleanly then this usually works, if it wasn't then you have to resort to the emergancy-mode repair. Paul Randal has a good article on this.
Ouch (re: the client).
I do remember when I first started doing SQL (back in the 2000 days) that I stumbled across an article or something claiming transaction logs were recreated when the services started back up IF they had been deleted. ISTR the author claiming it wasn't a big deal. But I later decided he was conflating the tempdb recreation at start up with database transaction logs because who would want to lose their uncommitted transactions by deleting their log files?
I'm not wrong, am I, that you can shut down a database cleanly and still have uncommitted transactions?
January 27, 2015 at 1:02 pm
Only time I've used this is when we have the mdf, but not the ldf. This is almost always because someone never set up backups and tried to move a database to another machine.
WRT your question. It depends on what you mean by clean. You can shut down a database and have uncommitted transactions, and it should close connections and rollback the transaction. However potentially, I believe, this might commit log records, but not db records. So there could still be roll back on restart.
However, my suspicion is that a shutdown command to SQL Server always does the roll back in the data before it closes down.
January 28, 2015 at 8:41 am
Thanks, everyone for your input.
January 28, 2015 at 9:02 am
I've used it actuallt 2 months ago when i occured a log file corruption, long story short, this caused the database to go in suspect state, the database was in simple recovery model, i was only a bit worried that there are some transactions not checked in, but if so i wouldnt be able to use this option. Luckly i was able to take the mdf file reattach it with new log and everything seemed to be working.
January 28, 2015 at 9:22 am
Emil Bialobrzeski (1/28/2015)
I've used it actuallt 2 months ago when i occured a log file corruption, long story short, this caused the database to go in suspect state, the database was in simple recovery model, i was only a bit worried that there are some transactions not checked in, but if so i wouldnt be able to use this option. Luckly i was able to take the mdf file reattach it with new log and everything seemed to be working.
Was using this your first response or did you do other things before using it? (I'd like to know details if you care to share.)
How much data did you lose in the end?
January 28, 2015 at 10:35 am
[Url]http://www.sqlservercentral.com/Forums/Topic1639247-1550-1.aspx[/url]
You can find all the details in this topic. I think i still have the corrupted log with mdf saved somewhere. I wrote that i could fix the issue in 2 ways.
There was no data loss
January 29, 2015 at 1:59 pm
I remember that SQL 2000 trick about stopping the service and deleting the log file. Also found someone stopping the service and making zip of the mdf files as a fast way to do backups. :w00t: Bad practice! Bad, bad, bad practice. [Rolled newspaper across the nose kind of bad.]
The create for attach is a recovery practice. Your typical migration practice is to restore from a backup.
ATBCharles Kincaid
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply