Полное резервное копирование SQL Server

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

Введение в полное резервное копирование SQL Server

Полная резервная копия базы данных создает резервную копию всей базы данных. Она включает в себя следующие данные:

• Метаданные базы данных, такие как имя, дата создания, параметры базы данных, пути к файлам и т. д.
• Использованные страницы данных каждого файла данных.

Также полная резервная копия включает часть журнала транзакций. Она представляет базу данных на момент завершения резервного копирования.

При выполнении полного резервного копирования SQL Server может использовать значительный объем дискового ввода-вывода. Поэтому следует выполнять полное резервное копирование в то время, когда рабочая нагрузка низкая, например, ночью.

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

Обратите внимание, что для выполнения других типов резервного копирования, таких как дифференциальное резервное копирование и резервное копирование журнала транзакций, необходимо выполнить как минимум одно полное резервное копирование.

На следующем рисунке показаны две полные резервные копии:

На этом рисунке первая полная резервная копия содержит идентификатор 1, а вторая полная резервная копия содержит идентификаторы 1, 2 и 3.

Создайте полную резервную копию базы данных с помощью T-SQL

Чтобы создать полную резервную копию базы данных, используйте оператор BACKUP DATABASE со следующим синтаксисом:

BACKUP DATABASE database_name
TO DISK = path_to_backup_file
WITH options;

В этом синтаксисе:

• database_name — имя базы данных, резервную копию которой вы хотите сделать. База данных должна существовать и нормально работать на сервере.
• path_to_backup_file — это путь к файлу резервной копии. По соглашению расширение резервной копии — bak. Если вы сохраняете файл резервной копии в папке, эта папка должна существовать, поскольку оператор не создаст папку для вас неявно.
• Параметр, следующий за ключевым словом WITH, указывает один или несколько параметров для резервного копирования.

INIT против NOINIT

Параметр INIT добавляется к существующей резервной копии файла, а параметр NOINIT добавляется к самой последней резервной копии. NOINIT является параметром по умолчанию, если вы не указали INIT или NOINIT.

ИМЯ

ИМЯ указывает имя резервной копии. По умолчанию имя резервной копии пустое.

Помимо параметров INIT, NOINIT и NAME, оператор BACKUP DATABASE имеет и другие параметры, которые мы рассмотрим в следующем руководстве.

Давайте рассмотрим пример выполнения полного резервного копирования.

Сначала создайте базу данных HR с одной таблицей под названием People и вставьте в нее четыре строки:

-- drop the HR database
USE master;
DROP DATABASE IF EXISTS HR;
-- create the HR database
CREATE DATABASE HR;
GO
-- create the People table
USE HR;
CREATE TABLE People(
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);
INSERT INTO People(FirstName, LastName)
  VALUES('John', 'Doe'),
 ('Jane', 'Doe'),
 ('Upton', 'Luis'),
 ('Dach', 'Keon');
SELECT * FROM People;

Во-вторых, используйте оператор BACKUP DATABASE для резервного копирования базы данных HR в файл hr.bak, расположенный в каталоге D:\backup\:

BACKUP DATABASE HR 
TO  DISK = 'D:\backup\hr.bak'
WITH INIT,
NAME = 'HR-Full Database Backup';

Обратите внимание, что папка D:\backup\ должна существовать до запуска резервного копирования. Оператор BACKUP DATABASE не создаст папку.

Также вы найдете файл hr.bak в папке D:\backup.

В-третьих, используйте RESTORE HEADERONLY для чтения из файла резервной копии:

RESTORE HEADERONLY   
FROM DISK ='D:\backup\hr.bak';

Выход:

Вывод содержит много информации, но наиболее важными являются:

• Имя резервной копии
• Тип резервного копирования(1 для полного резервного копирования)
• Позиция: 1
• База данных: HR
• Размер резервной копии
• Дата начала резервного копирования
• Дата окончания резервного копирования

Выполнение нескольких полных резервных копий в одном файле

SQL Server позволяет хранить несколько резервных копий в одном файле резервной копии. Для этого необходимо использовать опцию NOINIT в предложении WITH. Например:

Сначала вставьте новую строку в таблицу People:

INSERT INTO People(FirstName, LastName)
VALUES('Bob', 'Climo');

Во-вторых, выполните второе полное резервное копирование в тот же файл резервной копии:

BACKUP DATABASE HR 
TO  DISK = 'D:\backup\hr.bak'
WITH NOINIT,
NAME = 'HR-Full Database Backup';

В-третьих, проверьте файл резервной копии:

Резервный файл содержит две полные резервные копии. Значения в столбце позиции указывают порядок резервных копий. Значение 1 указывает на первую полную резервную копию, а значение 2 представляет вторую полную резервную копию.

В первой резервной копии таблица People содержит четыре строки, а во второй резервной копии таблица People содержит пять строк.

Восстановление базы данных из полной резервной копии

Для восстановления базы данных используется оператор RESTORE DATABASE.

Оператор RESTORE DATABASE

Ниже показан синтаксис оператора RESTORE DATABASE:

RESTORE DATABASE database_name
FROM DISK = path_to_backup_file
WITH options;

В этом синтаксисе:

• database_name — имя базы данных, которую необходимо восстановить.
• path_to_backup_file — путь к файлу резервной копии.
• параметры — один или несколько вариантов восстановления базы данных.

Давайте воспользуемся оператором RESTORE DATABASE для восстановления базы данных HR из полной резервной копии.

Восстановление из первой полной резервной копии

Сначала переключитесь на основную базу данных и удалите базу данных HR:

USE master;
DROP DATABASE HR;

Во-вторых, восстановите базу данных HR из первой полной резервной копии:

RESTORE DATABASE HR
FROM  DISK = N'D:\backup\hr.bak'
WITH FILE = 1;

В этом примере мы восстанавливаем базу данных HR из файла резервной копии D:\backup\hr.bak. Значение 1 в предложении WITH FILE указывает SQL Server восстановить первую полную резервную копию. Оно соответствует положению файла резервной копии.

В-третьих, переключитесь на базу данных HR и выберите данные из таблицы People для проверки:

USE hr;
SELECT * FROM People;

Выход:

Запрос возвращает четыре строки, как и ожидалось.

Восстановление из второй полной резервной копии

Сначала переключитесь на основную базу данных и удалите базу данных HR:

USE master;
DROP DATABASE HR;

Во-вторых, восстановите базу данных HR из второй полной резервной копии:

RESTORE DATABASE HR
FROM  DISK = N'D:\backup\hr.bak'
WITH FILE = 2;

Значение 2 в предложении WITH FILE указывает SQL Server восстановить вторую резервную копию из файла резервной копии.

В-третьих, переключитесь на базу данных HR и выберите данные из таблицы People для проверки:

USE hr;
SELECT * FROM People;

Выход:

Запрос возвращает пять строк, как и ожидалось.

Краткое содержание

• Используйте оператор BACKUP DATABASE для создания полной резервной копии базы данных.
• Используйте параметр WITH INIT для перезаписи резервной копии и параметр WITH NOINIT для добавления к существующему файлу резервной копии.
• Используйте оператор RESTORE DATABASE для восстановления базы данных из полной резервной копии.