#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

image

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

image

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.

References:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s