태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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


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


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


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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
// 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 사이트에서 가져온 내용이고.. 위의 프로시져를 보시는편이 더 쉽게 이해하실수 있을 것 같습니다....)

1
2
3
4
5
6
7
8
9
10
11
-- 전체 백업 , 윈도우 인증을 통한 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'"


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

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




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

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


출처 - http://www.uhoon.co.kr/mssql/1301


'프로그래밍 > MS_SQL' 카테고리의 다른 글

MSSQL IDENTITY 값 재 설정  (0) 2015.04.27
MSSQL 자동 증가 시드값 가져오기  (0) 2015.04.27
sqlexpress 백업 및 복원  (0) 2015.04.24
mssql 파일 크기 줄이기  (0) 2015.04.14
MSSQL COMMENTS 생성  (0) 2015.04.13
mssql 테이블 용량 및 row 갯수  (0) 2015.03.24
블로그 이미지

구본숭 하늘을사랑한사람

진실된 사람으로 태어 나기 위한 조건들, 사랑하기 위해 필요한 것을 찾습니다.