Автор: Ирина Наумова

Содержание

Порядок создания резервной копии
Media set и media family
Системные таблицы

backupfile
backupset
backupmediafamily
backupmediaset

Оптимизация выполнения операции резервного копирования

Порядок создания резервной копии

Создание полной резервной копии файла данных происходит следующим образом:

• Производится копирование данных из базы на устройство резервного копирования. При этом пользователи могут работать с базой, исключая операции ALTER DATABASE с опциями ADD FILE и REMOVE FILE, операции сжатия базы данных либо отдельных ее файлов такие как SHRINKFILE, SHRINKDATABASE, в том числе и автоматическое сжатие. Все изменеия, вносимые в процессе резервного копирования сохраняются в журнале.
• Копируется часть журнала транзакций, необходимая для восстановление базы на момент создания копии (roll forward), включая и записи об операциях, выполнявшихся в ходе резервного копирования.

При создании дифференциальной копии на устройство копируются только изменения в базе, произошедшие с момента создания последней полной резервной копии, для того, чтобы установить эти изменения используется Differential Changed Map (DCM). Это битовая карта, каждый бит которой, установленный в 1, говорит о том, что в данном экстенте были изменения с момента последней полной копии, соответственно при значении 0 – изменений не было.
Также, в модели Bulk-Logged, добавляется еще страница Bulk Changed Map (BCM), это тоже битовая карта, которая определяет те экстенты, в которых массовые операции произвели изменения с момента последнего резервного копирования журнала транзакций. Когда выполняется операция BACKUP LOG (резервирование журнала транзакций), сканируются страницы BCM и записывает все измененные экстенты на резервное устройство наряду с записями журнала транзакций. В результате резервная копия занимает гораздо больше дискового пространства, чем в модели Full, а процесс резервирования выполняется дольше. В моделях Full и Simple BCM не используются.
Страницы DCM и BCM определяют по 64000 экстентов, и в файле они физически расположены за страницами GAM(Global Allocation Map) и SGAM(Shared Global Allocation Map).
Таким образом время выполнения дифференциального бэкапа прямо пропорционально количеству измененных экстентов с момента последней полной копии и не зависит от размера базы данных.
При копировании файлов или файловых групп, копируются только данные, журнал не копируется. Дифференциальную копию отдельных файлов или файловых групп возможно делать только в случае если модель резервирования Full или Bulk-Logged..
При создание копии журнала транзакций выполняется копирование лишь той части журнала, которая еще не была скопирована на устройство резервного копирования.

[В начало]

Media set и media family

При создании резервной копии SQL Server позволяет использовать несколько устройств резервного копирования одновременно. При этом резервная копия распределяется среди нескольких носителей. Необходимо, чтобы все устройства относились к одному типу носителей. Например нельзя использовать ленту и диск одновременно для создания резервной копии. Распараллеливание процесса копирования позволяет увеличить скорость копирования и тем самым уменьшить время на создание резервных копий.
Результат выполнения одной операции резервного копирования – резервный набор (backup set).
При резервном копировании с использованием нескольких накопителей на магнитной ленте, используется специальная терминология. Совокупность лент, которые используются для создания резервной копии, называется набором (media set).
Ленты, которые при создании резервной копии записывались одним устройством резервного копирования, называются семейством лент (media family). В случае, если используется дисковый накопитель, семейством является отдельный файл.
При записи резервных копий на ленточный накопитель, первая лента в семействе называется инициализирующей (Initial media). В случае если создаваемая резервная копия не помещается на инициализирующую ленту, используются дополнительные ленты (Continuation media).
Например, если для создания резервной копии используются четыре ленточных устройства (стриммера), каждый из которых содержит по пять лент, то набор содержит 20 лент. А набор лент, содержащих бэкап на отдельном устройстве, является семейством, т.е. для данного примера семейство будет состоять из пяти лент. Для этого примера необязательно использовать потом для восстановления все 5 стриммеров, можно использовать и один, но в этом случае придется загружать данные последовательно с каждой из лент.
Носители из набора (конктретный файл или ленту, если их несколько в семействе) нельзя использовать для создания резервной копии по отдельности. Т.е. например, если Вы создали резервную копию в три файла 1.bak, 2.bak, 3.bak, то следующую резервную копию нельзя поместить только в один из этих файлов, теперь для этих целей можно использовать только весь набор из трех файлов, либо удалить файлы и создать новый набор.
Также информацию об использовании наборов устройств резервного копирования можно получить в статье BOL "Using Media Sets and Families"
Перед тем, как поместить данные на устройство резервного копирования, оно инициализируется и форматируется если создается впервые, на устройство помещается заголовок. При повторном использование этого же устройства при выборе опции INIT происходит перезапись всех уже существующих копий, содержащихся на этом устройстве, при выполнении следующих условий:

• резервный набор устарел (истекла дата, которая была задана в параметре EXPIREDATA или RETAINDAYS при создании копии)
• указанное в параметре MEDIANAME имя носителя (если оно указано), совпадает с именем, записанным в заголовке носителя.
• не задан параметр SKIP, который отменяет проверку выполнения предыдущих пунктов.

Заголовок носителя сохраняется, но все имеющиеся на нем резервные наборы перезаписываются. Новый резервный набор помещается в начало носителя.
При выборе опции FORMAT происходит перезапись заголовков на каждом носителе, на который записывается резервный набор. Весь набор резервных копий, записанный на этом носителе, становится недоступным. Если резервный набор записывался на несколько носителей, то после перезаписи заголовка на одном из них, резервный набор также становится недоступным.

[В начало]

Системные таблицы

Вся хронология создания резервных копий хранится в базе данных msdb, в следующих таблицах:

backupfile
backupset
backupmediafamily
backupmediaset

[В начало]

backupfile

Содержит по одной строке для каждого файла данных и файла журнала транзакций.
Т.е. если например база данных состоит из двух файлов данных и двух файлов журнала транзакций (test.mdf, test1.ndf, test_log, ldf, test_log1.ldf), то заполнение строк таблицы будет выглядеть следующим образом:

Полная копия	Дифференциальная копия	Копия отдельных файлов или файловых групп	Копия журнала транзакций
4 строчки для каждого из файлов: test.mdf, test1.ndf, test_log, ldf, test_log1.ldf	2 строчки для файлов данных: test.mdf, test1.ndf	по одной строчке для каждого копируемого файла	2 строчки для файлов журнала: test_log, ldf, test_log1.ldf

Теперь рассмотрим поля таблицы и их значения.

Поле	Тип данных	Описание
backup_set_id	int NOT NULL REFERENCES backupset(backup_set_id)	Идентификатор резервной копии в наборе всех резервных копий. Ссылка на backupset.backup_set_id
first_family_number	tinyint NULL	Номер первого файла/ленты в семействе носителей, на которых содержится резервная копия.
first_media_number	smallint NULL	Номер первого файла/ленты в наборе носителей, на которых содержится эта резервная копия.
filegroup_name	nvarchar(128) NULL	Для резервной копии файла данных - имя файловой группы, к которой принадлежит файл данных. Для резервной копии журнала транзакций - NULL.
page_size	int NULL	Размер страницы в байтах. Для резервной копии файла данных - 8 192 (страница данных 8 кб * 1024), для копии файла журнала транзакций - NULL.
file_number	numeric(10,0) NOT NULL	Уникальный идентификатор резервируемого файла (FILE_ID), его можно посмотреть в таблице sysfiles базы данных.
backed_up_page_count	numeric(10,0) NULL	Общее число страниц, участвовавших в создании резервной копии.
file_type	char(1) NULL	'D' - для файла базы данных 'L' - для файла журнала транзакций
source_file_block_size	numeric(10,0) NULL	Размер блока при считывании данных из источника, где содержатся резервируемые данные.
file_size	numeric(20,0) NULL	Размер резервируемого файла в байтах. Можно узнать так: select size*8*1024,name from test..sysfiles
logical_name	nvarchar(128) NULL	Логическое имя файла, для которого создается резервная копия. Определяется полем name таблицы sysfiles текущей БД.
physical_drive	varchar(260) NULL	Имя устройства или раздела диска, на котором содержатся резервируемые данные. Например C:\
physical_name	varchar(260) NULL	Физическое имя резервируемого файла, например C:\Data\Test_data.mdf

[В начало]

backupset

Содержит по ожной строке для каждого файла резервного набора.

Название поля	Тип данных	Описание
backup_set_id	int NOT NULL IDENTITY PRIMARY KEY	Уникальный идентификатор резервной копии в наборе всех копий.
backup_set_uuid	uniqueidentifier NOT NULL	Уникальный идентификатор резервной копии, определяющий набор резервных копий на носителе.
media_set_id	int NOT NULL REFERENCES backupmediaset (media_set_id)	Ссылка на поле media_set_id таблицы backupmediaset
first_family_number	tinyint NULL	Номер первого файла / ленты в семействе, на котором содержится начало резервной копии.
first_media_number	smallint NULL	Номер носителя в наборе носителей, на котором содержится начало резервной копии.
last_family_number	tinyint NULL	Номер последнего файла / ленты в семействе, на котором содержится окончание резервной копии.
last_media_number	smallint NULL	Номер носителя в наборе носителей, на котором содержится окончание резервной копии.
catalog_family_number	tinyint NULL	Номер семейства, содержащего указатель на начало резервного набора
catalog_media_number	smallint NULL	Номер носителя, содержащего указатель на начало резервного набора
position	int NULL	Если резервные копии не перезаписываются, а добавляются в файл (опция NOINIT в операторе Backup), это значение показывает порядковый номер текущей копии в общем файле, содержащем несколько копий. Напимер, если мы сделали сначала резервную копию файла данных базы test в файл c:\test.bak, а затем копию журнала транзакций базы test в тот же файл c:\test.bak c опцией NOINIT, то для копии файла данных значение position будет =1, а для журнала =2 Это значение используется при восстановлении данных.
expiration_date	datetime NULL	Это значение заполняется в случае использования опции EXPIREDATE или RETAINDAYS оператора BACKUP и указывает дату и время, по прошествии которого резервную копию можно перезаписать.
software_vendor_id	int NULL	Идентификационный номер поставщика программного обеспечения, с помощью которого осуществляется запись заголовков на носитель. Для MS SQL Server это значение равно 4 608 в десятичной или 0х1200 в шестнадцатеричной системе счисления.
name	nvarchar(128) NULL	Имя резервной копии, задается параметром NAME оператора BACKUP, если не задано, присваивается значение NULL
description	nvarchar(255) NULL	Описание резервного набора, задается параметром DESCRIPTION оператора BACKUP, если не задано, присваивается NULL
user_name	nvarchar(128) NULL	Имя пользователя, производившего эту операцию резервного копирования.
software_major_version	tinyint NULL	select serverproperty('ProductVersion') Дает следующий результат: 'major.minor.build'
software_minor_version	tinyint NULL	см. software_major_version. SQL Server minor version number.
software_build_version	smallint NULL	см. software_major_version. SQL Server build number.
time_zone	smallint NULL	Часовой пояс. Разница между локальным временем на машине где производилась операция резервного копирования, и мировым стандартом координатного времени (Universal Coordinated Time) в 15-минутных интервалах. Значение может находиться в диапазоне -48 .. +48. 127 соответствует неопределенному значению. Например, для русского стандарта времени это значение будет 12, получается оно следующим образом: 3*4=12, где 4 - количество 15-минутных интервалов в одном часе, 3 - разница между UDT и Russian Standard Time. Эту разницу можно увидеть если дважды щелкнуть мышью по часам в трее или в панели управления в виде: (GMT +03:00) Москва,Санкт-Перербург,Волгоград.
mtf_minor_version	tinyint NULL	Номер первой версии Microsoft Tape Format.
first_lsn	numeric(25,0) NULL	LSN& первой или самой "старой" записи журнала транзакций в резервном наборе.
last_lsn	numeric(25,0) NULL	LSN последней или "новейшей" записи в резервном наборе.
checkpoint_lsn	numeric(25,0) NULL	LSN записи , где хранится информация о запуске процесса восстановления(recovery).
database_backup_lsn	numeric(25,0) NULL	LSN записи, в которой хранится информация о последней операции полного резервного копирования.
database_creation_date	datetime NULL	Дата и время создания базы данных. Можно посмотреть так: select crdate from master..sysdatabases where name='test'
backup_start_date	datetime NULL	Дата и время начала операции резервного копирования
backup_finish_date	datetime NULL	Дата и время окончания операции резервного копирования
type	char(1) NULL	Тип резервной копии: D = полная резервная копия I = дифферинциальная резервная копия. L = копия журнала транзакций F = копия отдельных файлов или файловых групп
sort_order	smallint NULL	Порядок сортировки, определяется текущей коллацией.
code_page	smallint NULL	Кодовая страница. Можно посмотреть так: select collationproperty(cast(databasepropertyex('test','collation') as nvarchar(20)),'Codepage') если коллация для базы данных установлена по умолчанию, то нужно смотреть так: select collationproperty(cast(serverproperty('collation') as nvarchar(50)),'CodePage')
compatibility_level	tinyint NULL	Уровень совместимости для базы данных: 60 = SQL Server version 6.0. 65 = SQL Server 6.5. 70 = SQL Server 7.0 80 = SQL Server 8.0 (2000) Для более подробной информации смотрите sp_dbcmptlevel
database_version	int NULL	Версия базы данных. select databasepropertyex('test','version')
backup_size	numeric(20,0) NULL	Размер резервной копии в байтах.
database_name	nvarchar(128) NULL	Имя базы данных, для которой производилась операция резервного копирования.
server_name	nvarchar(128) NULL	Имя сервера SQL Server на котором выполнялась операция резервного копирования. SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
machine_name	nvarchar(128) NULL	Имя компьютера.
flags	int NULL	Flag bits: 1 = Резервная копия содержит минимально протоколированные данные. Backup contains minimally logged data. 2 =  использовалась опция WITH SNAPSHOT. 4 = База данных доступна только для чтения при выполнении резервного копирования. 8 = База данных находится в режиме 'single user' при выполнении резервного копирования
unicode_locale	int NULL	Идентификатор страны для символов Юникода. select collationproperty(cast(serverproperty('collation') as nvarchar(10)),'LCID') Null для коллаций SQL Server.
unicode_compare_style	int NULL	Стиль сравнения для символов Юникода. select collationproperty(cast(serverproperty('collation') as nvarchar(10)),'ComparisonStyle')
collation_name	nvarchar(128) NULL	Имя коллации select cast(databasepropertyex('test','collation') as nvarchar(20))

[В начало]

backupmediafamily

Содержит по одной строке для каждого семейства носителей.

Название поля	Тип данных	Описание
media_set_id	int NOT NULL REFERENCES backupmediaset (media_set_id)	Ссылка на поле media_set_id таблицы backupmediaset
family_sequence_number	tinyint NOT NULL	Порядковый номер семейства в наборе носителей.
media_family_id	uniqueidentifier NULL	Уникальный идентификатор семейства носителей. Соответствует значению MediaFamilyId , полученному с помощью RESTORE LABELONLY для этой резервной копии.
media_count	int NULL	Номер резервной копии в семействе.
logical_device_name	nvarchar(128) NULL	Имя устройства резервного копирования из таблицы master..sysdevices (поле logical_device_name), либо NULL для временного устройства. На значение этого поля влияет значение параметра TO < backup_device > [ ,...n] Если полный путь к файлу не проставлен в опции To < backup_device >, он проставляется по умолчанию Узнать путь по умолчанию для создания резервной копии можно так: xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
physical_device_name	nvarchar(260) NULL	Физическое имя устройства резервного копирования.
device_type	tinyint NULL	Тип устройства резервного копирования: Диск: 2 - временное. 102 - постоянное. Лента: 5 - временное. 105 - постоянное. Именованный канал: 6 - временное. 106 - постоянное. Имена постоянных устройств находятся в таблице sysdevices
physical_block_size	int NULL	Размер блока данных для записи на устройство резервного копирования, задается параметром BLOCKSIZE. Информация о допустимых размерах блока данных для конкретного устройства должна быть приведена в его документации. По умолчанию: Для дисковых устройств - 512 Для лент - 65536 Для именованного канала 65536

[В начало]

backupmediaset

Содержит по одной строке для каждого набора носителей.

Название поля	Тип данных	Описание
media_set_id	int IDENTITY PRIMARY KEY	Уникальный идентификатор набора устройств резервного копирования.
media_uuid	uniqueidentifier NULL	Номер устройства в наборе. По всей видимости значение отличное от NULL возникает в случае создания резервной копии на несколько устройств.
media_family_count	tinyint NULL	Число семейств в наборе.
name	nvarchar(128) NULL	Название набора. Заполняется при использовании опции MEDIANAME.
description	nvarchar(255) NULL	Текстовый комментарий к набору устройств. Заполняется при использовании опции MEDIADESCRIPTION
software_name	nvarchar(128) NULL	Наименование программы, создавшей заголовок на носителе.
software_vendor_id	int NULL	Идентификационный номер поставщика программного обеспечения, с помощью которого осуществляется запись заголовков на носитель. Для MS SQL Server это значение равно 4 608 в десятичной или 0х1200 в шестнадцатеричной системе счисления.
MTF_major_version	tinyint NULL	Номер версии Microsoft Tape Format, использовавшийся для создания этого набора.

Для того чтобы удалить историю бэкапов до определенной даты, существует хранимая процедура sp_delete_backuphistory из базы данных msdb, имеющая один входной параметр, определяющий дату, до которой вся история будет удалена.

Пример вызова:

exec msdb..sp_delete_backuphistory '4-mar-2004 13:00:00'

удалит всю хронологию резервных копий, сделанных до 13:00:00 4 марта.
Если же Вам необходимо удалить историю за период, либо информацию о конкретной резервной копии, можно сделать следующее:

Получаем текст процедуры sp_delete_backuphistory следующим образом:

sp_helptext 'sp_delete_backuphistory'

Затем нетрудно догадаться как нужно изменить текст, чтобы его можно было использовать для получения нужного результата.

[В начало]

Оптимизация выполнения операции резервного копирования

Все файлы, выбранные для операции резервного копирования, ассоциированы с дисковыми устройствами, список которых можно посмотреть следующим образом: Select filename from sysfiles в текущей базе данных, с каждым дисковым устройством связывается свой процесс чтения, и для каждого устройства резервного копирования порождается свой процесс записи. При увеличении количества логических файлов для базы данных, увеличивается количество операций параллельного чтения, а при увеличении числа устройств резервного копирования, соответственно увеличивается количество операций параллельной записи.
Также количество операций параллельной записи увеличивается при увеличении количества устройств резервного копирования. При этом необходимо учитывать, что значение “max worker thread” в конфигурации сервера по умолчанию установлено равным 255. Если же общее количество рабочих процессов равняется этому значению, то SQL Server не может создать новый процесс и отказывает в соединении, но в некоторых случаях он может разделить один рабочий процесс между несколькими соединениями, и это приведет к тому, что резервное копирование/восстановление на несколько устройств не будет проходить в режиме параллельного считывания / записи, а соответственно снизится скорость резервного копирования/восстановления и необходимость использования нескольких устройств потеряет смысл. В этом случае необходимо предусмотреть чтобы значение этого параметра было установлено таким образом, чтобы этого количества хватило и на соединения к серверу и на процессы записи/чтения при резервном копировании/восстановлении. Но не следует без необходимости увеличивать значение этого параметра, т.к. это может привести к снижению производительности сервера.
Уменьшение времени на создания резервной копии можно добиться, если создавать резервную копию на локальный диск, а затем уже переносить эту копию по сети на другой компьютер или ленту для хранения. Восстановление с локального диска также проходит быстрее.
Если резервная копия создается на локальный ленточный накопитель, то лучше если этот накопитель использует собственный интерфейс SCSI, а не разделенный с дисковым массивом.
Для того чтобы убедиться, что операция резервного копирования или восстановления на диск проходит с оптимальной скоростью можно измерить следующие счетчики в Performance Monitor:

• SQL Server Backup Device Object: Device Throughput Bytes/sec - Определяет количество данных, скопированных, либо восстановленных в секунду. Если значение слишком мало, необходимо подумать над тем, чтобы увеличить эффективность системы ввода/вывода.
• Phisical Disk: %Disk Time - Это значение не должно превышать 55%. Если это значение превышает 90% длительное время при выполнении операций резервирования или восстановления, то это показывает что система ввода/вывода является узким местом. Для того, чтобы определить является ли узким местом физический диск, необходимо также оценить значения счетчиков %Disk Read Time и %Disk Write Time, чтобы понять является причиной появления узкого места считывание или запись.
• Phisical Disk Object: Avg. Disk Queue Length - Если значение этого счетчика длительное время превышает 2 для каждого диска в массиве, когда проходит процесс резервного копирования или восстановления, вероятно у Вас узким местом является этот дисковый массив.

[В начало]