현재 시간을 파일명으로 만들어 백업하기

declare @path nvarchar(500)
declare @year nchar(4)
  = REPLICATE ('0', 4-LEN (DATEPART(year, getdate())))
+ convert(nvarchar(4), DATEPART(year, getdate()))
declare @month nchar(2)
  = REPLICATE ('0', 2-LEN (DATEPART(month, getdate())))
+ convert(nvarchar(2), DATEPART(month, getdate()))
declare @day nchar(2)
  = REPLICATE ('0', 2-LEN (DATEPART(day, getdate())))
+ convert(nvarchar(2), DATEPART(day, getdate()))
declare @time nchar(6)
  = REPLICATE ('0', 2-LEN (DATEPART(hour, getdate())))
+ convert(nvarchar(2), DATEPART(hour, getdate()))
  + REPLICATE ('0', 2-LEN (DATEPART(minute, getdate())))
+ convert(nvarchar(2), DATEPART(minute, getdate()))
  + REPLICATE ('0', 2-LEN (DATEPART(second, getdate()))
+ convert(nvarchar(2), DATEPART(second, getdate()))

--전체 백업
set @path = N'D:\MSSQL\Backup\LDB_backup_' + @year + @month + @day + '_' + @time + N'.bak'
BACKUP DATABASE [LDB] TO  DISK = @path WITH NOFORMAT, COMPRESSION, NOINIT,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--로그 백업
set @path = N'D:\MSSQL\Backup\LDB_backup_' + @year + @month + @day + '_' + @time + N'.trn'
BACKUP LOG [LDB] TO  DISK = @path WITH COMPRESSION

댓글 없음: