레이블이 mssql인 게시물을 표시합니다. 모든 게시물 표시
레이블이 mssql인 게시물을 표시합니다. 모든 게시물 표시

2017년 11월 24일 금요일

MS-SQL 테이블 설명 보기

테이블 설명보는법

select *
from V_TABLE_PROTERTIES or V_COULMN_PROTERTIES
where TAB like 'TABLE명'
order by TAB asc

2017년 11월 9일 목요일

2017년 11월 3일 금요일

MS-SQL 버전확인 쿼리 (스크랩)



-- 서비스팩 확인
select SERVERPROPERTY('productlevel')

-- 사용중인 SQL 빌드넘버와 서비스팩 확인
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

-- 사용중인 SQL종류와 빌드넘버
select @@version

-- 아래의 두개는 똑같은 결과를 보여준다.
EXEC sp_server_info

EXEC master..xp_msver

MS-SQL 날짜계산 함수 (스크랩)

(작성자링크)


dateadd
-- 월의 마지막 날
select dateadd(month,1,getdate())-day(getdate())
-- 월의 첫째날
select dateadd(day,-(day(getdate()-1)), getdate())
-- 월의 13개월전 첫째날
select dateadd(month,-12,getdate())-(day(getdate())-1)

-- 1일 더하기
select dateadd(day,1,getdate())
-- 1월 더하기
select dateadd(month,1,getdate())
-- 1년 더하기
select dateadd(year,1,getdate())


datediff
datediff( 시간단위구분자, 시작시간, 종료시간 )

getdate()+1 은 현재시간에서 1일을 더하는 함수이다.

현재 시간이 2012-12-20 11:50:11 이면, getdate()+1 은 2012-12-21 11:50:11 이 된다

각각 분단위, 초단위, 시간단위, 일단위, 월단위, 년단위로 구한다.

select DATEDIFF ( mi , getdate() , getdate()+1 ) ==> 1440 분 (1일)

select DATEDIFF ( s , getdate() , getdate()+1 ) ==> 86400 초 (1일)

select DATEDIFF ( hour , getdate() , getdate()+1 ) ==> 24 시간 (1일)

select DATEDIFF ( day , getdate() , getdate()+1 ) ==> 1 일 (1일)

select DATEDIFF ( month , getdate() , getdate()+31 ) ==> 1 개월 (31일)

select DATEDIFF ( year , getdate() , getdate()+730 ) ==> 2 년 (730일)

2017년 10월 19일 목요일

MS-SQL 복사전용 백업 쿼리

쿼리문

BACKUP DATABASE [DBNAME] [경로] WITH COPY_ONLY

예문

BACKUP DATABASE JUNGWOOK DISK=N'D:\JUNGWOOK.BAK' WITH COPY_ONLY


LOG 백업시에는 DATABASE를 LOG로 바꿔 주면 된다.

2015년 3월 13일 금요일

MS-SQL 2008 R2 에서 서버인증 변경 후 로그인이 안되는 경우

MS-SQL 2008R2 에서 설치시 Windows 인증 모드로만 설치 했었어.

그리고 그 후에 SQL Server 인증도 필요하게 되서 '서버 속성' - '보안' - '서버 인증' 에서 'SQL Server 및 Windows 인증 모드로' 변경을 했어

그런데 SQL Server 계정을 추가 하고 접속을 시도 했는데 안되는거야

이럴때 SQL Server 서비스를 재시작 해주면 된다.

2014년 1월 9일 목요일

SSIS 패키지 실행 로그를 기록하지 않게 해보자. EVENT ID : 12289 (SQLISPackage100)

MS-SQL 에서 유지관리작업을 실행한 기록이 윈도우 이벤트 로그에 기록이 되는데, 자주 실행하는 작업일 경우 그 기록이 하루에도 어마어마 하게 많을 것이고, 그로 인해 Application (응용프로그램) 이벤트 로그를 정상적으로 확인 할 수 없을 것이다.

이럴 경우 SSIS 실행 기록을 이벤트로그에 기록하지 않게 해야 하는데
그 방법은 아래와 같다..

HKEY_LOCAL_MACHINE\SOFTWARE\Micosoft\Microsoft SQL Server\100\SSIS의
REG_DWORD "LogPackageExecutionToEventlog" 값을 "0"으로 변경함

링크1 (SSIS 로그를 응용프로그램 로그와 분리하여 기록하도록 하는 방법)
링크2 (REGISTRY 값 변경하여 기록 하지 못하도록 함) <- 내가 사용하는 방법

2013년 11월 8일 금요일

MS-SQL 에서 GETDATE()를 STUFF 할 경우 기본값으로 변경됨

백업 쿼리 만들다 발견한건데..

MS-SQL에서 현재 날짜와 시간을 2013-11-01 00:00:00이란 값으로 STUFF를 사용해 임의로 DAY와 시간을 바꾸고자 SELECT STUFF(GETDATE(),9,11,'01 00:00:00') 할경우 yyyy-MM-01 00:00:00 으로 출력 될 꺼라고 생각하는데.. 01 MM yyyy hh:mm:ss 로 출력되더라..
원인은 모르겠지만 STUFF를 할 경우 GETDATE값이 표준 형식으로 바뀌는 것 같어
이럴 경우에는 STUFF(CONVERT(CHAR(19), CURRENT_TIMESTAMP,20),9,11,'01 00:00:00')
로 쿼리를 날려야 원하는 출력값을 얻을 수 있다..

요약
1. STUFF를 이용하여 날짜 일부를 강제로 바꿀경우
2. GETDATE가 아닌 CURRENT_TIMESTAMP와 CONVERT를 사용하여야 한다.


2013년 10월 24일 목요일

MS-SQL "유지 보수 계획" 의 작업기록(로그)가 저장되어 있는 DB 및 TABLE

MS-SQL "유지 보수 계획" 의 작업기록(로그)가 저장되어 있는 DB 및 TABLE는 아래와 같다..
JOIN 해서 사용하면 된다.

select * from msdb.dbo.sysmaintplan_plans
select * from msdb.dbo.sysmaintplan_subplans
select * from  msdb.dbo.sysmaintplan_log
select * from msdb.dbo.sysmaintplan_logdetail

이제 이 로그도 데이터베이스에 별도로 입력할 수 있겠구나~~ 에헤라~~ 디어~~
아..그냥 땡겨서 써도 되겠다..

2013년 5월 28일 화요일

MS-SQL 테이블 별 용량 조회 쿼리

퍼왔다.
퍼온 링크 - http://ariswear.com/

-- 테이블별 사용 용량
SELECT table_name = convert(varchar(30), min(o.name))
 , table_size = ltrim(str(sum(cast(reserved as bigint)) * 8192 / 1024.,15,0) + 'KB')
FROM sysindexes i
  INNER JOIN
  sysobjects o
  ON (o.id = i.id)
WHERE i.indid IN (0, 1, 255)
AND  o.xtype = 'U'
GROUP BY i.id
-- 용량별 소팅
SELECT table_name = convert(varchar(30), min(o.name))
 , table_size = convert(int, ltrim(str(sum(cast(reserved as bigint)) * 8192 / 1024., 15, 0))), UNIT = 'KB'
FROM sysindexes i
  INNER JOIN
  sysobjects o
  ON (o.id = i.id)
WHERE i.indid IN (0, 1, 255)
AND  o.xtype = 'U'
GROUP BY i.id
ORDER BY table_size DESC
-- 테이블별 Row 수
SELECT o.name
 , i.rows
FROM sysindexes i
  INNER JOIN
  sysobjects o
  ON i.id = o.id
WHERE i.indid < 2
AND  o.xtype = 'U'
ORDER BY i.id


2013년 4월 5일 금요일

MS-SQL DB 오프라인이 오래 걸릴 때.

MS-SQL 에서 DB를 OFFLINE 할 때 오래 걸릴 때가 있다...

이럴땐 일단 아직 DB에 접속해 있는 분당(?)들을 제거 해야 되..

그걸 확인하는 방법은

일단 master DB에서 실행해야되고

누가 접속 해있는지 확인하는 sp_who 로 확인 해당 DB에 접속 해 있는 유저를 찾고 유저으 SIPD 를 찾은 후 죽여 버리면 됨

죽일 때는 kill [SPID] 임


관련 링크  :  http://blog.naver.com/PostView.nhn?blogId=smilemoon602&logNo=90104902665&redirect=Dlog&widgetTypeCall=true


2013년 3월 8일 금요일

MS-SQL 에서 스키마 때문에 계정삭제 못하는 경우

MS-SQL에서 "데이터베이스 보안주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다." 라고 에러 발생하면서 사용자 계정을 삭제 하지 못할 경우

보안 >> 스키마 >> 사용자와 연결된 스키마 삭제 한뒤 계정을 삭제 하면 된다.


2013년 2월 22일 금요일

MS-SQL 에러 18456



날짜 2013-02-22 16:45:50
로그 SQL Server (현재 - 2013-02-22 16:00:00)

원본 로그온

메시지
Login failed for user '[시스템명]\[계정]'. 원인: 명시적으로 지정된 데이터베이스를 열지 못했습니다. [클라이언트: <local machine>



이게 말이야......
이벤트 로그에 MSSQL 알림 메시지가 10초마다 뜨는거야......--;;;
알고봤더니 쌓이고  쌓인게 2GB? 헐~
저 위에 메시지는 mssql 이벤트 로그야 로그가 너무 많이 쌓여서 윈도우 이벤트로그는 지워 버렸어..
자 로그를 함 보자고~
클라이언트 : <xxx> 여기는 어디서 접속을 할려고 했느냐고 알려주겠지..
근데 어라 윈도우 게정인데 안된다네...?
데이터베이스를 열수 없다니.......
접속할려는 데이터베이스가 사라졌나??? 하고 원인을 찾았어..
근데, 그냥 찾을 리가 있겠냐
구글링 해보니~ sql server profiler 돌려서 알아보라고 하더라고 <여기: 외국인 꺼>
그래서 하라는대로 프로파일러 새 추적을 만들고 이벤트 선택 항목에서 아래에 놈들만 추가했지

  • Audit Login Failed (under Security Audit)
  • User Error Messages (under Errors & Warnings)
  • Errorlog (under Errors & Warnings). This is optional.
어!! 접속할려는 DB이름은 나왔어!! 그래서 sql 구성관리자도 확인하고 DB도 확인했어..
근데 계속  뜬다~?  그래서 프로파일러 이벤트 선택 항목에 좀 더 추가 핼볼까 해서 eventlog (under errors & warnings) 만 추가했더니 주기적으로 먼가 돌더만 그래서 봤더니 sql agent 작업이 걸려져 있더라 그래서 작업 삭제하고 서버 개체 > 연결된 서버 > 없어진 DB 삭제 했더니
로그가 더이상 안뜨더라....

1. 저 위에 메시지 발생하면
2. 프로파일러 돌려서 접속할려는 DB가 있는지 만약 없다면 없어진 DB와 관련된 agent 작업이나 유지보수 계획이 걸려 있는지 확인해 봐라
3. 프로파일러 상당히 유용하다 


2013년 2월 13일 수요일

MS-SQL 암호화 해보자. - 2


/*
    MS-SQL 2005 이상 대칭키 사용 방법
    대칭키(Symetric)와 인증키(Certificate)를 OPEN하고 패스워드(Password)를 입력한다.
    ※옵션(Option) : 패스워드는 인증키 생성시 입력 했으면 입력 해야함
    KEY_GUID의 값을 넣기 위한 변수 선언 및 Symetric Key의 name 값으로 KEY_GUID를 가져와야한다.
*/
-- 1. Start Session Open
/*
    이거 세트로 해야되고 GUID 값 받는거 선언해야하는거 이해하느라 맨땅에 하루 반나절 해딩했다...
    해딩은아니고 이곳 저곳 찾아보다가 막 해보다가 알게되었어...
*/
USE [DB_NAME];
OPEN SYMMETRIC KEY [SYMMETRIC_KEY_NAME]
DECRYPTION BY CERTIFICATE [CERTIFICATE_KEY_NAME]
WITH PASSWORD = '[CERTIFICATE_PASSWORD]'
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = (SELECT KEY_GUID FROM SYS.symmetric_keys WHERE name='[SYMMETRIC_KEY_NAME]')
-- 1. End Session Close 여기 까지가 암호화/복호화 준비 과정 [이 구문은 세트(SET) 라고 보면 된다.]

-- 2. Start ENCRYPTION
/*
    수정(UPDATE)를 한다고 치자. 물론 그전에 암호화 해서 넣을 컬럼(COLUMN)을 만들어 줘야겠지?
    이놈은 암호화기 때문에 문자가 엄청 길어질꺼잖아 그래서 형식(TYPE)을 VARBINARY를 해서 만들어 줘야해
    여기서 잘봐야 할 건 저기 ENCRYPTBYKEY 구문이야, 저걸 해줘야 당근 암호화가 되겠지?
    삽입/입력(INSERT) 도 마찬가지로 ENCRYPTBYKEY를 해줘야겠지 대칭키는 ENCRYPTBYKEY 를 써줘야하고 다른 암호화 방식은
    ENCRYPTBYPHASE(?) 맞나 암튼 그런걸로 해줘야 해.. 일단 대칭키 방법이 가장 빠르다니깐 뭐 이걸 많이 쓰는 것 같어
 
*/
UPDATE  TEST_TABLE  SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, NORMAL_COLUMN)
--또는
UPDATE  TEST_TABLE  SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, '멍청이')
-- 2. End ENCRYPTION (여기 까지가 암호화)

-- 3. Start DECRYPTION
/*
    복호시 CAST나 CONVERT 를 사용해서 TYPE 변환을 해줘야 해. WHY? 그렇게 하지 않으면 이상한 문자로 깨져 나와..
    그리고 복호시에는 데이터 타입이 문자(String)이면 NVARCHAR, NCHAR, 숫자면 VARCHAR, CHAR로 해야되.
*/
SELECT col1, CONVERT(VARCHAR(MAX),DECRYPTBYKEY(col2)) FROM TEST_TABLE
SELECT col1, CAST(DECRYPTBYKEY(col2) AS VARCHAR(MAX)) FROM TEST_TABLE
-- 3. End SECRYPTION (여기 가지가 복호화)

-- 4. Start Session Close
/*
    그리고 다했으면 세션(SESSION)을 받아줘야 해. 무슨 세션이냐고? 당근 암호화 세션이지..
*/
CLOSE SYMMETRIC KEY [SYMMETRIC_KEY_NAME]

-- 4. End Seeion Close


MS-SQL 암호화 해보자. - 1

빨간 글씨는 수정한 부분임.

/*
    MS-SQL 2005 부터 지원되는 자체 암호화 기능 중 대칭키를 사용해 보도록 하자.
    다른 말론 컬럼(COLUMN) 암호화...
    먼저 마스터(데이터베이스 마스터) 키를 생성하고, 그 다음 대칭키를 사용하기 위해 인증서를 생성하고 다음은 대칭키를 생성한다. 자세한 설명은 그림봐라..
*/
암호화 하는 방법은 4가지야... 개콘 네가지..
1) 비대칭키를 사용하여 암호화 : EncryptByAsmKey()
2) 인증서를 사용하여 암호화 : EncryptByCert()
3) 대칭키를 사용하여 암호화 : EncryptByKey()
4) 패스워드를 사용하여 암호화 : EncryptByPassPharse()
※ 패스워드를 사용한 암호화는 소스에 하드코딩 됨으로 보안이 쥐약이겠지 그래서 패스워드 방식 빼고 사용하면 되겠지?

근데 왜 대칭키를 쓰냐고 물으신다면 비대칭키, 인증서, 대칭키 방법중 가장 암호화/복호화가 빠르니깐 쓴다.. 데이터가 졸라 많은데 암호화 해봐라...속 터지지...
당근 빠르면 암호화 강도는 대칭키나 인증서에 비해 약하겠지?
그리하야... 데이터는 대칭키로.. 대칭키는 인증서로 암호화를 한다고 한다.

자~~ 이정도면 대칭키를 만들어보자.
백문불여일행...

순서를 이래.. 마스터키 생성/백업 > 인증서 생성/백업 > 대칭키 생성/백업

