SQL server 2005 (3) 데이터베이스
USE master
GO
CREATE DATABASE MyDB01
-서버에 존재하는 모든 데이터베이스에 대한 정보를 조회한다.
EXEC sp_helpdb
-'MyDB01' 데이터베이스에 대한 상세 정보를 조회한다.
EXEC sp_helpdb MyDB01
SQL 쿼리 분석기에서 만든 엔터프라이즈 관리자에서 확인 가능
데이터 파일의 이름과 위치를 지정하여 데이터베이스를 만드는 예
USE master
GO
CREATE DATABASE MyDB02
ON
(
NAME = MyDB02_data,
FILENAME ='C:\MyDB02_data.mdf'
)
sp_helpdb MyDB02
USE master
GO
CREATE DATABASE MyDB03
ON
(
NAME = MyDB03_data,
FILENAME ='C:\MyDB03_data.mdf',
SIZE = 5
)
sp_helpdb MyDB03
GO
CREATE DATABASE MyDB04
ON
(
NAME = MyDB04_data,
FILENAME ='c:\MyDB04_data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5%
)
LOG ON
(
NAME = 'MyDB04_log',
FILENAME = 'c:\MyDB04_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
sp_helpdb MyDB03
CREATE DATABASE MyDB10
ON
(
NAME = MyDB10_data,
FILENAME ='c:\MyDB10_data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5%
),
(
NAME = 'MyDB10_data2',
FILENAME ='c:\MyDB10_data2.ndf',
SIZE = 3,
MAXSIZE = 10,
FILEGROWTH = 15%
)
LOG ON
(
NAME = 'MyDB10_log',
FILENAME = 'c:\MyDB10_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
),
(
NAME = 'MyDB10_log2',
FILENAME ='c:\MyDB10_log2.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 2MB
)
EXEC sp_helpdb MyDB10
사용자 정의 파일 그룹을 사용하여 데이터베이스를 만드는 예
USE master
GO
CREATE DATABASE MyDB06
ON PRIMARY
(
NAME = MyDB06_data,
FILENAME = 'c:\MyDB06_data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5%
),
FILEGROUP SalesGroup
(
NAME = MyDB06_data2,
FILENAME = 'c:\MyDB06_data2.ndf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 15%
),
FILEGROUP ProductGroup
(
NAME = MyDB06_data3,
FILENAME = 'c:\MyDB06_data3.ndf',
SIZE = 2,
MAXSIZE = 6,
FILEGROWTH = 5%
)
LOG ON
(
NAME = 'MyDB06_log',
FILENAME = 'c:\MyDB06_log.ldf',
SIZE = 2MB,
MAXSIZE = 10MB,
FILEGROWTH = 2MB
)
수정
GO
CREATE DATABASE MyDB07
ON
(
NAME = MyDB07_data,
FILENAME = 'c:\MyDB07_data.mdf',
SIZE = 3,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'MyDB07_log',
FILENAME = 'c:\MyDB07_log.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 1MB
)
<---- 데이터의 파일 크기를 5MB로 증가하려고 한다.
ALTER DATABASE MyDB07
MODIFY FILE (
NAME = MyDB07_data,
SIZE = 5MB)
EXEC sp_helpdb MyDB07
GO
CREATE DATABASE MyDB08
ON
(
NAME = MyDB08_data,
FILENAME = 'c:\MyDB08_data.mdf',
SIZE = 3)
EXEC sp_helpdb MyDB08
ALTER DATABASE MyDB08
MODIFY FILE (
NAME = MyDB08_data,
SIZE = 10MB)
DBCC SHRINKDATABASE (MyDB08,10)
GO
CREATE DATABASE MyDB09
ON
(
NAME = MyDB09_data,
FILENAME = 'c:\MyDB09_data.mdf',
SIZE = 3,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'MyDB09_log',
FILENAME = 'c:\MyDB09_log.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 1MB
)
EXEC sp_helpdb MyDB09
ALTER DATABASE MyDB09
MODIFY FILE (
NAME = MyDB09_data,
SIZE = 10MB)
USE MyDB09
GO
DBCC SHRINKFILE (MyDB09_data, 2)
데이터 파일을 추가 하는 방법
ALTER DATABASE MyDB09
ADD FILE
(
NAME = MyDB09_data2,
FILENAME ='c:\MyDB09_data2.ndf',
SIZE = 2MB
)
EXEC sp_helpdb MyDB09
데이터베이스의 주 파일 그룹에 테이블을 만드는 예
USE TESTDB
SELECT*FROM 고객정보
=
SELECT*FROM '서버이름'.'DB이름'.'소유자'.'테이블이름'
SELECT*FROM DB302E4.TESTDB.DBO.고객정보
SELECT*FROM TESTDB.DBO.고객정보
SELECT*FROM DBO.고객정보
SP_HELPDB TESTDB
SELECT*FROM 고객정보
---------> 이런식으로 명령어가 줄어든다...
USE MyDB09
SELECT*FROM TESTDB.DBO.고객정보
=
SELECT*FROM TESTDB..고객정보
----> ..은 '소유자'의 정보를 읽어야 하는 자리
SELECT*INTO ABC FROM TESTDB..고객정보
SELECT*FROM ABC
추가된 보조 데이터 파일을 삭제하기 위해 ALTER DATABASE 문을 사용 하는 예
ALTER DATABASE MyDB09
REMOVE FILE MyDB09_DATA2
데이터 파일에 이쓴 모든 데이터를 동일한 파일 그룹의 다른 데이터 파일로 이동하는 예
USE MyDB09
GO
DBCC SHRINKFILE('MyDB09_data2', EMPTYFILE)
파일을 옮긴 후 데이터 삭제
ALTER DATABASE MyDB09
REMOVE FILE MyDB09_DATA2
ALTER DATABASE MyDB09
MODIFY FILE(
NAME = MyDB09_data,
SIZE = 20MB
)
GO
EXEC SP_HELPDB MYDB09
NOTRUNCATE를 지정하면 해제된 공간이 운영 체제에 반환되지 않는다.
USE MYDB09
GO
DBCC SHRINKFILE (MyDB09_data, 10, NOTRUNCATE)
GO
EXEC SP_HELPDB MYDB09
TRUNCATEONLY를 지정하면 파일에서 사용되지 않는 공간을 운영 체제에 반환
USE MYDB09
GO
DBCC SHRINKFILE (MyDB09_data, 10, TRUNCATEONLY)
GO
EXEC SP_HELPDB MYDB09
DBCC SHRINKDATABASE (MyDB08,10) -> %로 크기를 줄이는 방법 (10%로 줄여라)
DBCC SHRINKFILE (MyDB09_data, 10) -> 직접 줄일 크기를 설정 하는 방법 (2MB로 줄여라)
DBCC SHRINKFILE('MyDB09_data2', EMPTYFILE) -> 해당 파일에 모든 항목을 비워라~
USE MASTER
GO
CREATE DATABASE MYDB11
ON
(
NAME = MYDB11_DATA,
FILENAME = 'C:\MYDB11_DATA.MDF',
SIZE = 3
)
ALTER DATABASE MYDB11
ADD FILE
(
NAME = MYDB11_DATA2,
FILENAME = 'C:\MYDB11_DATA2.NDF',
SIZE = 5MB
)
EXEC SP_HELPDB MYDB11
ALTER DATABASE MYDB11
REMOVE FILE MYDB11_DATA2
GO
EXEC SP_HELPDB MYDB11
로그 파일 추가 예
ALTER DATABASE MYDB11
ADD LOG FILE
(
NAME = MYDB11_LOG2,
FILENAME = 'C:\MYDB11_LOG2.LDF',
SIZE = 1MB
)
GO
EXEC SP_HELPDB MYDB11
삭제
ALTER DATABASE MYDB11
REMOVE FILE MYDB11_LOG2
GO
EXEC SP_HELPDB MYDB11
ALTER DATABASE를 이용하여 파일그룹을 추가하는 예
1. 모델 데이터 베이스와 동일한 형태의 데이터베이스 MYDB13을 만든다.
USE MASTER
GO
CREATE DATABASE MYDB13
EXEC SP_HELPDB MYDB13
2. SP_HELPFILEGROUP 시스텝 저장 프로시저를 사용하여 데이터베이스에 대한 파일 그룹 정보를 확인
USE MYDB13
GO
EXEC SP_HELPFILEGROUP
3. 그룹만들기
ALTER DATABASE MYDB13
ADD FILEGROUP SALESGROUP
GO
EXEC SP_HELPFILEGROUP
4. 파일을 만들어 그룹에 추가
ALTER DATABASE MYDB13
ADD FILE
(
NAME = MYDB13_DATA2,
FILENAME = 'C:\MYDB13_DATA2.NDF',
SIZE = 2MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
TO FILEGROUP SALESGROUP
EXEC SP_HELPFILEGROUP
EXEC SP_HELPFILEGROUP SALESGROUP
ALTER DATABASE MYDB13
MODIFY FILEGROUP SALESGROUP READONLY
ALTER DATABASE MYDB13
MODIFY FILEGROUP SALESGROUP READWRITE
파일 그룹의 이름을 변경하려면 DEFAULT 위치에 [NAME = 새그룹이름] 을 지정한다.
ALTER DATABASE MYDB13
MODIFY FILEGROUP SALESGROUP NAME = NEWGROUP
EXEC SP_HELPFILEGROUP
그룹에 포함된 데이터 파일 삭제
ALTER DATABASE MYDB13
REMOVE FILE MYDB13_DATA2
그룹삭제
USE MYDB13
GO
ALTER DATABASE MYDB13
REMOVE FILEGROUP NEWGROUP
EXEC SP_HELPNEWGROUP
EXEC SP_DBOPTION
EXEC SP_DBOPTION MYDB13
USE MASTER
GO
EXEC SP_DBOPTION 'MYDB13','READ ONLY','TRUE'
USE MASTER
GO
EXEC SP_DBOPTION 'MYDB13','READ ONLY','FALSE'
USE MASTER
GO
EXEC SP_DBOPTION 'MYDB13','OFFLINE','TRUE'
USE MASTER
GO
EXEC SP_DBOPTION 'MYDB13','OFFLINE','FALSE'
EXEC SP_DBOPTION MYDB13
데이터베이스 분리
USE MASTER
GO
EXEC SP_DETACH_DB'MYDB09','TRUE'
GO
EXEC SP_HELPDB
데이터베이스 연결
CREATE DATABASE MYDB09
ON
(
FILENAME='c:\MYDB09.MDF'
)
FOR ATTACH
USE MASTER
GO
EXEC SP_ATTACH_DB @DBNAME = 'MYDB09',
@FILENAME1 = 'C:\MYDB09.MDF',
@FILENAME2 = 'C:\MYDB09_LOG.LDF'
테이블추가
USE MYDB09
GO
CREATE TABLE TESTTABLE(AA INT)
GO
INSERT TESTTABLE VALUES(123)
SELECT*FROM TESTTABLE
데이터베이스 이름 변경
USE MASTER
GO
EXEC SP_RENAMEDB 'NEWMYDB11','MYDB11'
EXEC SP_HELPDB
USE MASTER
GO
EXEC SP_ADDLOGIN 'HONG','PASS','MASTER'
EXEC SP_ADDLOGIN 'KIM','PASS','MYDB11'
USE MYDB11
GO
EXEC SP_GRANTDBACCESS 'KIM'
GO
EXEC SP_HELPUSER
사용자는 DB 소유자가 될 수 없다.
EXEC SP_CHANGEDBOWNER 'KIM'
사용자로 지정이 되어 있지 않기 때문에 새 소유자로 저정할 수 있다.
EXEC SP_CHANGEDBOWNER 'HONG'
GO
EXEC SP_HELPUSER
사용자에서 제거를 하면 소유자로 지정할 수 있다.
USE MYDB11
GO
EXEC SP_REVOKEDBACCESS 'KIM' - 사용자를 제거한다.
GO
EXEC SP_CHANGEDBOWNER 'KIM' - 소유자로 지정한다.
GO
EXEC SP_HELPUSER
EXEC SP_CHANGEDBOWNER 'sa'
exec sp_droplogin 'KIM'
exec sp_droplogin 'HONG'
USE MASTER
GO
DROP DATABASE MYDB01,MYDB02,MYDB03,MYDB04,MYDB05,MYDB06,MYDB07,MYDB08,MYDB11