SQL 2005 까지는 Database 관리를 위하여 저장 프로시저를 이용했습니다. (sp_attach_db, sp_rename_db, sp_dboption 등). 그러나 2008부터는 이 저장 프로시저들을 사용할 수 없고, CREATE DATABASE나 ALTER DATABASE를 이용해야 합니다. 개인적인 의견으로는 저장 프로시저를 사용하는 것보다 훨씬 직관적이고 좋은것 같습니다.
아래 내용은 mssqltips에서 퍼온 내용입니다. 영문이지만 주요 내용을 보는데는 큰 불편이 없을 것이라 생각됩니다. (번역하기기 귀찮아서요 ^^)
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 |
|
참고자료 : http://www.mssqltips.com/tip.asp?tip=1373
'DB > MS-SQL' 카테고리의 다른 글
| 동적으로 원하는 개수만큼 행을 다루기(TOP, ROWCOUNT) (0) | 2008.07.10 |
|---|---|
| 임시테이블의 OBJECT_ID 얻기 (0) | 2008.06.10 |
| 복구 모델(Recovery Model) (0) | 2008.05.14 |
| SELECT @@VERSION 의 빌드(BUILD) 번호로 서비스팩 및 보안패치 현황 파악하기. (0) | 2008.03.12 |
| 유용한 SQL 도구 - SQL Prompt (0) | 2008.02.26 |