Service plan "for every day" - Part 3: Automatic creation of backups

There are a great number of posts in which they insistently call for one simple truth - you need to make backups on an ongoing basis. But people will always be divided into two categories: who still does not make backups, and who already does them. The first category, which neglects such tips, can often be found on specialized forums with approximately the same questions:

- I flew disks / someone deleted my database ... how can I recover my data?
- Do you have a fresh backup?
- not

To avoid becoming the hero of this situation, you need to spend a minimum of effort. First, select the disk array on which to put backups. Because storing backups with database files is clearly not our choice. The second ... is to create a database backup maintenance plan.
')
What we will do next, and then we will discuss some of the subtleties associated with backups.

Create a table in which error messages will be recorded when creating 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 

The script for backing up databases for every day I use this:

 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 Database Mail is configured on the server, then you can add an email notification about the problems encountered to the script:

 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 backup is ready. It remains to create a job , which would run this script on schedule.

Owners of Express editions need to be mentioned separately, because in the SQL Server Express edition there is no possibility to use SQL Server Agent . Whatever sadness comes after these words, in fact, everything is solved. The easiest way to create a bat file with roughly similar content is:

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

Next, open Task Scheduler and create a new task in it.



The second alternative is to use third-party designs that allow you to run tasks on a schedule. Among them, we can highlight SQL Scheduler - a convenient and free tool. I have lost the installer, so I will be grateful if someone shares a working link for readers.

Now let's talk about the useful things associated with backups.

Compression…

The ability to compress backups appeared for the first time in SQL Server 2008 . I remember with nostalgia the time when working on the 2005 version I had to compress 7Zip backups. Now everything has become much easier.

But you have to remember that backup compression will be used only if you run the BACKUP command with the COMPRESSION parameter or enable compression by default with the following command:

 USE [master] GO EXEC sp_configure 'backup compression default', 1 RECONFIGURE WITH OVERRIDE GO 

By the way, it will be said that compressed backups have some advantages: less storage space is needed, database recovery from compressed backups is usually done a little faster, and they are also created faster because they require less I / O operations. By the way, there are also disadvantages - when working with compressed backups, the load on the processor increases.

This query can return the size of the last FULL backup with and without compression:

 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 you do not take into account 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 request, you can monitor for which databases 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 you have SQL Server 2005, then this line:

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

need to change to:

backup_size = s.backup_size / 1048576.0

The results of this query can help prevent many problems:

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

You can immediately see that for all whether the database has FULL backups for the current date.

Then you can look at the time of the backup. Why ask? Suppose that earlier backup database DB_Dev took 5 seconds, and then began to take 1 hour. There can be many reasons for this: the disks cannot cope with the load, the data in the database has grown to indecent volumes, the disk has flown in RAID and the write speed has decreased.

If the database has a FULL or BULK_LOGGED recovery model , then it is advisable to backup the log from time to time so as not to condemn the server to the constant growth of the LDF file. The degree of filling the data file and log for databases can be viewed with 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 

Query results on my local 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 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 

I also wanted to show a couple of interesting tricks that can make life easier. If you specify several paths when executing the BACKUP command, the final file with backup will be cut into approximately the same size.

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

Once it was useful to me when I had to copy backup to a USB flash drive with the FAT32 file system, in which there is a limit on the maximum file size.

Another interesting opportunity is to create a backup copy. From personal experience, I will say that I have met people who first created backup in the default folder, and then copied it by hand or by script to a disk disk. And you just had to use this command:

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

Previous posts to automate tasks "for every day":

Part 1: Automatic Index Defragmentation
Part 2: Automatic statistics update