백업

목적 : 전체 백업 관리

• 백업 디바이스 지정 : 이전 버전의 SQL 경우, 백업을 실행하기 이전에 백업 장치에 대한 설정을 잡아주어야만 했다. 하지만 SQL2005에서는 이러한 설정을 잡아줄 필요가 없으며, 백업 장치를 정의해야 할 필요가 없다.  하지만, 백업 장치를 사용하면 시간이 지나도 백업 파일 이름과 장소가 늘 동일하게 유지하기 쉬운 방법을 제공한다.
• 대용량의 백업 Data Transfer Options : 백업시 I/O 버퍼 사이즈 지정.
  가능 버젼 : SQL2000(sp4), SQL2005
  참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms186865.aspx

 

• 백업 속도 향상
  
• http://www.brentozar.com/archive/2014/01/improving-the-performance-of-backups/
• http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

SQL2000

---------------------------------------------------------------------------------
-- SQL 2000
---------------------------------------------------------------------------------
USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_db_backup') 
 BEGIN
  DROP  PROCEDURE ap_db_backup
 END
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DBO.ap_db_backup
 @DBNAME  varCHAR(16),   
 @path  varchar(32) = 'E:\DB_TEST\Backup\', -- Backup PATH
 @gen  int = 7, -- 7 DAYS PASSED BACKUP FILE DROPS
 @IsTransferOption BIT = 0, -- default 0 : off 
 @bufferCount INT = 1,
 @maxtransfersize INT = 65536
AS
Begin   
 
  declare @dev_name varchar(64)   
  declare @dev_name_del varchar(64)   
  declare @physical_name  varchar(64)   
  declare @description varchar(64)   
   
  if ( select count(*) from master.dbo.sysdatabases where name = @dbname ) = 0    
  begin    
   select 'Database '' + @DBNAME + '' does not exist.'   
   return   
  end   
   
  select @description = @DBNAME  + ' , ' + CONVERT(CHAR(20),GETDATE())   
  select @dev_name = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() , 120 )   
  select @dev_name_del = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() - @gen , 120 )   
  select @physical_name = rtrim(upper(@PATH)) + rtrim(upper(@dev_name)) + '.BAK'   
  select @description = 'Daily Backup , ' + @DBNAME  + '   ' + CONVERT(CHAR(20),GETDATE())   
      

  if ( select count(*) from master.dbo.sysdevices where name = rtrim(@dev_name_del) ) > 0    
   EXEC sp_dropdevice @dev_name_del,'delfile'   
   
  if ( select count(*) from master.dbo.sysdevices where name = rtrim(@dev_name) ) = 0    
   EXEC sp_addumpdevice 'disk' , @dev_name , @physical_name 
   
  IF @IsTransferOption = 1
  BEGIN
   --Data Transfer Options ( BUFFERCOUNT, MAXTRANSFERSIZE )
   -- BUFFERCOUNT : 백업 작업에 사용되는 I/O 버퍼의 총 수를 지정합니다. 임의의 양의 정수를 지정할 수 있지만 버퍼 수가 많으면 Sqlservr.exe 프로세스의 부적절한 가상 주소 공간으로 인해 "메모리가 부족합니다"라는 오류가 발생할 수 있습니다.
   -- MAXTRANSFERSIZE : SQL Server와 백업 미디어 간에 사용되는 가장 큰 전송 단위(바이트)를 지정합니다. 가능한 값은 최대 4194304바이트(4MB)까지 65536바이트(64KB)의 배수입니다.
   -- 공식 : buffercount * maxtransfersize

   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , BUFFERCOUNT = @bufferCount
   , MAXTRANSFERSIZE = @maxtransfersize
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
  ELSE
  BEGIN
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
 
end

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL2005

USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_db_backup') 
 BEGIN
  DROP  PROCEDURE ap_db_backup
 END
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DBO.ap_db_backup
 @DBNAME  varCHAR(16),   
 @path  varchar(32) = 'E:\DB_TEST\Backup\', -- Backup PATH
 @gen  int = 7, -- 7 DAYS PASSED BACKUP FILE DROPS
 @IsTransferOption BIT = 0, -- default 0 : off 
 @bufferCount INT = 1,
 @maxtransfersize INT = 65536
AS
Begin  
 
  declare @dev_name varchar(64)   
  declare @dev_name_del varchar(64)   
  declare @physical_name  varchar(64)   
  declare @description varchar(64)   
   
  if ( select count(*) from sys.sysdatabases where name = @dbname ) = 0    
  begin    
   select 'Database '' + @DBNAME + '' does not exist.'   
   return   
  end   
   
  select @description = @DBNAME  + ' , ' + CONVERT(CHAR(20),GETDATE())   
  select @dev_name = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() , 120 )   
  select @dev_name_del = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() - @gen , 120 )   
  select @physical_name = rtrim(upper(@PATH)) + rtrim(upper(@dev_name)) + '.BAK'   
  select @description = 'Daily Backup , ' + @DBNAME  + '   ' + CONVERT(CHAR(20),GETDATE())   
      

  if ( select count(*) from sys.sysdevices where name = rtrim(@dev_name_del) ) > 0    
   EXEC sp_dropdevice @dev_name_del,'delfile'   
   
  if ( select count(*) from sys.sysdevices where name = rtrim(@dev_name) ) = 0    
   EXEC sp_addumpdevice 'disk' , @dev_name , @physical_name -- 2005 데이터베이스 엔진 의 인스턴스에 백업 장치를 추가합니다 
 
  -- backup option
  -- INIT :  미디어 헤더만 보존하고 모든 백업 세트를 덮어쓰도록 지정합니다
  -- NOSKIP :  BACKUP 문이 백업 세트를 덮어쓰기 전에 미디어에 있는 모든 백업 세트의 만료 날짜를 확인하도록 지정합니다.
  -- NOFORMAT : 이 백업 작업에 사용된 모든 볼륨에 미디어 헤더가 기록되지 않도록 지정합니다. 기본값입니다.
  IF @IsTransferOption = 1
  BEGIN
   --Data Transfer Options ( BUFFERCOUNT, MAXTRANSFERSIZE )
   -- BUFFERCOUNT : 백업 작업에 사용되는 I/O 버퍼의 총 수를 지정합니다. 임의의 양의 정수를 지정할 수 있지만 버퍼 수가 많으면 Sqlservr.exe 프로세스의 부적절한 가상 주소 공간으로 인해 "메모리가 부족합니다"라는 오류가 발생할 수 있습니다.
   -- MAXTRANSFERSIZE : SQL Server와 백업 미디어 간에 사용되는 가장 큰 전송 단위(바이트)를 지정합니다. 가능한 값은 최대 4194304바이트(4MB)까지 65536바이트(64KB)의 배수입니다.
   -- 공식 : buffercount * maxtransfersize

   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , BUFFERCOUNT = @bufferCount
   , MAXTRANSFERSIZE = @maxtransfersize
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
  ELSE
  BEGIN
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END   

end

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- TEST QUERY
--exec dbo.ap_db_backup AdventureWorks, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT