Everyday Service Plan - Part 3: Automatically Create Backups
- Tutorial
There are a large number of posts in which they insistently call for one simple truth - you need to do backups on an ongoing 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 neglects such advice, can often be found on specialized forums with approximately the same questions:
- my disks flew / someone deleted my database ... how can I recover my data?
- Do you have a fresh backup?
- no
In order not to become the hero of such a situation, you need to spend a minimum of effort. First, allocate a disk array on which to store backups. Since storing backups along with database files is clearly not our choice. The second ... is to create a database backup maintenance plan.
What we will do next, and after that we will discuss some of the subtleties associated with backups.
Create a table in which error messages will be recorded when creating backups:
The script for backing up databases for every day I use this:
If the Database Mail component is configured on the server , then you can add a notification by mail about the problems encountered in the script:
Actually, at this stage, a working script for automatically creating backups is ready. It remains to create it job , which would run this script on a schedule.
Owners of Express editions need to be mentioned separately, since there is no way to use SQL Server Agent in SQL Server Express edition . Whatever sadness comes after these words, in fact, everything is solved. The easiest way is to create a bat file with roughly similar contents:
Next, open the Task Scheduler and create a new task in it.
The second alternative is to use third-party developments that allow you to run tasks on a schedule. Among them, you can highlight SQL Scheduler - a convenient and free tool. My installer was lost, so I would be grateful if someone would share a working link for readers.
Now let's talk about useful little things related to backups.
Compression ... The
ability to compress backups was first introduced in SQL Server 2008 . I remember with nostalgia the time when working on the 2005 version I had 7Zip th compress backups. Now everything has become much easier.
But you need to remember that backup compression will be used only if you run the BACKUP command with the COMPRESSION parameter or enable default compression with the following command:
By the way, it will be said that compressed backups have some advantages: you need less space to store them, restoring a database from compressed backups is usually done a little faster, they are also created faster because they require fewer I / O operations. By the way, there are also disadvantages - when working with compressed backups, the load on the processor increases.
With this query, you can return the size of the last FULL backup with and without compression:
Typically, compression reaches 40-90%, if you do not take binary data into account:
If you modify the previous request, then you can monitor for which databases backups were made:
If you have SQL Server 2005, then this line: need to be changed to: The results of this query can help prevent many problems:
You can immediately see that for all databases there are FULL backups for the current date.
Next, you can look at the backup creation time. Why is it asking? Suppose that earlier backup of the DB_Dev database 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 the recovery model FULL or BULK_LOGGED , then it is advisable to backup the log from time to time so as not to doom the server to the torment of constant growth of the LDF file. The degree of filling the data file and log for databases can be viewed with this query:
The query results on my local instance:
I also wanted to show a couple of interesting tricks that can make life easier. If several paths are specified when executing the BACKUP command , the resulting backup file will be cut into pieces of approximately the same size.
Once it came in handy when I had to copy backup to a USB flash drive with the FAT32 file system , which has a limit on the maximum file size.
Another interesting opportunity is to create a backup copy. From personal experience I’ll say that I’ve met people who first created backup in the default folder, and then copied it to a disk ball with their hands or a script. And you just had to use this command:
Previous posts for automating everyday tasks:
Part 1: Automatically defragment indexes
Part 2: Automatically update statistics
- my disks flew / someone deleted my database ... how can I recover my data?
- Do you have a fresh backup?
- no
In order not to become the hero of such a situation, you need to spend a minimum of effort. First, allocate a disk array on which to store backups. Since storing backups along with database files is clearly not our choice. The second ... is to create a database backup maintenance plan.
What we will do next, and after that we will discuss some of the subtleties associated with backups.
Create a table in which error messages will be recorded when creating backups:
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 the Database Mail component is configured on the server , then you can add a notification by mail about the problems encountered in the script:
IF EXISTS(SELECT 1 FROM dbo.BackupError) BEGIN
DECLARE @report NVARCHAR(MAX)
SET @report =
'' +
CAST((
SELECT td = db, '', td = dt, '', td = msg
FROM dbo.BackupError
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'database date message
'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'your_account@mail.ru',
@subject = 'Backup Problems',
@body = @report,
@body_format = 'HTML'
END
Actually, at this stage, a working script for automatically creating backups is ready. It remains to create it job , which would run this script on a schedule.
Owners of Express editions need to be mentioned separately, since there is no way to use SQL Server Agent in SQL Server Express edition . Whatever sadness comes after these words, in fact, everything is solved. The easiest way is to create a bat file with roughly similar contents:
sqlcmd -S \ -i D:\backup.sql
Next, open the Task Scheduler and create a new task in it.
The second alternative is to use third-party developments that allow you to run tasks on a schedule. Among them, you can highlight SQL Scheduler - a convenient and free tool. My installer was lost, so I would be grateful if someone would share a working link for readers.
Now let's talk about useful little things related to backups.
Compression ... The
ability to compress backups was first introduced in SQL Server 2008 . I remember with nostalgia the time when working on the 2005 version I had 7Zip th compress backups. Now everything has become much easier.
But you need to remember that backup compression will be used only if you run the BACKUP command with the COMPRESSION parameter or enable default compression 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: you need less space to store them, restoring a database from compressed backups is usually done a little faster, they are also created faster because they require fewer I / O operations. By the way, there are also disadvantages - when working with compressed backups, the load on the processor increases.
With this query, you 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
Typically, compression reaches 40-90%, if you do not take binary data into account:
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, then you can monitor for which databases backups 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: need to be changed to: The results of this query can help prevent many problems:
backup_size = CASE WHEN s.backup_size = s.compressed_backup_size THEN ...
backup_size = s.backup_size / 1048576.0
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 databases there are FULL backups for the current date.
Next, you can look at the backup creation time. Why is it asking? Suppose that earlier backup of the DB_Dev database 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 the recovery model FULL or BULK_LOGGED , then it is advisable to backup the log from time to time so as not to doom the server to the torment of 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
The 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 several paths are specified when executing the BACKUP command , the resulting backup file will be cut into pieces of 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 came in handy when I had to copy backup to a USB flash drive with the FAT32 file system , which has a limit on the maximum file size.
Another interesting opportunity is to create a backup copy. From personal experience I’ll say that I’ve met people who first created backup in the default folder, and then copied it to a disk ball with their hands or a script. 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 for automating everyday tasks:
Part 1: Automatically defragment indexes
Part 2: Automatically update statistics