Buat jadwal Otomatis Backup SQL 2012 dan Kompres

Catatanku

Untuk membuat jadwal otomatis backup dan kompres pada SQL 2012 .

Ini Scrip nya

SET NOCOUNT ON;

DECLARE
      @FileName NVARCHAR(1024)
    , @DBName NVARCHAR(256)
    , @PathName NVARCHAR(256)
    , @Message NVARCHAR(2048)
    , @IsCompressed BIT

SELECT
      @PathName = 'E:\BACKUP DATABASE\'
    , @IsCompressed = 1

DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT
          sd.name
        , file_path = @PathName +name+ '_' +FileDate  +  '.bak'
    FROM sys.databases sd
    CROSS JOIN (
        SELECT FileDate = '' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
    ) fd
    WHERE sd.state_desc != 'OFFLINE'
        AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb','dtc_trial')
    ORDER BY sd.name

OPEN db

FETCH NEXT FROM db INTO
      @DBName
    , @FileName

WHILE @@FETCH_STATUS = 0 BEGIN

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
    RAISERROR (@Message, 0, 1) WITH NOWAIT

    SELECT @SQL =
    'BACKUP DATABASE [' + @DBName + ']
    TO DISK = N''' + @FileName + '''
    WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;'

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM db INTO
          @DBName
        , @FileName

END  

CLOSE db  
DEALLOCATE db

--backup log

DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT
          sd.name
        , file_path = @PathName +name+ '_' +FileDate  +  '.TRN'
    FROM sys.databases sd
    CROSS JOIN (
        SELECT FileDate = '' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
    ) fd
    WHERE sd.state_desc != 'OFFLINE'
        AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb','dtc_trial')
AND DATABASEPROPERTYEX (name,'Recovery') IN ('FULL','BULK_LOGGED')
    ORDER BY sd.name

OPEN db

FETCH NEXT FROM db INTO
      @DBName
    , @FileName

WHILE @@FETCH_STATUS = 0 BEGIN

   
    SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
    RAISERROR (@Message, 0, 1) WITH NOWAIT

    SELECT @SQL =
    'BACKUP LOG [' + @DBName + ']
    TO DISK = N''' + @FileName + '''
    WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;'

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM db INTO
          @DBName
        , @FileName

END  

CLOSE db  
DEALLOCATE db

 
  http://widiy.blogspot.com  

Bagikan

Jangan lewatkan

Buat jadwal Otomatis Backup SQL 2012 dan Kompres

4 / 5

Oleh nur gmail

nur gmail Selasa, Juni 28, 2016 Komentar