By: Jeremy Kadlec | Updated: 2007-11-12 | Comments | Related: > 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
- Keep an eye and ear out for the SQL Server deprecated features. These changes will make a difference to your coding and scripts moving forward. Microsoft say's they are going to be serious about deprecated features this time, so this will impact your future systems. Luckily the migration path is straight forward, but you will need to take action.
- Check out these tips related to SQL Server deprecated features:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-11-12
