SQL-Ex blog

1. Вы хотите иметь возможность восстанавливать поврежденные данные или ошибки, которые делали люди, непосредственно меняющие данные - например, при написании и выполнении скрипта в SSMS, вне приложения, которое использует набор протестированных хранимых процедур.


2. Вы хотите обновить вашу среду разработки, чтобы иметь данные, подобные тем, которые находятся в промышленной базе данных.


3. Вы хотите иметь бэкапы, чтобы протестировать восстановление базы данных и выяснить, сколько времени это займет.


4. Вам нужны бэкапы, поскольку они часто представляют собой существенную часть процесса перехода с одного SQL Server на другой - обычно более новый или на Azure.

Полные резервные копии SQL Server (Full Backup)

Что выполняет полный бэкап? Полный бэкап создает резервную копию всей базы данных. Это подразумевает включение всей структуры базы данных и всех данных. Когда стартует полное резервирование, записывается Log Sequence Number (LSN - последовательный номер журнала), LSN записывается и при завершении полного резервирования. Этот LSN является механизмом, используемым SQL Server, чтобы знать, в каком порядке выполнялись операторы INSERT, UPDATE или DELETE. При этом наличие записанных LSN начала и окончания, как части полного бэкапа, обеспечивает согласованное с точки зрения транзакций резервное копирование, поскольку при полном резервном копировании учитываются изменения, произошедшие во время резервного копирования. Это обеспечивает обработку таких транзакций в процессе восстановления бэкапа. Эта полная резервная копия служит базовым бэкапом или отправной точкой для всех последующих дифференциальных резервных копий или бэкапов журналов. Всякий раз, когда создается новый полный бэкап, устанавливается новая точка отсчета бэкапов.

Дифференциальные резервные копии SQL Server

Что дает вам дифференциальный бэкап? Дифференциальное резервное копирование основывается на концепции разностного растрового изображения. SQL Server оперирует 8-килобайтными страницами. Эти страницы собраны в группы по 8, называемые "экстентами" (Extents). Имеется битовая карта, которая используется для пометки каждого экстента, который подвергался изменению данных с момента последнего полного резервирования. При выполнении дифференциального бэкапа именно эта битовая карта определяет, какие данные помещать в резервную копию.

В зависимости от размера и интенсивности изменений в базе данных дифференциальные резервные копии зачастую готовятся быстрей, чем полный бэкап. Кроме того, дифференциальные бэкапы будут меньших размеров, поскольку включают только измененные экстенты, а не всю базу данных.

Когда используется полная модель восстановления, мощь дифференциала может быть очень важна. Дифференциальный бэкап сокращает число резервных копий журнала, которые должны быть восстановлены. Например, если имеется полный бэкап, сделанный в ночь воскресенья, и после этого каждые 15 минут создавались журналы транзакций, и администратору нужно восстановить базу данных ко вторнику в 5:53, ему потребуется построить процесс восстановления на базе полного бэкапа и каждого журнала транзакция, начиная с вечера воскресенья! Это потребует включения множества журналов транзакций. При ежедневном дифференциале, выполняемом в 5 часов, администратору потребуется восстановить полный воскресный бэкап, один дифференциальный, созданный во вторник в 5 часов, и только те журналы транзакций, которые создавались после 5 часов вторника. Возможность отбросить все эти журналы транзакций и обуславливает мощь этого метода.
Это значительно упрощает создание и читабельность скрипта и может сохранить массу времени администратору при написании скрипта вручную. Вам следует иметь автоматизированный процесс создания скрипта резервирования, но некоторые администраторы еще не настроили его. Мы позже поговорим об автоматизации создании скрипта резервных копий.

Резервные копии файлов и файловых групп в SQL Server

Что такое бэкапы файлов и файловых групп? Для начала это более продвинутый вариант по сравнению с другими типами бэкапов. А раз это так, менее вероятно, что вам потребуется этот тип резервной копии. Однако знание того, какие варианты существуют, всегда полезно.

