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. 위의 스크립트를 통해 생성한 배치파일을 실행하는것으로 백업이 가능하며
윈도우 스케쥴링에 등록하여 자동 백업이 가능하게 됩니다.