Cannot enable select into/bulkcopy on SQL Server 2005 database using sp_dboption

Question

I am trying to enable the select into/bulkcopy database option on a SQL Server 2005 database using the command:

EXEC sp_dboption 'mydbname', 'select into/bulkcopy', 'true'

After executing the above, running EXEC sp_dboption 'mydbname', 'select into/bulkcopy' tells me that the option is still set to OFF.

I've confirmed that my Windows login is a user in the database and that it belongs to the db_owner role. Having read the MSDN Documentation for sp_dboption, this appears to be the only prerequisite for using the procedure to change options on a database.

Are there any other steps or settings I am missing that could prevent me from being able to enable this option?

Answer 1

That procedure is deprecated. You can use

 ALTER DATABASE [mydbname] SET RECOVERY BULK_LOGGED WITH NO_WAIT

This seems to happen if your DB is currently in SIMPLE recovery model.

Looking at the sp_dboption procedure definition the relevant bit of code is

    if @alt_optvalue = 'ON'
    begin
        if databaseproperty(@dbname, 'IsTrunclog') = 1
            select @alt_optvalue = 'RECMODEL_70BACKCOMP'
        else
            select @alt_optvalue = 'BULK_LOGGED'
    end

The effect of running ALTER DATABASE [mydbname] SET RECOVERY RECMODEL_70BACKCOMP WITH NO_WAIT seems to be to set the recovery model to SIMPLE so basically it has no effect in this instance