My Name is Kay....

DIY , 먹방 , 개발 , 육아 , 여행 좋아합니다.
AdBlock 사용시 화면이 정상적으로 노출되지 않습니다.
포스팅 관련 문의 및 개발 문의는 Email : wkzkfmxksi@gmail.com

추가 포스팅이 이뤄지지 않는 블로그입니다. 문의는 wkzkfmxksi@gmail.com 으로 연락주세요.
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

Express 버전의 경우 서버 에이젼트가 설치되지 않아 작업스케쥴링을 설정하는것이 불가능합니다..


그래서 배치파일("sqlcmd")과 프로시져를 이용한 백업 스크립트를 작성하고  윈도우 스케쥴링을 통해 자동 백업을 실행 할 수 있습니다.


상세한 내용은 MS Support Url을 참조해주세요. ( http://support.microsoft.com/kb/2019698/en-us )


1. 백업을 위한 프로시져 생성


// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 
 
       SET NOCOUNT ON; 
           
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
           
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
           
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
 
      WHILE @Loop IS NOT NULL
      BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
 
-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
 
-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
       IF @backupType = 'F' 
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L' 
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
 
-- Execute the generated SQL command
       EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END



2. 백업 프로시져 호출을 위한 bat 파일을 생성합니다.

( 아래 스크립트는 MS 사이트에서 가져온 내용이고.. 위의 프로시져를 보시는편이 더 쉽게 이해하실수 있을 것 같습니다....)

-- 전체 백업 , 윈도우 인증을 통한 SQL EXPRESS의 모든 대상 백업.
sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

-- 차등 백업 ,  Login 인증을 통한 SQL EXPRESS의 모든 대상 백업.
sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType=’D’"

-- 로그 전체 백업 , 윈도우 인증을  SQL EXPRESS의 모든 대상 백업.
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

-- 전체 백업 , 지정된 DB 를 윈도우 인증을 통해 백업
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"


 실제사용한 백업 배치파일 스크립트

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='E:\uhoon_DB_BACKUP\', @databaseName='db_name', @backupType='F'"



3. 위의 스크립트를 통해 생성한 배치파일을 실행하는것으로 백업이 가능하며

윈도우 스케쥴링에 등록하여 자동 백업이 가능하게 됩니다.




?

List of Articles
번호 제목 글쓴이 날짜 조회 수
48 프로시져 & 함수 백업 및 정보 확인 ( 생성일 , 수정일 등..) kay 2013.03.08 5011
47 프로시저 내용 보기 ( sp_helptext ) kay 2013.05.28 7495
46 특정 컬럼 , 타입 , 테이블 조건 확인하기 - information_schema .columns kay 2013.05.09 15706
45 특정 사용자에게 특정 테이블 , 뷰테이블 등.. 권한주기 file kay 2014.02.12 9921
44 테이블별 사이즈 및 Row 수 체크하기 kay 2013.08.26 25248
43 테이블 존재 확인하기 1 kay 2013.09.09 3138
42 테이블 정의서 생성 file kay 2013.03.15 17624
41 테이블 자동 증가값 초기화 kay 2013.04.25 2574
40 테이블 및 프로시져 컬럼 및 파라미터 정보 보기 단축키 ( Alt + F1 : sp_help ) file kay 2013.08.12 7100
39 테이블 내용 비교 쿼리 kay 2013.06.05 4883
38 캐시 초기화 kay 2013.03.08 3063
37 물리적 스토리지 용량 체크하기 (exec master.dbo.xp_fixeddrives) kay 2013.05.23 12821
36 문자결합 ( 커서 , 쿼리 ) kay 2013.07.01 3233
35 디비 로그 축소 kay 2013.03.08 4143
34 데이터 형식 우선 순위 kay 2013.07.10 2690
33 데이터 베이스 소유자 아이디 매칭 kay 2013.03.08 2896
32 데이터 대소문자 구분하기 - case-insensitive(CI) , accent-insensitive(AS) kay 2013.03.08 4064
31 덤프쿼리 생성.. ( sp_generate_insert_script ) file kay 2013.06.07 3192
30 단어 자동 완성(IntelliSense) 옵션 활성화 및 해제하기 file kay 2013.10.08 7363
29 단순 DB 온라인/오프라인 상태 체크하기 kay 2013.08.27 3185
Board Pagination Prev 1 2 3 Next
/ 3