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.

References:

• sp_dboption - http://msdn.microsoft.com/en-us/library/ms187310(v=sql.90).aspx
• ALTER DATABASE statement - http://msdn.microsoft.com/en-us/library/bb522682.aspx

Until we meet next time,

Be courteous. Drive responsibly.