블로그 "공감"

/*

                  Database Backup 용 procedure

*/

ALTER proc [dbo].[MNG_BackupDatabase]

as

set nocount on

declare @strFilename nvarchar(200)

declare @strName nvarchar(200)

declare @weekday tinyint

declare @DBName varchar(30)

declare @delDate varchar(50)

set @weekday = datepart(weekday, getdate())

-- Delete Old Backup Data (1Week)

set @delDate = convert(varchar(30), getdate() - 6)

EXECUTE master.dbo.xp_delete_file 0,N'G:\BackupFolder',N'bak', @delDate,1

declare curDBName cursor fast_forward for

                  select name from sys.sysdatabases where dbid in (5,7) order by name

open curDBName

fetch next from curDBName

                  into @DBName

while @@fetch_status = 0

begin

                  set @strName = N'G:\BackupFolder\' + @DBName + '\' + @DBName + '_backup_' + replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', '')

                  set @strFilename = N'G:\BackupFolder\' + @DBName + '\' + @DBName + '_backup_' + left(replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', ''), 12)

                  if @weekday = 1

                  begin

                                   -- Full Backup

                                   set @strFilename = @strFilename + '.bak'

                                   BACKUP DATABASE @DBName TO  DISK = @strFilename WITH NOFORMAT, NOINIT,  NAME = @strName, SKIP, REWIND, NOUNLOAD,  STATS = 10

                  end

                  else

                  begin

                                   -- Differntial Backup

                                   set @strFilename = @strFilename + '.bak'

                                   BACKUP DATABASE @DBName TO  DISK = @strFilename WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = @strName, SKIP, REWIND, NOUNLOAD,  STATS = 10

                  end

                  fetch next from curDBName

                                   into @DBName

end

close curDBName

deallocate curDBName