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
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
create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'
open c
fetch c into @id
while @@fetch_status = 0 begin
insert into #t
exec sp_spaceused @id
fetch c into @id
end
close c
deallocate c
select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #t
NOTRUNCATE target_percent의 지정 여부와 관계없이 데이터 파일의 끝에서 할당된 페이지를 파일 앞의 할당되지 않은 페이지로 이동합니다.파일 끝의 사용 가능한 공간이 운영 체제에 반환되지 않고, 파일의 물리적 크기가 변경되지 않습니다.그러므로 NOTRUNCATE를 지정하면 파일이 축소되지 않는 것처럼 보입니다.NOTRUNCATE는 데이터 파일에만 적용되며로그 파일에는 영향을 주지 않습니다.이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.
TRUNCATEONLY 파일 끝의 모든 사용 가능한 공간을 운영 체제로 해제하지만 파일 내에서 페이지 이동을 수행하지 않습니다.데이터 파일은 마지막으로 할당된 익스텐트까지만 축소됩니다.TRUNCATEONLY로 지정되면target_size가 무시됩니다. TRUNCATEONLY 옵션은 로그에 있는 정보를 이동시키지 않습니다. 하지만 로그 파일의 끝에서 비활성 상태의 VLF를 제거합니다.이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.
TRUNCATEONLY 옵션으로는 중간중간에 비어있는 공간에 대해서 해제를 하지 못한다는 것.
NOTRUNCATE 옵션으로 중간중간 비어있는 공간들을 당겨주고
TRUNCATEONLY 옵션으로 해제해주어야 원하는 결과를 얻을 수 있었다.
DBCC SHRINKFILE(DB명,NOTRUNCATE)
-- DB 빈 공간 정리 마치 조각 모음과 같다. ( 빈 공간은 그대로 남아있다 )
DBCC SHRINKFILE(DB명, TRUNCATEONLY)
-- 빈 여유 공간 제거 ( 사이즈 축소 )