자~~ 따라오너라~




1. 마스터키 생성 / 백업

-- 마스터 키 생성
-- 만약 마스터 키가 기존 것이 있다면 지워야한다 */
-- 지우는건 아래 쿼리로 지운다.
USE [DB_NAME]
go
DROP MASTER KEY
-- 여기까지가 지우는 마스터키 지우는 쿼리
USE [DB_NAME]
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '[MASTER_KEY_PASSWORD]' -- 마스터키 생성 패스워드

/* 마스터 키 백업 */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MASTER_KEY_PASSWORD'; -- 마스터키 생성시 사용한 패스워드
BACKUP MASTER KEY TO FILE = 'D:\CERTKEYS\MASTER_BACKUP_KEY' --마스터 키를 백업 할 경로 (미리 디렉토리를 생성해 놔야 한다)
ENCRYPTION BY PASSWORD = 'MASTER_BACKUP_KEY_PASSWORD' -- 마스터키 백업 패스워드

/* 마스터 키 조회 */
SELECT * FROM [DB_NAME].sys.symmetric_keys




2. 인증서 생성 / 백업

/* 인증서 생성 */
/* 만료일자(expiry_date) 지정하지 않으면 기본 값 (1년)으로 지정된다... 그래서 그냥 듬뿍 인심 써서 2050년 으로...
*/

CREATE CERTIFICATE [CERTIFICATE_NAME] -- 인증서 이름
ENCRYPTION BY PASSWORD = '[PASSWORD]' -- 인증서를 생성하는 패스워드
WITH SUBJECT = '[SUBJECT]'  --이게 무슨인증서요~ 하는 약간 디테일한 이름 지어줌
, EXPIRY_DATE = '12/31/2050' -- 만료일자 지정

/* 인증서 백업 */
BACKUP CERTIFICATE [CERTIFICATE_NAME] TO FILE = 'D:\CERTKEYS\CERTKEYBACKUP' --인증서 백업 경로 (미리 디렉토리를 생성해놔야 한다.)
WITH PRIVATE KEY (
FILE = 'D:\CERTKEYS\PRIVATEKEY' -- 개인키
, ENCRYPTION BY PASSWORD = '[PRIVATE_BACKUP_KEY_PASSWORD]' --개인키 암호화  패스워드
, DECRYPTION BY PASSWORD = '[PRIVATE_RESTORE_KEY_PASSWORD]' -- 개인키 암호 해독용 패스워드
)
/* 인증서 조회 */
SELECT * FROM [DB_NAME].sys.certificates




3. 대칭키 생성 / 백업

/* 대칭키 생성 */
CREATE SYMMETRIC KEY [SYMMETRIC_KEY_NAME] -- 대칭키 이름
WITH ALGORITHM = AES_256 -- 암호 알고리즘이다. 강하면 강할수록 조타~
, IDENTITY_VALUE = 'DATA ENCRYPTION KEY 2013/02/12' -- 대칭키 다시 만들때 사용하는 데이터
, KEY_SOURCE = 'ACCOUNT DATABASE SYMMETRIC KEY' -- 대칭키 다시 만들때 사용하는 데이터
ENCRYPTION BY CERTIFICATE [CERTIFICATE_NAME]

/* 대칭키 조회 */
SELECT * FROM [DB_NAME].sys.symmetric_keys

/*
    대칭키는 백업과 복원이 없어..그래서 query문장을 보관해야해... 안그럼 다른 DBMS에 복사한다거나 복구 할 때 복호화를 할 수 없을 수가 있어.
    그래서 query문을 그대로 보관해야해 잘... 특히 IDENTITY_VALUE 나 KEY_SOURCE 항목이 중요해 이게 대칭키를 구분해주는 열할을 하는가봐 난 자세히는 몰라
    직접 해보고 내용을 추가할 께
*/


----- 2편 

관련링크
http://blog.taks.pe.kr/20
http://blog.naver.com/lovebabyyh/80061399950
http://purumae.tistory.com/94
MS SQL 대칭키 암호화 복호화 - http://social.technet.microsoft.com/Forums/ko-KR/sqlserverko/thread/b1cdf310-bd20-4a7f-a666-8336b50d0fe7/

2013년 2월 12일 화요일

MS-SQL Database, Logical Name 변경하기

MS-SQL 데이터베이스의 Logical Name 의 이름을 변경 하고자 할 때는 아래와 같이 변경하면 된다.
DB 복원이나 복사 후 변경할 때 사용.

/* MDF 변경 */
alter database MyDatabase modify file (NAME = 'oldMdfName', NEWNAME = 'NewMdfNAme')
                        [DB 명]                                          [현재 mdf 파일 logical name]  [새로운 logical name]
/* LDF 변경 */
alter database MyDatabase modify file (NAME = 'oldLdfName', NEWNAME = 'NewLdfNAme')
                        [DB 명]                                          [현재 ldf 파일 logical name]  [새로운 logical name]

2013년 2월 5일 화요일

MS-SQL 2008 구성관리자 클라이언트 구성 백업 및 복구

mssql의 구성관리자라는게 있어 여기에 DB에 접속하는 IP랑 포트 정보를 입력해 놓고 alias 즉, 별칭을 부여해서 접속 할 때 마다 일일이 IP랑 포트를 입력하는 수고를 덜게 할 수 있거든. 참으로 편리하지.
근데 이걸 열심히 쓰다보면 한두개씩 설정해 놓은 값이 늘어나겠지.
근데 내 PC나 서버가 뻑이나서 다시 설정해야하는데 저걸 언제 일일이 다 입력하냐고..
그래서 내가 쉽게 백업하고 복구 하는 방법을 알려 줄께.
방법은 간단해. 레지스트리 알지? 이것도 레지스트리에 값을 저장 하고 있거든.
그래서 이걸 그냥 내보내기 해서 받으면되. 그럼 백업이 되겠지?
복구는 어떻게 하냐고? 그냥 백업받은 파일을 더블클릭만 하면되.
이게 구성관리자 내용이 있는 위치야.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

2013년 1월 28일 월요일

MS-SQL 2008 트랜젝션로그 줄이기(정리, 축소하기)

ms sql 를 운영하다 보면 ldf 파일이 mdf 파일 보다 훨~~씬 커지는 경우가 생길꺼야..
ldf 가 transaction log 파일의 확장자거든.. 자세한 설명은 나중에 적어줄께
일단 급한건 이걸 줄이는 거잖아
이게 근데 인터넷에 흔하게 떠도는 dbcc shrinkfile 명령어로는 잘 될지 않을 때가 있어
그 때는 db 복구 형식을 단순으로 바꿔준 후 shrickfile 명령어로 로그를 줄여주고 다시 전체 모드로 바꿔주면되.. 어때 쉽지?

// log 파일을 줄일 db로 들어가
use [log 파일을 줄일 db name]
go
// 복구모드를 단순(simple)로 바꿔줘 실제로는 괄호[]도 같이 적으면 안되는거 알지?
// 이때 DB상태에 따라 좀 오래 걸릴 수도 있어
alter datebase [dbname]
set recovery [simple]

// 로그파일을 줄여 log 파일의 논리 이름을 적어줘, 1은 1MB로 줄이라는거야
dbcc shrinkfile('[log 파일의 논리 이름]', 1)

// 다시 복구 모드를 전체(full)로 바꿔줘 원래대로 하라는거지
alter database [dbname]
set recovery [full]



이상 끝이야. 쉽지?

관련 사이트
1. http://fordev.tistory.com/71

2012년 2월 21일 화요일

mssql 복구시 액세스 거부 오류

1. Version : MS-SQL 2008 R2 Ent.


2. 문제 : mdf,ldf나 백업 파일로 복구 시도시 " .... 운영 체제 오류 5(액세스가 거부되었습니다.)" 라는 메시지 발생


3. 해결방법 : 해당 파일이나 폴더의 권한 설정에 관리자 계정을 추가하고 모든 권한을 주고 다시 쿼리를 실행하면 명령어가 잘 실행이 됨


4. 기타 참고 사이트
http://laigo.kr/321
http://sqlsql.tistory.com/87
http://zammany.tistory.com/