Бэкапы файлов и файловых групп позволяют получить более гранулированные резервные копии и, соответственно, более детализированный процесс восстановления. То, что делается, соответствует названию. Резервируется отдельный файл или коллекция файлов, содержащихся в файловой группе. Это позволяет восстановить небольшую часть базы данных, чтобы исправить проблему, а не восстанавливать всю базу данных. Однако этот вариант вносит сложность и, на мой взгляд, на самом деле полезен только для больших баз данных, особенно в диапазоне 500Гб и более.

Эти типы резервирования файлов и файловых групп могут использоваться, когда большая база данных разбита на множество файлов. Например, большая база данных, содержащая информацию о продажах, разбивается на различные файлы Sales, возможно, по годам или даже помесячно, если база данных содержит большой объем записей о продажах. Эти файлы затем собираются в файловые группы. Ниже приведен пример создания файлов и файловых групп в базе данных. Этот пример взят из документации MS.

USE master;

GO

-- Создание базы данных с дефолтными 

-- файловой группой для данных, 

-- файловой группой для файлового потока 

-- файла журнала. 

-- Задан максимальный размер и его приращение 

-- для первичного файла данных

CREATE DATABASE MyDB

ON PRIMARY

  ( NAME='MyDB_Primary',

    FILENAME=

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',

    SIZE=4MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

-- Здесь создается отдельный файл и

-- привязывается к файловой группе, 

-- которая несколько больше, чем контейнер.

FILEGROUP MyDB_FG1

  ( NAME = 'MyDB_FG1_Dat1',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

-- Здесь создается другой файл и 

-- привязывается к файловой группе.

  ( NAME = 'MyDB_FG1_Dat2',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB)

LOG ON

  ( NAME='MyDB_log',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',

    SIZE=1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB);

GO

ALTER DATABASE MyDB 

  MODIFY FILEGROUP MyDB_FG1 DEFAULT;

GO

-- Создается таблица в файловой группе пользователя.

USE MyDB;

CREATE TABLE MyTable

  ( cola int PRIMARY KEY,

    colb char(8) )

ON MyDB_FG1;

GO

Резервные копии журналов транзакций в SQL Server

Что такое бэкап журнала транзакций? Бэкап журнала транзакций будет создавать резервную копию журнала транзакций SQL Server, чтобы изменения данных, хранящиеся в нем, можно было воспроизвести в процессе восстановления. И здесь упомянутая ранее концепция Log Sequence Number является ключевой. Поскольку транзакции происходят в определенном порядке, бэкап журнала поддерживает этот порядок транзакций. Бэкапы журналов транзакций должны восстанавливаться по порядку. Если вы попытаетесь восстановить ряд бэкапов журналов в неверном порядке, SQL Server сгенерирует ошибку. Наличие бэкапов журналов позволяет выполнить так называемое восстановление к заданной точке во времени (point in time restore). Возможность восстановления к заданной точке времени может быть критичным для бизнеса. Предположим, что некто внес изменения непосредственно в одну или более таблиц. Если была допущена ошибка и произошло непредусмотренное изменение данных, восстановление к точке времени позволит администратору восстановить данные к моменту, предшествующему выполнению ошибочных изменений.

Только копирование резервных копий (Copy Only Backups)

Что такое копирование только бэкапов? Копирование бэкапов не влияет или не отслеживает информацию Log Sequence Number. Зачем это может понадобиться? Ну, предположим, что вы уже сделали полный, дифференциальные бэкапы и бэкапы журналов. Вас просят предоставить файл бэкапа баы данных в другой отдел или, возможно, отдельному разработчику, работающему на проектом. Этот отдел предположительно не имеет доступа к каталогу, где находятся резервные копии. Вместо того, чтобы попытаться скопировать множество бэкапов для предоставления общего доступа или размещения его в другом месте, к которому имеет доступ разработчик, вы могли бы просто создать полный бэкап Copy Only, который записывается в место общего доступа. Затем разработчик может использовать этот бэкап для восстановления на локальной машине, а вы получаете то преимущество, что не меняете базовую резервную копию для своей цепочки бэкапов.

Что дальше

1. Исследуйте свою среду на предмет использования "мощи дифференциала" для создания дифференциальных резервных копий с целью упростить процесс восстановления.


2. Узнайте размер ваших баз данных и определите, подходит ли ваша ситуация для получения выгоды от разбиения базы данных на множества файлов и файловых групп.