Разработка ПО для резервного копирования и восстановления базы данных SQL Server

Введение

В настоящее время безопасное хранение данных представляет одну из основных проблем информационной безопасности. Согласно докладу о рисках Всемирного экономического форума 2021 года, утеря и утечка данных являются четвертым и пятым глобальными рисками, с которыми сталкивается каждая организация. По своей значимости эти риски приравниваются к экологическим проблемам. В официальном ежегодном отчете за 2019 год, опубликованном Cybersecurity Ventures, сообщается, что атаки хакеров во всём мире происходят каждые 11 секунд. Специалисты компании InfoWatch в конце года рассказали Известиям, что за прошлый год в сеть утекло более 14 млрд. конфиденциальных записей. Рост числа утечек во всём мире по сравнению с 2018 годом увеличился на 10%, в России – более чем на 40%.
Их этого можно сделать вывод, что хранение данных, должно обеспечивать их целостность. Большинство данных хранятся в базах, так как это является удобным способом взаимодействия с ними. Одной из основных технологий баз данных является Sql Server, который  является системой управления реляционными базами данных (РСУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка. Для сохранения целостности баз данных используют такие технологии как их резервное копирование и восстановление.
Глава 1. Основные принципы резервного копирования и восстановления баз данных SQL ServerРезервное копирование базы данных - это процесс загрузки данных (из базы данных, журнала транзакций или файла) на устройства резервного копирования, которые создаются и обслуживаются системой. Устройство резервного копирования может быть дисковым файлом или магнитной лентой. Компонент Database Engine обеспечивает статическое и динамическое резервное копирование. Статическое резервное копирование означает, что во время процесса резервного копирования только один активный сеанс, поддерживаемый системой, является сеансом, создающим резервную копию. Другими словами, во время копирования никакие пользовательские процессы не допускаются. Динамическое резервное копирование означает, что копирование базы данных может выполняться без остановки сервера базы данных, удаления пользователей и даже без закрытия файлов. (Пользователи даже не узнают, что идет процесс резервного копирования.)
Компонент Database Engine предоставляет четыре различных метода резервного копирования:
Полное копирование базы данных;
Дифференцированное резервное копирование;
Резервное копирование протокола транзакции;
Резервное копирование файла или файловой группы.
Полная копия базы данных фиксирует состояние базы данных, каким оно было при запуске копии. Во время полного копирования базы данных система копирует данные, а также схему всех таблиц в базе данных и соответствующие файловые структуры. Если полная копия базы данных выполняется динамически, система базы данных записывает все действия, происходящие во время процесса резервного копирования. Поэтому даже все неподтвержденные транзакции в журнале транзакций будут записаны на устройство резервного копирования.
Все типы резервных копий можно выполнять с помощью двух операторов Transact-SQL:
backup database;
backup log.
Компонент Database Engine позволяет копировать базы данных, журналы транзакций и файлы на устройства резервного копирования, такие как:
1. Диск;
2. Магнитная лента.
Дисковые файлы чаще всего используются для хранения резервных копий. Диск с копией может быть расположен на локальном жестком диске на сервере или на удаленном диске в общей сетевой папке. Компонент Database Engine позволяет добавлять новые копии в файл, который уже содержит копии той же или другой базы данных. Когда вы добавляете новый набор копий на существующий носитель, предыдущее содержимое носителя остается нетронутым, и новая копия записывается после последней копии на этом носителе. (Набор копий включает все сохраненные данные объекта, которые вы выбираете для копирования.) По умолчанию компонент Database Engine всегда добавляет новые копии в файлы на диске.
Магнитная лента обычно используется в качестве устройства хранения резервных копий так же, как и дисковый накопитель. Однако при резервном копировании на ленту ленточное устройство должно быть локально подключено к системе. Преимущество ленточных устройств перед дисковыми - простота администрирования и эксплуатации.
Сценарий восстановления — SQL Server - это процесс восстановления данных из одной или нескольких резервных копий и возврата базы данных в исходное состояние. Поддерживаемые сценарии восстановления зависят от модели восстановления базы данных и выпуска SQL Server.
Для большинства сценариев восстановления необходимо применить резервную копию журнала транзакций и позволить ядру СУБД SQL Server запустить процесс восстановления, чтобы перевести базу данных в оперативный режим. Восстановление - это процесс, используемый SQL Server для запуска каждой базы данных в транзакционно согласованном (чистом) состоянии.
В случае аварийного переключения или другого неясного завершения работы базы данных могут оставаться в состоянии, в котором некоторые изменения не были записаны из буферного кеша в файлы данных, и в файлах данных могут быть некоторые изменения, вызванные незавершенными транзакциями. Когда вы запускаете экземпляр SQL Server, выполняется трехэтапное восстановление каждой базы данных на основе самой последней контрольной точки в базе данных:
На этапе анализа анализируется журнал транзакций для определения последней контрольной точки и создается таблица грязных страниц (DTP) и таблица активных транзакций (TAT). TGS содержит записи о страницах, которые были грязными при выключении базы данных. TAT содержит записи транзакций, которые все еще были активны, когда база данных была аварийно завершена.
На этапе повтора повторяются все зарегистрированные изменения, которые, возможно, не были записаны в файлы данных, когда база данных была закрыта. Минимальный порядковый номер журнала (minLSN), необходимый для успешного восстановления всей базы данных, находится в TGS и отмечает начало операций восстановления, необходимых для всех грязных страниц. На этом этапе компонент SQL Server Database Engine записывает на диск все грязные страницы, принадлежащие зафиксированным транзакциям.
На этапе отката выполняется откат ожидающих транзакций, обнаруженных в TAT, чтобы гарантировать сохранение целостности базы данных. После отката база данных переводится в оперативный режим, и больше нельзя применять резервные копии журнала транзакций.
Цель полного восстановления - восстановить всю базу данных. База данных отключена во время периода восстановления. Перед тем, как какая-либо часть базы данных перейдет в оперативный режим, все данные восстанавливаются до точки согласованности, когда все части базы данных находятся в один и тот же момент времени и отсутствуют незавершенные транзакции.
В режиме полного восстановления после восстановления резервных копий данных необходимо восстановить все последующие резервные копии журналов транзакций, а затем и саму базу данных. База данных может быть восстановлена до определенной точки восстановления в одной из этих резервных копий журнала. Этой точкой восстановления может быть указанная дата и время, помеченная транзакция или порядковый номер журнала (LSN).
При восстановлении базы данных, особенно при использовании модели восстановления с полным или неполным протоколированием, используйте одну последовательность восстановления. Последовательность восстановления состоит из одной или нескольких операций восстановления, которые перемещают данные в одну или несколько фаз восстановления.
Глава 2. Создание программного обеспечения для резервного копирование и восстановления базы данных.При создании программного обеспечения для резервного копирования и восстановления базы данных SQL Server, использовалась среда разработки Microsoft Visual Studio 2019, язык программирования C# на Windows Forms. Структурная схема программы была составлена средствами программного обеспечения Graph Editor и представлена на Рисунке 1.

Рисунок SEQ Рисунок \* ARABIC 1. Структурная схема программы
При создании программного обеспечения использовались два основных класса:
Form.csBD.csForm.cs – является классом, в котором происходит реализация отклика графических элементов.
public partial class Form1 : Form
{
//Создание объекта класса
BD bD = BD.getInstance();
public Form1()
{
InitializeComponent();
}
//Обработчик нажатия на кнопку Подключиться
private void buttonConnect_Click(object sender, EventArgs e)
{
//Считывание значений с textBoxs string Server = textBoxNameServer.Text;
string NameUser = textBoxNameUser.Text;
string Pass = textBoxPass.Text;
//Вызов метода подключения к базе данных, который находится в классе BD
bD.Connection(Server, NameUser, Pass);
}
// Обработчик нажатия на кнопку Резервное копирование
private void buttonCopy_Click(object sender, EventArgs e)
{
// Считывание значения из textBox string NameBase = textBoxNameBase.Text;
// Вызов диалогового окна для выбора файла
FolderBrowserDialog folderDlg = new FolderBrowserDialog();
folderDlg.ShowNewFolderButton = true;
DialogResult result = folderDlg.ShowDialog();
if (result == DialogResult.OK)
{
// Присваивание переменной пути до файла
string Directory = folderDlg.SelectedPath;
// Обработчик исходов резервного копирования
int state = bD.Backup(NameBase, Directory);
switch (state)
{
case 0:
MessageBox.Show("Резервное копирование завершено");
break;
case 1:
MessageBox.Show("Ошибка резервного копирования");
break;
case 2:
MessageBox.Show("Вы не подключены к Sql Server");
break;
}
}
}
// Обработчик нажатия на кнопку Выбрать файл
private void buttonRecovery_Click(object sender, EventArgs e)
{
// Открытие диалогового окна
OpenFileDialog OPF = new OpenFileDialog();
if (OPF.ShowDialog() == DialogResult.OK)
{
// Контроль выбора файла формата .bak if (OPF.FileName.Substring(OPF.FileName.Length - 3, 3) != "bak")
{
MessageBox.Show("Вы не выбрали .bak файл");
}
else {
// Отработка метода восстановления базы данных и возвращения исхода
int state = bD.Recovery(OPF.FileName, Path.GetFileNameWithoutExtension(OPF.FileName));
switch (state)
{
case 0:
MessageBox.Show("Восстановление завершено");
break;
case 1:
MessageBox.Show("Ошибка восстановления");
break;
case 2:
MessageBox.Show("Вы не подключены к Sql Server");
break;
}
}
}

Из выше представленного фрагмента кода, можно сделать вывод, что в классе реализованы три основных метода:
Обработчик события нажатия на кнопку «Подключиться» (private void buttonConnect_Click(object sender, EventArgs e))
Обработчик события нажатия на кнопку «Резервное копирование» (private void buttonCopy_Click(object sender, EventArgs e))
Обработчик события нажатия на кнопку «Выбрать файл» (private void buttonRecovery_Click(object sender, EventArgs e))
BD.cs – является классом взаимодействия команд, созданных на языке программирования C#, с языком SQL, который используется для создания и выполнения запросов, посылаемых в SQL Server.
class BD
{
// Начало создания паттерна одиночки (singlton)
private static BD instance;
private BD()
{ }
public static BD getInstance()
{
if (instance == null)
instance = new BD();
return instance;
}
//Конец создания паттерна
//Объявление поля типа SqlConnection SqlConnection conn;
//Подключение к базе данных
public void Connection(string datasource, string nameUser, string pass)
{
//Создание строки
string connString = @"Data Source=" + datasource + ";User Id = " + nameUser + "; Password = " + pass + ";";
//Создание объекта
conn = new SqlConnection(connString);
}
//Метод резевного копирования базы данных
public int Backup(string NameBase, string Directory)
{
//Создание строки
string sqlExpression = " BACKUP DATABASE[" + NameBase + "] TO DISK = N'" + @Directory + @"\" + NameBase + ".bak' WITH NOFORMAT, NOINIT, NAME = N'" + NameBase + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
//Проверка на наличие объекта
if (conn != null)
{
try {
//Открытие соединения
conn.Open();
//Создание команды
SqlCommand command = new SqlCommand(sqlExpression, conn);
//Исполнение команды
command.ExecuteScalar();
//Закрытие соединения
conn.Close();
return 0;
}
catch {
conn.Close();
return 1;
}
}
else {
conn.Close();
return 2;
}
}
//Метод восстановления базы данных
public int Recovery(string FileNamePath, string FileName)
{
//Создание строки
string sqlExpression = " USE [master] RESTORE DATABASE["+FileName+"] FROM DISK = N'"+FileNamePath+"' WITH FILE = 1, NOUNLOAD, STATS = 5";
// Проверка на наличие объекта соединения
if (conn != null)
{
try {
// Открываем соединение
conn.Open();
// Создание объекта команды
SqlCommand command = new SqlCommand(sqlExpression, conn);
// Исполнение команды
command.ExecuteScalar();
// Закрытие соединения
conn.Close();
return 0;
}
catch {
conn.Close();
return 1;
}
}
else {
conn.Close();
return 2;
}
}
Анализируя выше представленный фрагмент кода можно сделать вывод, что в классе реализованы три основных метода:
Метод соединения с SQL Server
public void Connection(string datasource, string nameUser, string pass)
Метод резервного копирования базы данных
public int Backup(string NameBase, string Directory)
Метод восстановления базы данных
public int Recovery(string FileNamePath, string FileName)
Глава 3. Графическое описание программного обеспеченияДля графической составляющей программного обеспечения использовался механизм Windows Forms. На Рисунке 2 представлено основное окно программного обеспечения.

Рисунок SEQ Рисунок \* ARABIC 2. Основная форма программы
Данная форма делится на три блока:
Блок подключения к серверу;
Блок резервного копирования;
Блок восстановления базы данных.
Глава 4. Тестирование работы программного обеспеченияСоздадим тестовую базу данных в SQL Server:

Рисунок SEQ Рисунок \* ARABIC 3. Соединение с SQL Server через менеджер управления SQL

Рисунок SEQ Рисунок \* ARABIC 4. Тестовая база данных
Сделаем ее резервную копию через наше программное обеспечение:

Рисунок SEQ Рисунок \* ARABIC 5. Резервное копирование тестовой базы данных

Рисунок SEQ Рисунок \* ARABIC 6. Временное подтверждение резервного копирования
Удалим тестовую базу данных из списка баз данных SQL Server:

Рисунок SEQ Рисунок \* ARABIC 7. Удаление тестовой базы данных

Рисунок SEQ Рисунок \* ARABIC 8. Проверка на отсутствие тестовой базы данных
Восстановим тестовую базу данных с помощью нашего программного обеспечения:

Рисунок SEQ Рисунок \* ARABIC 9. Восстановление тестовой базы данных

Рисунок SEQ Рисунок \* ARABIC 10. Временное подтверждение восстановления тестовой базы данных.
ЗаключениеВ ходе данной работы были рассмотрены основные факторы и алгоритмы, позволяющие провести резервное копирования и восстановление базы данных SQL Server. Построена структурная схема программы, по которой было реализовано программное обеспечение, позволяющее совершать резервное копирование и восстановление базы данных, не использую среду Microsoft SQL Server Management Studio.
Список используемых источниковШилдт, Герберт. C# 4.0: полное руководство. Пер. с англ. — М: ООО «И.Д. Вильямс». Москва, 2011. — 1056 с.
Осетрова И.С., Разработка баз данных в MS SQL Server 2014. - СПб: Университет ИТМО, 2016. – 114 с.
Шарп Джон Microsoft Visual C#. Подробное руководство. 8-е изд. — СПб.: Питер, 2017. — 848 с.: ил. — (Серия «Библиотека программиста»)