MS-SQL DB 백업 스케줄

:: 백업 스크립트
1. 풀백업 (일주일에 한번 일요일 오전 2시 이후)
DECLARE CurQuery CURSOR FOR
select name from dbo.sysdatabases where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100) OPEN CurQuery
FETCH NEXT FROM CurQuery INTO @name WHILE @@FETCH_STATUS = 0 BEGIN declare @dir nvarchar(200) set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + convert
(nvarchar(20), getDate(), 112) + replace(substring(convert(nvarchar(20), getDate(), 114),
1,5),':','') + '.dmp' backup database @name to DISK = @dir PRINT @dir FETCH NEXT FROM CurQuery INTO @name
END --end of while
CLOSE CurQuery
DEALLOCATE CurQuery

2. 차등백업 (하루에 한번 오전 2시 이후)
DECLARE CurQuery CURSOR FOR
select name from dbo.sysdatabases where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100) OPEN CurQuery
FETCH NEXT FROM CurQuery INTO @name WHILE @@FETCH_STATUS = 0 BEGIN declare @dir nvarchar(200) set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + convert
(nvarchar(20), getDate(), 112) + replace(substring(convert(nvarchar(20), getDate(), 114),
1,5),':','') + '.dif'
backup database @name to DISK = @dir with differential PRINT @dir FETCH NEXT FROM CurQuery INTO @name
END --end of while
CLOSE CurQuery
DEALLOCATE CurQuery

3. 로그백업 (한시간에 한번) (sp_helpDB로 DB recovery가 simple이 아닐 경우 )
DECLARE CurQuery CURSOR FOR
select name from dbo.sysdatabases where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE' DECLARE @name varchar(100) OPEN CurQuery
FETCH NEXT FROM CurQuery INTO @name WHILE @@FETCH_STATUS = 0 BEGIN declare @dir nvarchar(200) set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + convert
(nvarchar(20), getDate(), 112) + replace(substring(convert(nvarchar(20), getDate(), 114),
1,5),':','') + '.lg'
backup log @name to DISK = @dir PRINT @dir FETCH NEXT FROM CurQuery INTO @name
END --end of while
CLOSE CurQuery
DEALLOCATE CurQuery