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

Область применения: SQL Server

В этой статье описывается создание полной резервной копии базы данных в SQL Server с помощью SQL Server Management Studio, Transact-SQL или PowerShell.

Дополнительные сведения см. в статье SQL Server Backup and Restore with Хранилище BLOB-объектов Azure and SQL Server Backup to URL-адрес.

ограничения

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

Общие сведения о концепциях и задачах резервного копирования см. в разделе "Обзор резервного копирования" (SQL Server), прежде чем продолжить.

Рекомендации

• По мере увеличения размера базы данных полное резервное копирование занимает больше времени и требует больше дискового пространства. Для больших баз данных может потребоваться, кроме полных резервных копий, создавать также и разностные резервные копии баз данных.
• Размер полной резервной копии базы данных вы можете вычислить с помощью системной хранимой процедуры sp_spaceused .
• По умолчанию каждая успешная операция резервного копирования добавляет запись в журнал ошибок SQL Server и в журнал системных событий. Если резервные копии создаются слишком часто, сообщения об успешном завершении очень быстро накапливаются. Это приводит к увеличению журналов ошибок, затрудняя поиск других сообщений. Если работа существующих скриптов не зависит от записей журнала резервного копирования, то их можно отключить с помощью флага трассировки 3226. Дополнительные сведения см. в разделе Флаги трассировки (Transact-SQL).

Безопасность

Для резервной копии базы данных свойству TRUSTWORTHY присваивается значение OFF. Дополнительные сведения о том, как задать ЗНАЧЕНИЕ TRUSTWORTHY on, см. в разделе ALTER DATABASE SET Options (Transact-SQL) (ALTER DATABASE SET Options(Transact-SQL).

Начиная с SQL Server 2012 (11.x), параметры PASSWORD и MEDIAPASSWORD больше не доступны для создания резервных копий. Все еще вы можете восстанавливать резервные копии, созданные с паролями.

Разрешения

Разрешения BACKUP DATABASE и BACKUP LOG по умолчанию назначаются участникам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator.

Проблемы, связанные с владельцем и разрешениями у физических файлов на устройстве резервного копирования, могут помешать операции резервного копирования. Служба SQL Server должна читать и записывать на устройство. Учетная запись, в которой выполняется служба SQL Server, должна иметь разрешения на запись на устройство резервного копирования. Однако процедура sp_addumpdevice, добавляющая запись для устройства резервного копирования в системные таблицы, не проверяет разрешения на доступ к файлу. Проблемы с физическим файлом устройства резервного копирования могут не проявиться до тех пор, пока эта резервная копия не будет применена или не будет выполнена попытка восстановления.

Использование среды SQL Server Management Studio

Примечание.

При указании задачи резервного копирования с помощью SQL Server Management Studio можно создать соответствующий скрипт Transact-SQL BACKUP , нажав кнопку "Скрипт " и выбрав назначение скрипта.

1. После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.

2. Разверните узел Базы данныхи выберите пользовательскую базу данных или разверните узел Системные базы данных и выберите системную базу данных.

3. Щелкните правой кнопкой мыши базу данных, которую вы хотите создать резервную копию, наведите указатель на задачи и выберите команду "Создать резервную копию...".

4. В диалоговом окне Резервное копирование базы данных выбранная база данных приводится в раскрывающемся списке (ее можно изменить на любую другую базу данных на сервере).

5. В раскрывающемся списке Тип резервной копии выберите нужный вариант (по умолчанию выбран тип Полная).

   Внимание

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

6. В разделе Компонент резервного копирования выберите База данных.

7. В разделе Назначение проверьте расположение по умолчанию для файла резервной копии (в папке ../mssql/data).

   Чтобы выбрать другое устройство, можно использовать раскрывающийся список Создать резервную копию на. Щелкните Добавить, чтобы добавить объекты резервного копирования и (или) целевые объекты. Резервный набор данных можно перераспределить между несколькими файлами, чтобы повысить скорость резервного копирования.

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

8. (Необязательно) Просмотрите другие доступные параметры на страницах Параметры носителя и Параметры резервного копирования.

   Дополнительные сведения о различных параметрах резервного копирования см. на странице "Общие", странице "Параметры носителя" и странице "Параметры резервного копирования".

9. Чтобы начать резервное копирование, нажмите кнопку OK.

10. После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

Дополнительная информация:

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

• Также можно установить флажок Резервная копия только для копирования, чтобы создать резервную копию только для копирования. Резервная копия только для копирования — это резервная копия SQL Server, которая не зависит от последовательности обычных резервных копий SQL Server. Дополнительные сведения см. в статье Резервные копии только для копирования (SQL Server). Резервная копия только для копирования недоступна для типа резервной копии Разностная.

• При резервном копировании на URL-адрес параметр Перезаписать носитель на странице Параметры носителя недоступен.

Примеры

Для следующих примеров создайте тестовую базу данных со следующим кодом Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

А. Полное резервное копирование на диск в расположение по умолчанию

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

1. После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.

2. Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....

3. Нажмите ОК.

4. После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

B. Полное резервное копирование на диск в нестандартное расположение

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

1. После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.

2. Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....

3. На странице Общие в разделе Назначение выберите Диск в раскрывающемся списке Создать резервную копию на: .

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

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

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

7. Щелкните ОК, а затем еще раз щелкните ОК, чтобы начать резервное копирование.

8. После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

C. Создание зашифрованной резервной копии

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

1. После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.

2. Разверните узел Базы данных и узел Системные базы данных, щелкните правой кнопкой мыши базу данных master и выберите действие Создать запрос, чтобы открыть окно запроса с подключением к базе данных SQLTestDB.

3. Выполните приведенные ниже команды, чтобы создать главный ключ базы данных и сертификат в базе данных master.

   -- Create the master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
   
   -- If the master key already exists, open it in the same session that you create the certificate (see next step)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
   
   -- Create the certificate encrypted by the master key
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';  
   

4. В обозревателе объектов в узле Базы данных щелкните правой кнопкой мыши базу данных SQLTestDB, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....

5. На странице Параметры носителя в разделе Перезапись носителя выберите Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных.

6. На странице Параметры резервного копирования в разделе Шифрование установите флажок Зашифровать резервную копию .

7. В раскрывающемся списке "Алгоритм" выберите AES 256.

8. В раскрывающемся списке Сертификат или асимметричный ключ выберите MyCertificate.

9. Нажмите ОК.

D. Резервное копирование до Хранилище BLOB-объектов Azure

В этом примере создается полная резервная копия SQLTestDB базы данных для Хранилище BLOB-объектов Azure. В этом примере предполагается, что у вас уже есть учетная запись хранения с контейнером BLOB-объектов. В примере создается подписанный URL-адрес, и если у контейнера уже есть подписанный URL-адрес, операция завершится сбоем.

Если у вас нет контейнера Хранилище BLOB-объектов Azure в учетной записи хранения, создайте его перед продолжением. Дополнительные сведения см. в статье Создание учетной записи хранения и разделе Создание контейнера.

1. После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.

2. Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....

3. На странице Общие в разделе Назначение выберите URL-адрес в раскрывающемся списке Создать резервную копию на: .

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

5. Если ранее вы зарегистрировали контейнер службы хранилища Azure, который хотите использовать с SQL Server Management Studio, то выберите его. В противном случае щелкните Создать контейнер, чтобы зарегистрировать новый контейнер.

6. В диалоговом окне Соединение с подпиской Майкрософт войдите в свою учетную запись.

7. В текстовом поле с раскрывающимся списком Выберите учетную запись хранения выберите свою учетную запись хранения.

8. В текстовом поле с раскрывающимся списком Выбрать контейнер BLOB-объектов выберите контейнер больших двоичных объектов.

9. В поле календаря с раскрывающимся списком Политика срока действия подписанных URL-адресов выберите дату окончания срока действия для политики общего доступа, создаваемой в этом примере.

10. Щелкните Создать учетные данные, чтобы создать подписанный URL-адрес и учетные данные в SQL Server Management Studio.

11. Щелкните ОК, чтобы закрыть диалоговое окно Соединение с подпиской Майкрософт.

12. В текстовом поле Файл резервной копии при необходимости измените имя файла резервной копии.

13. Щелкните ОК, чтобы закрыть диалоговое окно Выбор места назначения резервной копии.

14. Чтобы начать резервное копирование, нажмите кнопку OK.

15. После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

Примечание.

Резервное копирование до Хранилище BLOB-объектов Azure в настоящее время не поддерживается с помощью управляемых удостоверений.

Использование Transact-SQL

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

• имя базы данных для создания резервной копии;
• устройство резервного копирования, на которое записывается полная резервная копия базы данных.

Базовый синтаксис Transact-SQL для полной резервной копии базы данных:

BACKUP DATABASE database TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

Вариант	Описание
database	База данных для резервного копирования.
backup_device [ ,...n ]	Указывает список от 1 до 64 устройств резервного копирования, используемых для создания резервной копии. Можно указать как физическое устройство резервного копирования, так и соответствующее логическое устройство, если оно уже определено. Для указания физического устройства резервного копирования используйте параметр DISK или TAPE. { DISK | TAPE } =имя_физического_устройства_резервного_копирования Дополнительные сведения см. в разделе Устройства резервного копирования (SQL Server).
WITH with_options [ ,...o ]	Используется для указания одного или нескольких параметров, o. Сведения о некоторых основных параметрах см. в пункте 2.

При необходимости укажите один параметр WITH или несколько. Здесь описываются некоторые основные параметры WITH. Дополнительные сведения обо всех параметрах WITH см. в статье BACKUP (Transact-SQL).

Основные параметры WITH резервного набора данных:

• { СЖАТИЕ | NO_COMPRESSION }: только в SQL Server 2008 (10.0.x) Enterprise и более поздних версий указывает, выполняется ли сжатие резервных копий для этой резервной копии, переопределяя значение по умолчанию уровня сервера.
• ШИФРОВАНИЕ (АЛГОРИТМ, СЕРТИФИКАТ СЕРВЕРА | ASYMMETRIC KEY): только в SQL Server 2014 или более поздней версии укажите используемый алгоритм шифрования и ключ сертификата или асимметричного ключа для защиты шифрования.
• DESCRIPTION = { "text'text_variable@ | }: задает текст свободной формы, описывающий резервный набор данных. В этой строке может содержаться до 255 символов.
• NAME = { backup_set_name backup_set_name_var@ | }: указывает имя резервного набора данных. Длина имени не может превышать 128 символов. Если имя не указано, оно остается пустым.

По умолчанию команда BACKUP добавляет резервную копию в существующий набор носителей, сохраняя существующие резервные наборы данных. Чтобы явно задать значение, используйте параметр NOINIT. Сведения о добавлении к существующим резервным наборам см. в разделе "Наборы носителей", "Семейства носителей" и "Резервные наборы" (SQL Server).

Чтобы отформатировать носитель резервной копии, используйте параметр FORMAT:

FORMAT [ , MEDIANAME = { media_name media_name_variable | @ } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Используйте предложение FORMAT при первом обращении к носителю или при необходимости перезаписать все существующие данные. При необходимости назначьте новому носителю имя и описание.

Внимание

Будьте предельно осторожны, используя предложение FORMAT инструкции BACKUP, так как оно удаляет все резервные копии, сохраненные ранее на носителе резервных копий.

Примеры

Для следующих примеров создайте тестовую базу данных со следующим кодом Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

А. Резервное копирование на дисковое устройство

В следующем примере производится резервное копирование всей базы данных SQLTestDB на диск и создание нового набора носителей с помощью параметра FORMAT .

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Резервное копирование на ленточное устройство

В следующем примере создается полная резервная копия базы данных SQLTestDB на ленте в дополнение к предыдущим резервными копиям.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. Резервное копирование на логическое ленточное устройство

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

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

Использование PowerShell

Используйте командлет Backup-SqlDatabase . Чтобы явно указать, что это полная резервная копия базы данных, задайте параметр -BackupAction со значением Database, которое используется по умолчанию. Данный параметр является необязательным для полных резервных копий баз данных.

Примечание.

Для этих примеров требуется модуль SqlServer. Чтобы определить, установлен ли он, выполните команду Get-Module -Name SqlServer. Чтобы установить его, выполните команду Install-Module -Name SqlServer в сеансе PowerShell с правами администратора.

Дополнительные сведения см. в статье SQL Server PowerShell Provider.

Внимание

При открытии окна PowerShell из SQL Server Management Studio для подключения к установке SQL Server учетные данные можно опустить, так как для установки подключения между PowerShell и экземпляром SQL Server автоматически используются ваши учетные данные в SSMS.

Примеры

А. Полная резервная копия (локальная)

В следующем примере создается полная резервная копия базы данных <myDatabase> в заданном по умолчанию расположении резервного копирования на экземпляре сервера Computer\Instance. Дополнительно в этом примере указывается параметр -BackupAction Database.

Полные примеры синтаксиса см. в документации по Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Полная резервная копия в Azure

В следующем примере создается полная резервная копия базы данных <myDatabase> на экземпляре <myServer> в Хранилище BLOB-объектов Azure. Хранимая политика доступа была создана с правами на чтение, запись и составление списков. Учетные данные SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, были созданы с использованием подписанного URL-адреса, который связан с хранимой политикой доступа. Команда PowerShell использует параметр BackupFile для указания расположения (URL-адреса) и имени файла резервной копии.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Связанные задачи

• Резервное копирование базы данных (SQL Server)
• Создание разностной резервной копии базы данных (SQL Server)
• Restore a Database Backup Using SSMS
• Восстановление резервной копии базы данных в простой модели восстановления (Transact-SQL)
• Восстановление базы данных до точки отказа в модели полного восстановления (Transact-SQL)
• Восстановление базы данных в новое расположение (SQL Server)
• Использование мастера планов обслуживания

Связанный контент

• Устранение неполадок операций резервного копирования и восстановления SQL Server
• Общие сведения о резервном копировании (SQL Server)
• Резервные копии журналов транзакций (SQL Server)
• Наборы носителей, семейства носителей и резервные наборы данных (SQL Server)
• sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Архивация базы данных (страница "Общие")
• Архивация базы данных (страница "Параметры резервного копирования")
• Разностные резервные копии (SQL Server)
• Полные резервные копии баз данных (SQL Server)