December 21, 2006 at 9:33 pm
From the BOL:
"in SQL Server 2005, using select into/bulkcopy causes the recovery model to be reset to bulk-logged."
Either this is not the case or I am reading something wrong. I issued a SELECT INTO statement (I was not using a temp table as the INTO table) but the database recovery model stayed at SIMPLE. I changed the recovery model to FULL and tried the query again and the recovery model stayed at FULL. Did I miss something?
December 21, 2006 at 10:18 pm
If you are running SELECT INTO statement to populate the table it will not change the recovery model...
If you change the db option to "SELECT INTO/BULKCOPY" usigng alter database statement or sp_dboption then the recovery model changes...
MohammedU
Microsoft SQL Server MVP
December 21, 2006 at 11:47 pm
ok, that's what I read it to say so my misunderstanding. However, another interesting thing is that I ran this statement:
sp_dboption 'MyDatabase', 'select into/bulkcopy', 'TRUE'
and it didn't change to bulk logged. Any idea why?
December 22, 2006 at 12:31 am
When the recovery model is SIMPLE if you enable SELECT INTO... it will not change the recovery model... it changes the only option...
where as when the recovery model FULL if you enable SELECT INTO... it will change the recovery model to BULK_LOGGED....
Read BOL for more details..
MohammedU
Microsoft SQL Server MVP
December 22, 2006 at 2:42 pm
I test every scenario and it never changed the recovery model to bulk logged. It may have something to do with the fact that I can't set "select int/bulkcopy" using sp_dboption.
Not that it should make any difference but I'm running the developer edition of SS2005.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply