Post reply

How does setting databaseproperty 'isbulkcopy' to 'TRUE' work in simple recovery model

  • June 8, 2011 at 10:05 am

    #251468

    Hello,

    What effect does setting databaseproperty 'isbulkcopy' to 'TRUE' have on a database in simple recovery model in terms of logging. Can this property be set using ALTER DATABASE command.

    Thanks,

    P

  • June 8, 2011 at 10:55 am

    #1335820

    As per Books Online:

    select into/bulkcopy

    Starting with Microsoft SQL Server 2000, if the recovery model of the database is currently set to FULL, using the select into/bulkcopy option resets the recovery model to BULK_LOGGED. The proper way to change the recovery model is by using the SET RECOVERY clause of the ALTER DATABASE statement.

    I suspect it will be ignored on Simple Recovery (as that already allows minimally logged operations). It's trivial to test (and left as an exercise to the reader)

    That option should not be used any longer (it's set with sp_dboption). Rather specify the recovery model with ALTER 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

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

  • June 13, 2011 at 8:41 am

    #1337791

    Thank you for the reply.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply