Deprecated SQL Server Commands


By:   |   Updated: 2007-11-12   |   Comments   |   Related: More > Deprecated Features


Problem
It is common in many SQL Server database management scripts to rename databases, detach and attach databases, change the database status, etc.  With all of the changes related to deprecated SQL Server features, are any of these commands (sp_attach_db,  sp_renamedb, sp_dboption, etc.) marked for replacement in SQL Server 2008?  If so, what are the corresponding commands that I need to start using in order to not re-write my code in the future?  What is the new coding standard?

Solution
Yes - These commands are marked as deprecated features and your scripts should be changed to reflect the new syntax.  These commands and others should be on your radar screen since Microsoft has indicated that the features will ultimately be deprecated. 

Migrate sp_attach_db syntax to CREATE DATABASE

The sp_attach_db is a command to attach a database.  This is one option for upgrading a database from SQL Server 2000 to 2005 or used in move databases from one SQL Server to another.  Here is example code:

Deprecated Syntax
EXEC sp_attach_db @dbname = 'YourDatabase',
@filename1 = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\YourDatabase.mdf',
@filename2 = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\YourDatabase_log.ldf'
GO
 
Additional Information

Recommended Syntax

CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG } [;]
GO
 

Additional Information

Incidentally the system stored procedure sp_detach_db according to the current version of SQL Server 2008 Books Online appears to remain the command to detach a database although the sp_attach_db database is marked as deprecated.

Migrate sp_renamedb syntax to ALTER DATABASE

Another stored procedure that is commonly used as servers are migrated or databases are moved is the sp_renamedb stored procedure.  This stored procedure is being replaced by the ALTER DATABASE statement as shown below:

Deprecated Syntax
EXEC sp_renamedb '<old_name>', '<new_name>'
GO
 
Additional Information

Recommended Syntax

ALTER DATABASE <old_name>
Modify Name = <new_name>
GO
 

Additional Information

Migrate sp_dboption syntax to ALTER DATABASE

Since we are covering a number of core database management tasks, the last deprecated item that needs to be addressed is the sp_dboption which is commonly used for setting a database to read only or offline.  Although SQL Server 2000 Books Online recommends using ALTER DATABASE commands to complete these tasks, breaking old habits can be hard, so with SQL Server 2008, make sure ALTER DATABASE is used in these capacities:

Deprecated Syntax
USE master
EXEC sp_dboption 'YourDatabase', 'read only', 'TRUE'
GO
USE master
EXEC sp_dboption 'YourDatabase', 'offline', 'TRUE'
GO
 
Additional Information

Recommended Syntax

ALTER DATABASE <YourDatabase>
SET READ_ONLY
GO
ALTER DATABASE <YourDatabase>
SET OFFLINE
GO

Additional Information

Next Steps

 



Last Updated: 2007-11-12


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Deprecated Commands in SQL Server 2005

Identifying Deprecated SQL Server Code with Profiler

Identify Deprecated SQL Server Code with Extended Events

Identify deprecated SQL Server code with a server side trace








get free sql tips
agree to terms


Learn more about SQL Server tools