백업 혹은 복구 중인 데이터 베이스의 진행 상태나 진행률을 확인하는 SQL 입니다.

경과된 시간 , 남은 시간 , 예상 소요 시간등을 확인 할 수 있습니다.

SELECT r.session_id,r.command
,cast(CONVERT(NUMERIC(6,2),r.percent_complete)  as varchar) + ' %' AS [Percent Complete]
,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time]
,CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT text FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL] 
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

테이블의 속성이 변경되었을 때

혹은 컬럼 속성이 변경되었을 때 

아니면 테이블의 용도나 정체(?)를 알 수 없을 때

관련된 sp를 확인하기 위해서 개인적으로 자주 사용하는 SQL 입니다.

특정 프로시저에서 사용하는 테이블 목록을 조회하는 SQL 문입니다.

-- 프로시저에서 사용하는 테이블 목록
;WITH stored_procedures AS 
(   

    SELECT oo.name AS table_name,   o.name,
		ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row   
    FROM sysdepends d   
	    INNER JOIN sysobjects o ON o.id=d.id   
	    INNER JOIN sysobjects oo ON oo.id=d.depid   
    WHERE o.xtype = 'P' AND o.name LIKE '%프로시저명%' 
)  

SELECT name,Table_name FROM stored_procedures  
WHERE row = 1

반대로 특정 테이블을 참조하는 프로시저 목록을 조회하는 SQL문 입니다.

-- 특정 테이블을 참조하는 프로시저 목록
;WITH stored_procedures AS 
(   

    SELECT oo.name AS table_name,   o.name,
		ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row   
    FROM sysdepends d   
	    INNER JOIN sysobjects o ON o.id=d.id   
	    INNER JOIN sysobjects oo ON oo.id=d.depid   
    WHERE o.xtype = 'P' AND oo.name = '테이블명' 
)  

SELECT name,Table_name FROM stored_procedures  
WHERE row = 1

차등 백업은 전체 백업 마지막 시점을 기준으로 이후에 수정된 내용을 백업는 방법입니다.

기본적인 내용과 SQL문을 공유합니다.

-- 전체 백업

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_full.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- 차등백업 1 

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_1.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- 차등백업 2 

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_2.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- 기본 전체 백업 + 차등 복구

문법

RESTORE DATABASE [디비명] FROM DISK = N'풀백업위치'

               WITH MOVE '논리적 DB명' TO 'mdf 복구위치'

                            ,MOVE '논리적 LOG명' TO 'ldf 복구위치',  NORECOVERY,  NOUNLOAD,  STATS = 5;

RESTORE DATABASE [tes디비명] FROM  DISK = N'차등백업위치' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

RESTORE DATABASE [test3] FROM DISK = N'D:\Backup\test_full.bak'

               WITH MOVE 'test' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.mdf'

                            ,MOVE 'test_log' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5;

RESTORE DATABASE [test3] FROM  DISK = N'D:\Backup\test_2.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

백업 및 복구 시점별 데이터 체크 

test : 풀백업  ,  test1 : 풀백업+차등1st ,  test2 : 풀백업+차등2nd

개인적인 생각에서

장점은 백업 용량이 데이터 변화에 비례하기 때문에 수정이 적다면 용량도 적어집니다.

수정된 데이터와 용량은 비례하게 됩니다.

단점은 전체 백업이 소실되면 복구가 불가능함. 전체 백업본이 있어야 차등백업본이 의미가 있기 때문에

무슨일이 있어도 전체 백업은 잘 보관해야합니다.

적당주기별 전체 백업과 차등백업을 처리한다면...스토리지가 좋아합니다.

mysql 최대 결과 길이 변경하기

세팅 값에 따라 부하가 걸릴수도 있으니 고민 고민 후 변경.

#PHP
#해당 세션에만 적용됨.
SET SESSION group_concat_max_len = @@max_allowed_packet;

#my.ini
#/etc/mysql/my.cnf
# 변경하고자 하는 최대 값 // [mysqld] 하단에 추가 
# GROUP_CONCAT()함수 에 허용되는 최대 결과 길이 (바이트)/기본값은 1024
group_concat_max_len=4M 

적용 후 서비스 재시작 해야 적용 됨.

만약 적용 후 서비스 재시작에 문제가 있다면 아래 글 참고.

MySQL80 서비스가 로컬 컴퓨터에서 시작했다가 중지되었습니다. 일부 서비스는 다른 서비스 또는 프로그램에서 사용되지 않으면 자동으로 중지됩니다.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

MySQL80 서비스가 로컬 컴퓨터에서 시작했다가 중지되었습니다. 일부 서비스는 다른 서비스 또는 프로그램에서 사용되지 않으면 자동으로 중지됩니다.

MYSQL 설정 파일인 my.ini 파일을 메모장으로 수정 후 서비스 재시작 시 발생한 에러입니다.

원인 : my.ini 파일 인코딩 문제 

my.ini 파일 초기 인코딩은 ANSI

확인해보시면 아마도 UTF-8로 변경되어있을텐데

다시 ANSI로 변경 저장 후 서비스 시작하시면 정상 작동 될 것입니다.

특정 컬럼명에 대한 테이블 및 속성 검색하는 쿼리 SQL

select table_name ,column_name ,column_default, data_type
	, character_maximum_length ,character_octet_length 
From information_schema .columns 
where column_name='cityCd' 
order by character_maximum_length desc

쿼리 

 CREATE TABLE dbo.[테이블명] (
        seq int IDENTITY (1, 1) NOT NULL,
       id varchar(20) NOT NULL
 )
 
insert into dbo.[테이블명] (seq,id) values(1,'abc')

IDENTITY_INSERT가 OFF로 설정되면 테이블 '테이블명'의 ID 열에 명시적 값을 삽입할 수 없습니다.

IDENTITY 가 있는 데이터 테이블 생성 후 해당 컬럼 입력 시도 할 경우 에러 발생

아래와 같이 IDENTITY_INSERT ON 처리 후 실행 시 가능해짐

반드시 INSERT 후에 OFF 처리 해야 이후 문제가 안생김

SET IDENTITY_INSERT dbo.[테이블명] ON

insert into dbo.[테이블명] (seq,id) values(1,'abc')

SET IDENTITY_INSERT dbo.[테이블명] OFF

년,월,일,시,분 단위로 그룹핑하여 보기 각종 통계시 활용

select DATEPART(YEAR, adddt) '년'
,DATEPART(MONTH, adddt) '월'
,DATEPART(DAY, adddt) '일'
,DATEPART(HOUR, adddt) '시'
-- ,(DATEPART(MINUTE, adddt) / 10)
,count(*) FRom 테이블명 with(nolock)
 GROUP BY
DATEPART(YEAR, adddt)
,DATEPART(MONTH, adddt) 
,DATEPART(DAY, adddt)
,DATEPART(HOUR, adddt)
--,(DATEPART(MINUTE, adddt) / 10)
order by 1,2,3,4


select TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
,count(*) FRom 테이블명 with(nolock) 
group by TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
order by 1