#0162–SQL Server 2012–Deprecated Features–Modify database options with sp_dboption–Msg 2812

If you have been into database administration & development for a while now, what is the first thing that comes to your mind when someone asks to modify database options like setting the database read-only, or taking it offline? Answer: The sp_dboption system stored procedure.

The typical way to modify these database options would be:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
sp_dboption 'TestDB','read only','TRUE'
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
sp_dboption 'TestDB','offline','TRUE'
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

SQL Server 2012 – Replacement – ALTER DATABASE…SET

However, starting SQL Server 2012, attempting to change the database options this way would produce the following results.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

The system could not find the system stored procedure! That’s simply because it has been deprecated! The replacement is the ALTER DATABASE statement, and the existing sp_dboption calls need to be modified as demonstrated in the script below:

--"Before" state
SELECT 'Before',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database to READ-ONLY mode
ALTER DATABASE TestDB SET READ_ONLY
GO
--"After" state
SELECT 'Post Read Only',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO
--Set the database OFFLINE
ALTER DATABASE TestDB SET OFFLINE
GO
--"After" state
SELECT 'All done',is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = 'TestDB'
GO

The sp_dboption system stored procedure was marked as “discontinued” ever since SQL Server 2005. Yet, I have seen a lot of production code this year itself that uses the sp_dboption statement. All of this code will break unless it is replaced with ALTER DATABASE…SET statement.