Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Deprecated SQL Server Commands


By:   |   Read Comments   |   Related Tips: More > Deprecated Features


Share your SQL Server knowledge and make some money too!


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 Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools