USE [master]
GO
-- 시작안되는 경우도 발생하기 때문에 startDt 기본값을 넣지 않음.
CREATE TABLE [dbo].[tblBackupDB](
[dbName] [varchar](50) NULL,
[startDt] [datetime] NULL,
[endDt] [datetime] NULL,
[msg] [ntext] NULL,
[mdfSize] [int] NULL,
[ldfSize] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
2. 백업을 위한 프로시저 생성, DATEPART를 이용해 특정 요일마다 풀백업과 차등백업을 반복하도록 설정.
샘플을 위해 특정 요일에 풀백업 그 외 차등백업으로 규정하였으나
적절한 풀백업과 차등백업을 번갈아가며 환경에 맞는 주기를 설정합니다.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_DataBaseBackup]
AS
BEGIN
DECLARE @dbName varchar(4000), @SQL varchar(4000)
DECLARE @yoil int , @mdfSize int , @ldfSize int
SELECT @yoil = DATEPART(WEEKDAY,GETDATE())
-- 백업대상 기록 , 시스템 디비는 제외
INSERT INTO master.dbo.tblBackupDB(dbName)
SELECT name
FROM sys.databases
WHERE UPPER(name) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
-- 서버 아이피 체크
declare @ip_v4 varchar(16)
SELECT DISTINCT @ip_v4 = local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL and local_net_address <> '127.0.0.1'
DECLARE bk_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE UPPER(name) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN bk_cursor
FETCH NEXT FROM bk_cursor
INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- mdf 파일 사이즈
SELECT @mdfSize = SUM(size)*8/1024 --MB
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
WHERE sys.databases.name = @dbName and type='0'
-- ldf 파일 사이즈
SELECT @ldfSize = SUM(size)*8/1024 --MB
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
WHERE sys.databases.name = @dbName and type='1'
-- 백업시작 기록/용량 기록
UPDATE master.dbo.tblBackupDB
SET startDt = GETDATE() , mdfSize = @mdfSize, ldfSize = @ldfSize
WHERE dbname = @dbName
AND startDt is null
IF @yoil = 7 -- 토요일은 풀백업
BEGIN
--풀백업
SET @SQL = 'BACKUP DATABASE ['+@dbName+'] TO DISK = N''D:\Backup\'+convert(varchar(30), getdate(),112)+'_'+@dbName+'.bak'' WITH COMPRESSION, INIT, NAME = '''+@dbName+' - Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;'
END
ELSE -- 주간은 차등백업
BEGIN
--차등백업
SET @SQL = 'BACKUP DATABASE ['+@dbName+'] TO DISK = N''D:\Backup\'+convert(varchar(30), getdate(),112)+'_'+@dbName+'.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'''+@dbName+' - Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;'
END
--print @SQL
EXEC (@SQL)
-- 백업종료 기록
UPDATE master.dbo.tblBackupDB
SET endDt = GETDATE()
WHERE dbname = @dbName
AND CONVERT(CHAR(10),startDt,23) = CONVERT(CHAR(10),GETDATE(),23)
AND endDt IS NULL
END TRY
BEGIN CATCH
-- 에러 기록
UPDATE master.dbo.tblBackupDB
SET msg = 'ERROR_NUMBER : ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
ERROR_MESSAGE: ' + ERROR_MESSAGE()
WHERE dbname = @dbName
AND CONVERT(CHAR(10),startDt,23) = CONVERT(CHAR(10),GETDATE(),23)
AND endDt IS NULL
END CATCH
FETCH NEXT FROM bk_cursor
INTO @dbName
END
CLOSE bk_cursor;
DEALLOCATE bk_cursor;
-- FREEPROCCACHE 는 메모리 상에 전체 캐시를 비우기
-- https://bit.ly/3icmJ85
DBCC FREEPROCCACHE;
END
3. 백업 후 남겨진 기록과 msdb.dbo.backupset 테이블 데이터를 참조하여 리포팅 메일 발송.
메일 발송을 위해 데이터베이스 메일 구성이 되어있어야 합니다.
html 코딩은 개인 취향대로.. msdb.dbo.backupset 에는 생각보다 많은 정보가 들어있습니다.
Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC
