Devart Blog

SQL Server Typical Maintenance Plans: Automated Database Backups

Posted by on December 4th, 2015

There are a lot of posts appealing to one simple truth – need perform backups on a regular basis. People will always be divided into two categories: those who do backup, and those who don’t. The first category, which ignores this advice, can often be found on relevant forums with much the same questions:

– My discs failed/someone has deleted my db… How can I restore my data?
– Do you have the latest backup?
– No.

In order to avoid this unpleasant situation, minimum effort is required. First thing you should do is select a disk array for storing backup copies. Since storing backups together with database files is not our choice, the second step will be to create a maintenance plan for the database backup.

Next, we will discuss some subtleties associated with backups.

Let us create a table for recording backup-related error messages:

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

The following script may be used for every day database backup:

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 the Database Mail component is configured on a server, an email notification about the problems, which may arise, can be added to the script:

IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN

    DECLARE @report NVARCHAR(MAX)
    SELECT @report = STUFF((
    	SELECT '
        ' + db + ' (' + CAST(dt AS VARCHAR(20)) + '):' + msg
        FROM dbo.BackupError
    	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

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

END

At this stage, the working script for automatic backup creation is ready for use. The only thing left to do is to create the job, which would run this script on schedule.

The owners of the Express editions should to be given a special mention, because SQL Server Express edition does not provide the possibility to use SQL Server Agent. As sad as it sounds, in fact, that can be easily solved. The easiest way is to create a bat file with a similar content:

sqlcmd -S \ -i D:\backup.sql

Next, open the Task Scheduler and create a new task therein.

Task Scheduler

The second option is to use a third-party software, which allows to run the tasks on schedule. Among them is SQL Scheduler – a convenient free tool.

Now let’s talk about useful things associated with backups.

Backup Compression

The backup’s compression feature appeared: SQL Server 2008 (only Enterprise/Developer edition), 2008R2/2012/2014 (only Enterprise, Business Intelligence, Standard, Developer edition). There was a time when working with SQL Server 2005, I had to compress backups using 7Zip. Now everything is much easier.

We should bear in mind that the backup compression will only be used when you execute the BACKUP command with the COMPRESSION parameter or enable a default compression using the following command:

USE [master]
GO

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

By the way, the compressed backups have a number of advantages:
1) you need less space to store them;
2) restoring databases from compressed backups usually takes less time;
3) the creation of compressed backups is faster and requires fewer I/O operations.

A few drawbacks also exist – when working with compressed backups, the CPU load increases.

The following query returns the size of the last FULL backup both compressed and not compressed:

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, the compression reaches 40-90%, leaving aside the binary data:

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 you modify the previous query, you can monitor the databases, for which backup copies were created:

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 you use SQL Server 2005, than this line:

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

should be changed to:

backup_size = s.backup_size / 1048576.0

The results of this query can help prevent many of the 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

You can see right away whether all the databases have FULL backups for the current date.

Then you can look at the time of backup creation. You may ask why? Suppose that the backup of the DB_Dev database used to take 5 seconds, and then it began to last for 1 hour. The reasons for that can be various: disks do not handle the load, the data in the database grew up enormously, a disc failure in RAID and the writing speed has decreased.

If the database contains the FULL or BULK_LOGGED recovery model, it is desirable to make log backup from time to time, to relief the server from constant growth of the LDF file. You can see the degree of filling of the data file and log for the database using this query:

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

The results of the query on my local SQL Server Instance:

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

There are also a couple of interesting tricks that can make your life easier. Specify several paths during the execution of the BACKUP command, and the resulting backup file will be cut into pieces of about the same size:

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

This can be useful when you copy a backup to a USB flash drive with the file system FAT32, which has the limitation on the maximum file size.

Another interesting possibility is the creation of a backup copy (only in Enterprise/Developer edition). Instead of creating a backup in a default folder and manually copying the script to a shared folder, you can simply use the following command:

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