Developers Club geek daily blog

1 year, 4 months ago
The plan of service "for every day" – Part 3: Automatic creation of backupsThere is a great quantity of posts in which persistently call for one simple truth – it is necessary to do backups on a permanent basis. But people will always be divided into two categories: who does not do backups yet, and who already does them. The first category which scorns such opinion it is often possible to meet at profile forums with approximately identical questions:

– at me disks / kto-to departed deleted my base … as to me to recover my data?
– you have a fresh backup?
– no

Not to become the hero of such situation, it is necessary to spend a minimum of efforts. First, to select the disk array on which to put backup copies. As to store backups together with the DB files – obviously not our choice. The second … it to create the plan of service for backup of databases.

What we will also make further, and later we will discuss some subtleties connected with backups.

Let's create the table in which error messages will register during creation of backup copies:

USE [master]
GO

IF OBJECT_ID('dbo.BackupError', 'U') IS NOT NULL
    DROP TABLE dbo.BackupError
GO

CREATE TABLE dbo.BackupError (
    db SYSNAME PRIMARY KEY,
    dt DATETIME NOT NULL DEFAULT GETDATE(),
    msg NVARCHAR(2048)
)
GO

Script for backup of databases for every day I use such:

USE [master]
GO

SET NOCOUNT ON
TRUNCATE TABLE dbo.BackupError

DECLARE
      @db SYSNAME
    , @sql NVARCHAR(MAX)
    , @can_compress BIT
    , @path NVARCHAR(4000)
    , @name SYSNAME
    , @include_time BIT

--SET @path = '\\pub\backup' -- можно задать свой путь для бекапа
IF @path IS NULL -- либо писать в папку для бекапов указанную по умолчанию
    EXEC [master].dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',
            N'BackupDirectory', @path OUTPUT, 'no_output'

SET @can_compress = ISNULL(CAST(( -- вопросы сжатия обсуждаются ниже
    SELECT value
    FROM sys.configurations
    WHERE name = 'backup compression default') AS BIT), 0)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT d.name
    FROM sys.databases d
    WHERE d.[state] = 0
        AND d.name NOT IN ('tempdb') -- базы для которых не надо делать бекапов

OPEN cur

FETCH NEXT FROM cur INTO @db

WHILE @@FETCH_STATUS = 0 BEGIN

    IF DB_ID(@db) IS NULL BEGIN
        INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db is missing')
    END
    ELSE IF DATABASEPROPERTYEX(@db, 'Status') != 'ONLINE' BEGIN
        INSERT INTO dbo.BackupError (db, msg) VALUES (@db, 'db state != ONLINE')
    END
    ELSE BEGIN

        BEGIN TRY

            SET @name = @path + '\T' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + @db + '.bak'
            SET @sql = '
                BACKUP DATABASE ' + QUOTENAME(@db) + '
                TO DISK = ''' + @name + ''' WITH NOFORMAT, INIT' + 
                CASE WHEN @can_compress = 1 THEN ', COMPRESSION' ELSE '' END

            --PRINT @sql
            EXEC sys.sp_executesql @sql

        END TRY
        BEGIN CATCH
            INSERT INTO dbo.BackupError (db, msg) VALUES (@db, ERROR_MESSAGE())
        END CATCH

    END

    FETCH NEXT FROM cur INTO @db
END

CLOSE cur
DEALLOCATE cur

If on the server the Database Mail component is configured, then it is possible to add the notification to a script by mail about the arisen problems:

IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN

    DECLARE @report NVARCHAR(MAX)
    SET @report =
        '<table border="1"><tr><th>database</th><th>date</th><th>message</th></tr>' +
        CAST(( 
            SELECT td = db, '', td = dt, '', td = msg
            FROM dbo.BackupError
            FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX)) +
        '</table>'

    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'your_account@mail.ru',
        @subject = 'Backup Problems',
        @body = @report,
        @body_format = 'HTML'

END

Actually, at this stage, the working script for automatic creation of backup copies is ready. It is necessary to create it job which according to the schedule would start this script.

Owners of Express of editions need to be mentioned separately as in SQL Server Express edition there is no opportunity to use SQL Server Agent. What pechalka would not come after these words, actually, everything is solve. It is the simplest to create a BAT file with approximately similar contents:

sqlcmd -S <ComputerName>\<InstanceName> -i D:\backup.sql

Further to open Task Scheduler and to create in it a new task.

The plan of service "for every day" – Part 3: Automatic creation of backups

The second alternative – to use third-party developments which allow to start tasks of the schedule. Among it is possible to select SQL Scheduler – convenient and free Toole. The installer at me was lost therefore I will be grateful if someone to share the working link for readers.

Now we will talk about the useful trifles connected with backups.

Compression …

The possibility of compression of backups appeared for the first time in SQL Server of 2008. I remember time when working for 2005 versions to me with nostalgia backups were necessary to squeeze 7Zip-ohm. Now everything became much simpler.

But it is necessary to remember that compression of backups will be used only if to execute the BACKUP command with the COMPRESSION parameter or to include compression by default the following command:

USE [master]
GO

EXEC sp_configure 'backup compression default', 1
RECONFIGURE WITH OVERRIDE
GO

To the word it will be told what compressed backups has some advantages: less place is necessary for their storage, recovery of a DB is usually executed of compressed backups a little quicker, also they are quicker created as demand smaller quantity of I/O of operations. Minuses, by the way, too are – during the work with compressed backups load of the processor increases.

This request it is possible to return the size of the last FULL of a backup with compression and without:

SELECT
      database_name
    , backup_size_mb = backup_size / 1048576.0
    , compressed_backup_size_mb = compressed_backup_size / 1048576.0
    , compress_ratio_percent = 100 - compressed_backup_size * 100. / backup_size
FROM (
   SELECT
          database_name
        , backup_size
        , compressed_backup_size = NULLIF(compressed_backup_size, backup_size)
        , RowNumber = ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC)
    FROM msdb.dbo.backupset
    WHERE [type] = 'D'
) t
WHERE t.RowNumber = 1

Usually compression reaches 40-90% if not to take binary data in attention:

database_name                   backup_size_mb   compressed_backup_size_mb  compress_ratio_percent
------------------------------- ---------------- -------------------------- ------------------------
AdventureWorks2012              190.077148437    44.652716636               76.50810894222767
DB_Dev                          1530.483398437   295.859273910              80.66890015190163
BinDocuments                    334.264648437    309.219978332              7.49246748707956
locateme                        37.268554687     17.247792243               53.72025454546944
master                          3.643554687      0.654214859                82.04459888434736
model                           2.707031250      0.450525283                83.35721895292208
msdb                            17.147460937     2.956551551                82.75807967958028
OnlineFormat                    125.078125000    23.639108657               81.10052524545207
Refactoring                     286.076171875    35.803841590               87.48450758543927
ReportServer$SQL_2012           4.045898437      0.696615219                82.78218719828627
ReportServer$SQL_2012TempDB     2.516601562      0.428588867                82.96953822273962

If to modify the previous request, then it is possible to monitor for what bases backup copies were made:

SELECT
      d.name
    , rec_model = d.recovery_model_desc
    , f.full_time
    , f.full_last_date
    , f.full_size
    , f.log_time
    , f.log_last_date
    , f.log_size
