How do I initiate a backup of database automatically using script and backup file needs to be
unique with date and time(till seconds) is included in backup file.
Below mentioned command takes DBName as the input parameter and it creates a folder based on database name and places backup file inside that.
DECLARE @DBNAME VARCHAR(100)
DECLARE @EXEC_STR VARCHAR(100)
DECLARE @Backup_Folder VARCHAR(5000)
DECLARE @Backup_Path VARCHAR(5000)
SELECT @DBNAME = 'master'
SELECT @Backup_Folder = 'E:\' + @DBNAME + '\Backup\'
SELECT @EXEC_STR = 'md ' + @Backup_Folder
exec master..xp_cmdshell @EXEC_STR
SELECT @Backup_Path = @Backup_Folder + @DBNAME + '_Full_' + replace(replace(convert(varchar(20),getdate(),113),':','_'),' ' , '_') + '.bak'
SELECT @Backup_Path
BACKUP DATABASE @DBNAME TO DISK = @Backup_Path WITH NOFORMAT
, NOINIT
, NAME = 'Full Database Backup'
, SKIP,
,STATS = 5
GO
No comments:
Post a Comment