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

Статья
01/09/2024

Применимо к: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), прежде чем продолжить.

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

Для резервной копии базы данных свойству 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 , нажав кнопку "Скрипт " и выбрав назначение скрипта.

После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.
Разверните узел Базы данныхи выберите пользовательскую базу данных или разверните узел Системные базы данных и выберите системную базу данных.
Щелкните правой кнопкой мыши базу данных, которую вы хотите создать резервную копию, наведите указатель на задачи и выберите команду "Создать резервную копию...".
В диалоговом окне Резервное копирование базы данных выбранная база данных приводится в раскрывающемся списке (ее можно изменить на любую другую базу данных на сервере).
В раскрывающемся списке Тип резервной копии выберите нужный вариант (по умолчанию выбран тип Полная).

Важно!

Перед тем как выполнять разностное резервное копирование или резервное копирование журналов транзакций, необходимо произвести по крайней мере одно полное резервное копирование базы данных.
В разделе Компонент резервного копирования выберите База данных.
В разделе Назначение проверьте расположение по умолчанию для файла резервной копии (в папке ../mssql/data).

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

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

Дополнительные сведения о различных параметрах резервного копирования см. на странице "Общие", странице "Параметры носителя" и странице "Параметры резервного копирования".
Чтобы начать резервное копирование, нажмите кнопку OK.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно 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 будет заархивирована на диск в папку резервных копий по умолчанию.

После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....
Нажмите ОК.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

Take SQL backup

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

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

После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....
На странице Общие в разделе Назначение выберите Диск в раскрывающемся списке Создать резервную копию на: .
Щелкайте элемент Удалить, пока не будут удалены все существующие файлы резервных копий.
Нажмите кнопку Добавить, чтобы открыть диалоговое окно Выбор места расположения резервной копии.
Введите допустимый путь и имя файла в текстовом поле Имя файла и используйте расширение .bak, чтобы упростить классификацию файла.
Щелкните ОК, а затем еще раз щелкните ОК, чтобы начать резервное копирование.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

Change DB location

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

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

После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.
Разверните узел Базы данных и узел Системные базы данных, щелкните правой кнопкой мыши базу данных master и выберите действие Создать запрос, чтобы открыть окно запроса с подключением к базе данных SQLTestDB.

Выполните приведенные ниже команды, чтобы создать главный ключ базы данных и сертификат в базе данных 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';

В обозревателе объектов в узле Базы данных щелкните правой кнопкой мыши базу данных SQLTestDB, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....
На странице Параметры носителя в разделе Перезапись носителя выберите Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных.
На странице Параметры резервного копирования в разделе Шифрование установите флажок Зашифровать резервную копию .
В раскрывающемся списке "Алгоритм" выберите AES 256.
В раскрывающемся списке Сертификат или асимметричный ключ выберите MyCertificate.
Нажмите ОК.

Encrypted backup

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

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

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

После подключения к соответствующему экземпляру ядро СУБД Microsoft SQL Server разверните дерево сервера в обозреватель объектов.
Разверните элемент Базы данных, щелкните SQLTestDB правой кнопкой мыши, наведите указатель на пункт Задачи и выберите действие Создать резервную копию....
На странице Общие в разделе Назначение выберите URL-адрес в раскрывающемся списке Создать резервную копию на: .
Нажмите кнопку Добавить, чтобы открыть диалоговое окно Выбор места расположения резервной копии.
Если ранее вы зарегистрировали контейнер службы хранилища Azure, который хотите использовать с SQL Server Management Studio, то выберите его. В противном случае щелкните Создать контейнер, чтобы зарегистрировать новый контейнер.
В диалоговом окне Соединение с подпиской Майкрософт войдите в свою учетную запись.
В текстовом поле с раскрывающимся списком Выберите учетную запись хранения выберите свою учетную запись хранения.
В текстовом поле с раскрывающимся списком Выбрать контейнер BLOB-объектов выберите контейнер больших двоичных объектов.
В поле календаря с раскрывающимся списком Политика срока действия подписанных URL-адресов выберите дату окончания срока действия для политики общего доступа, создаваемой в этом примере.
Щелкните Создать учетные данные, чтобы создать подписанный URL-адрес и учетные данные в SQL Server Management Studio.
Щелкните ОК, чтобы закрыть диалоговое окно Соединение с подпиской Майкрософт.
В текстовом поле Файл резервной копии при необходимости измените имя файла резервной копии.
Щелкните ОК, чтобы закрыть диалоговое окно Выбор места назначения резервной копии.
Чтобы начать резервное копирование, нажмите кнопку OK.
После успешного завершения резервного копирования щелкните ОК, чтобы закрыть диалоговое окно SQL Server Management Studio.

Использование 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 Management Studio

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

Примеры

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

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

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

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

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

Примеры

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

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

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

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

Примеры

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

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

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

Обратная связь

Обратная связь

Дополнительные ресурсы

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

ограничения

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

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

Разрешения

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

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

Примеры

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

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

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

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

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

Примеры

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

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

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

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

Примеры

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

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

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

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

Обратная связь

Обратная связь

Дополнительные ресурсы