FROM sys.databases d
LEFT JOIN (
    SELECT
          database_name
        , full_time = MAX(CASE WHEN [type] = 'D' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_time = MAX(CASE WHEN [type] = 'L' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_start_date
            , s.backup_finish_date
            , backup_size =
                        CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) f ON f.database_name = d.name

If at you is SQL Server 2005, then this line:

backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN ...

it is necessary to change on:

backup_size = s.backup_size / 1048576.0

Results of this request can help to prevent many problems:

name                         rec_model full_time full_last_date      full_size         log_time  log_last_date       log_size
---------------------------- --------- --------- ------------------- ----------------- --------- ------------------- ------------
master                       SIMPLE    00:00:01  2015-11-06 15:08:12 0.654214859       NULL      NULL                NULL
tempdb                       SIMPLE    NULL      NULL                NULL              NULL      NULL                NULL
model                        FULL      00:00:00  2015-11-06 15:08:12 0.450525283       NULL      NULL                NULL
msdb                         SIMPLE    00:00:00  2015-11-06 15:08:12 2.956551551       NULL      NULL                NULL
ReportServer$SQL_2012        FULL      00:00:01  2015-11-06 15:08:13 0.696615219       NULL      NULL                NULL
ReportServer$SQL_2012TempDB  SIMPLE    00:00:00  2015-11-06 15:08:13 0.428588867       NULL      NULL                NULL
DB_Dev                       FULL      00:00:13  2015-11-06 15:08:26 295.859273910     00:00:04  2015-11-01 13:15:39 72.522538642
BinDocuments                 FULL      00:00:05  2015-11-06 15:08:31 309.219978332     00:00:01  2015-11-06 13:15:39 2.012338638
Refactoring                  SIMPLE    00:00:02  2015-11-06 15:08:33 35.803841590      NULL      NULL                NULL
locateme                     SIMPLE    00:00:01  2015-11-06 15:08:34 17.247792243      NULL      NULL                NULL
AdventureWorks2012           FULL      00:00:02  2015-11-06 15:08:36 44.652716636      NULL      NULL                NULL
OnlineFormat                 SIMPLE    00:00:01  2015-11-06 15:08:39 23.639108657      NULL      NULL                NULL

It is possible to see at once that whether for all the DB is FULL backups for actual date.

Further it is possible to look at time of creation of a backup. Why it is asked? Let's assume that earlier the backup of the DB_Dev base took 5 seconds, and then began to take 1 hour. The reasons of it can be much: disks do not cope with loading, data in base grew to indecent volumes, the disk departed to RAID and writing rate decreased.

If the base has a model of recovery of FULL or BULK_LOGGED, then it is desirable to do a log backup from time to time not to doom the server to torments of permanent growth of the LDF file. It is possible to look at fill factor of the data file and a log for databases with this request:

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
    database_id INT PRIMARY KEY,
    data_used_size DECIMAL(18,6),
    log_used_size DECIMAL(18,6)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT 
      database_name = DB_NAME(t.database_id)
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , t.total_size
FROM (
    SELECT
          database_id
        , log_size = SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024
        , data_size = SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024
        , total_size = SUM(size) * 8. / 1024
    FROM sys.master_files
    GROUP BY database_id
) t
LEFT JOIN #space s ON t.database_id = s.database_id

Results of request on my local instans:

database_name                  data_size     data_used_size  log_size      log_used_size  total_size
------------------------------ ------------- --------------- ------------- -------------- --------------
master                         4.875000      3.562500        1.750000      0.781250       6.625000
tempdb                         8.000000      4.500000        0.500000      0.632812       8.500000
model                          4.062500      2.562500        1.250000      0.609375       5.312500
msdb                           16.687500     16.062500       26.187500     2.804687       42.875000
ReportServer$SQL_2012          4.062500      3.937500        10.125000     1.570312       14.187500
ReportServer$SQL_2012TempDB    4.062500      2.437500        1.312500      0.500000       5.375000
DB_Dev                         1782.812500   1529.562500     7286.125000   42.570312      9068.937500
BinDocuments                   334.000000    333.500000      459.125000    12.031250      793.125000
Refactoring                    333.125000    285.625000      127.882812    0.851562       461.007812
locateme                       591.000000    36.500000       459.125000    8.585937       1050.125000
AdventureWorks2012             205.000000    189.125000      0.750000      0.453125       205.750000
OnlineFormat                   125.375000    124.437500      1.015625      0.414062       126.390625

Still wanted to show couple of interesting tricks which can facilitate life. If at execution of the command of BACKUP to specify several ways, then the final file with a backup will be cut on pieces of approximately identical size.

BACKUP DATABASE AdventureWorks2012 
TO
	DISK = 'D:\AdventureWorks2012_1.bak',
	DISK = 'D:\AdventureWorks2012_2.bak',
	DISK = 'D:\AdventureWorks2012_3.bak'
GO

Once I needed it when it was necessary to copy a backup on the USB stick with the FAT32 file system in which there is a restriction for the maximum file size.

One more interesting opportunity – to create the copy of a backup. From personal experience I will tell that it happened to meet people who created a backup in the default folder in the beginning, and then hands or a script copied on disk to a sphere. And it was necessary just to use such command:

BACKUP DATABASE AdventureWorks2012 
	TO DISK = 'D:\AdventureWorks2012.bak'
	MIRROR TO DISK = 'E:\AdventureWorks2012_copy.bak'
	WITH FORMAT
GO

The previous posts for automation of tasks "for every day":

Part 1: Automatic defragmentation of indexes
Part 2: Automatic updating of statistics

This article is a translation of the original post at habrahabr.ru/post/270401/
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here: sysmagazine.com@gmail.com.

We believe that the knowledge, which is available at the most popular Russian IT blog habrahabr.ru, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus