Saturday, September 25, 2010

SQL Backup Plan with Dynamic file Name

DECLARE @day         VARCHAR(5)
DECLARE @month         VARCHAR(15)
DECLARE @year          VARCHAR(5)
DECLARE @hour          VARCHAR(5)
DECLARE @filename     VARCHAR(500)

SET @day     =   DATENAME(DAY, GETDATE())
SET @month     =   DATENAME(MONTH, GETDATE())
SET @year     =   DATENAME(YEAR, GETDATE())
SET @hour     =   DATENAME(HOUR, GETDATE())
SET @filename = 'D:\DataBase Backup\db_name_' + @day + @month + @year + @hour + '.bak'

Backup Database db_name to Disk = @filename with format   
BACKUP log db_name to disk = @filename with format

No comments:

Post a Comment