[MSSQL] Tạo Bath Script backup database mssql trên windows

DECLARE @BackupFile varchar(255), @DB varchar(100), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(100), @MediaName varchar(100), @BackupDirectory nvarchar(200)
SET @BackupDirectory = 'C:\SQLBACKUP\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

    BEGIN
        SET @Name = @DB + '( Daily BACKUP )'
        SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
        SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' +
            CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
        SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'

        IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
            BEGIN
                SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                    CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                --SET some more pretty stuff for sql server.
                SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
            END    
        ELSE
            BEGIN
                SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                    CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                --SET some more pretty stuff for sql server.
                SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
            END
            BACKUP DATABASE @DB TO DISK = @BackupFile
            WITH NAME = @Name, DESCRIPTION = @Description ,
            MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
            STATS = 10
        FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

sqlcmd -S . -i "C:\SQLBACKUP\script\Backup_All_Databases.sql"
set _my_datetime=%date%
set _my_datetime=%_my_datetime: =_%
set _my_datetime=%_my_datetime::=%
set _my_datetime=%_my_datetime:/=_%
set _my_datetime=%_my_datetime:.=_%

SET WINRAR="C:\Program Files\WinRAR"

%WINRAR%\WinRAR.exe a -ep1 "C:\SQLBACKUP\%_my_datetime%.rar" "C:\SQLBACKUP\*.bak"
del "C:\SQLBACKUP\*.bak"

:: Xoa tat ca cac ban backup .rar qua 7 ngay
Forfiles -p C:\SQLBACKUP\ -s -m *.rar -d -7 -c "cmd /c del /q @path"

exit