Name already in use
sql-docs / docs / relational-databases / backup-restore / create-a-full-database-backup-sql-server.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Create a Full Database Backup
This topic describes how to create a full database backup in [!INCLUDEssnoversion] using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell.
Limitations and restrictions
- The BACKUP statement isn’t allowed in an explicit or implicit transaction.
- Backups created by more recent version of [!INCLUDEssNoVersion] cannot be restored in earlier versions of [!INCLUDEssNoVersion].
For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.
- As a database increases in size, full database backups take more time to complete and require more storage space. For large databases, consider supplementing full database backups with a series of differential database backups.
- Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
- By default, every successful backup operation adds an entry in the [!INCLUDEssNoVersion] error log and in the system event log. If you back up frequently, success messages will accumulate quickly, resulting in huge error logs, making finding other messages difficult. In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).
TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).
Beginning with [!INCLUDEssSQL11], the PASSWORD and MEDIAPASSWORD options are no longer available for creating backups. You can still restore backups created with passwords.
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device’s physical file can interfere with a backup operation. The [!INCLUDEssNoVersion] service should read and write to the device. The account under which the [!INCLUDEssNoVersion] service runs must have write permissions to the backup device. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, doesn’t check file access permissions. Problems in the backup device’s physical file may not appear until the backup is used or a restore attempted.
Using SQL Server Management Studio
[!NOTE] When you specify a backup task by using [!INCLUDEssManStudioFull], you can generate the corresponding [!INCLUDEtsql] BACKUP script by clicking the Script button and selecting a script destination.
After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], in Object Explorer, expand the server tree.
Expand Databases, and either select a user database or expand System Databases and select a system database.
Right-click the database that you wish to backup, point to Tasks, and then select Back Up. .
In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).
In the Backup type drop-down list, select a backup type — the default is Full.
[!IMPORTANT] You must perform at least one full database backup before you can perform a differential or a transaction log backup.
Under Backup component, select Database.
In the Destination section, review the default location for the backup file (in the ../mssql/data folder).
You can use the Back up to drop-down list to select a different device. Select Add to add backup objects and or destinations. You can stripe the backup set across multiple files for increased backup speed.
To remove a backup destination, select it and select Remove. To view the contents of an existing backup destination, select it and select Contents.
(optional) Review the other available settings under the Media Options and Backup Options pages.
For more information about the various backup options, see General page, Media options page, and Backup options page.
Select OK to start the backup.
When the backup completes successfully, select OK to close the SQL Server Management Studio dialog box.
After creating a full database backup, you can create a differential database backup or a transaction log backup.
(optional) You can select the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a [!INCLUDEssNoVersion] backup that is independent of the sequence of conventional [!INCLUDEssNoVersion] backups. For more information, see Copy-Only Backups (SQL Server). A copy-only backup isn’t available for the Differential backup type.
The Overwrite media option is disabled on the Media Options page if you’re backing up to a URL.
For the following examples, create a test database with the following Transact-SQL code:
A. Full back up to disk to default location
In this example, the SQLTestDB database will be backed up to disk at the default backup location.
After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], in Object Explorer, expand the server tree.
Expand Databases, right-click SQLTestDB , point to Tasks, and then select Back Up. .
Select OK.
When the backup completes successfully, select OK to close the SQL Server Management Studio dialog box.
B. Full back up to disk to non-default location
In this example, the SQLTestDB database will be backed up to disk at a location of your choice.
After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], in Object Explorer, expand the server tree.
Expand Databases, right-click SQLTestDB , point to Tasks, and then select Back Up. .
On the General page in the Destination section select Disk from the Back up to: drop-down list.
Select Remove until all existing backup files have been removed.
Select Add and the Select Backup Destination dialog box will open.
Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.
Select OK and then select OK again to start the backup.
When the backup completes successfully, select OK to close the SQL Server Management Studio dialog box.
C. Create an encrypted backup
In this example, the SQLTestDB database will be backed up with encryption to the default backup location.
After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], in Object Explorer, expand the server tree.
Expand Databases, expand System Databases, right-click master , and select New Query to open a query window with a connection to your SQLTestDB database.
Execute the following commands to create a database master key and a certificate within the master database.
In Object Explorer, in the Databases node, right-click SQLTestDB , point to Tasks, and then select Back Up. .
On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
On the Backup Options page in the Encryption section select the Encrypt backup check box.
From the Algorithm drop-down list, select AES 256.
From the Certificate or Asymmetric key drop-down list select MyCertificate .
Select OK.
D. Back up to Azure Blob Storage
The example creates a full database backup of SQLTestDB to Azure Blob Storage. The example assumes that you already have a storage account with a blob container. The example creates a shared access signature for you; this example fails if the container has an existing shared access signature.
If you don’t have an Azure Blob Storage container in a storage account, create one before continuing. See Create a general purpose storage account and Create a container.
After connecting to the appropriate instance of the [!INCLUDEmsCoName] [!INCLUDEssDEnoversion], in Object Explorer, expand the server tree.
Expand Databases, right-click SQLTestDB , point to Tasks, and then select Back Up. .
On the General page in the Destination section select URL from the Back up to: drop-down list.
Select Add and the Select Backup Destination dialog box will open.
If you’ve previously registered the Azure storage container that you wish to use with SQL Server Management Studio, select it. Otherwise, select New container to register a new container.
In the Connect to a Microsoft Subscription dialog box, sign in to your account.
In the Select Storage Account drop-down text box, select your storage account.
In the Select Blob Container drop-down text box, select your blob container.
In the Shared Access Policy Expiration drop-down calendar box, select an expiration date for the shared access policy that you create in this example.
Select Create Credential to generate a shared access signature and credential in SQL Server Management Studio.
Select OK close the Connect to a Microsoft Subscription dialog box.
In the Backup File text box, modify the name of the backup file (optional).
Select OK to close the Select a backup destination dialog box.
Select OK to start the backup.
When the backup completes successfully, select OK to close the SQL Server Management Studio dialog box.
Create a full database backup by executing the BACKUP DATABASE statement to create the full database backup, specifying:
- The name of the database to back up.
- The backup device where the full database backup is written.
The basic [!INCLUDEtsql] syntax for a full database backup is:
BACKUP DATABASE database TO backup_device [ ,. n ] [ WITH with_options [ ,. o ] ] ;
< DISK | TAPE >=physical_backup_device_name
Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP (Transact-SQL).
Basic backup set WITH options:
- : In [!INCLUDEssEnterpriseEd10] and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY): In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
- DESCRIPTION=< ‘text‘ | @text_variable >: Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
- NAME = < backup_set_name | @backup_set_name_var >: Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME isn’t specified, it’s blank.
By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. To explicitly specify, use the NOINIT option. For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).
To format the backup media, use the FORMAT option:
FORMAT [ , MEDIANAME = < media_name | @media_name_variable > ] [ , MEDIADESCRIPTION = < text | @text_variable > ]
Use the FORMAT clause when you’re using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
[!IMPORTANT] Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.
For the following examples, create a test database with the following Transact-SQL code:
A. Back up to a disk device
The following example backs up the complete SQLTestDB database to disk, by using FORMAT to create a new media set.
B. Back up to a tape device
The following example backs up the complete SQLTestDB database to tape, appending the backup to the previous backups.
C. Back up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.
Use the Backup-SqlDatabase cmdlet. To explicitly indicate a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.
[!NOTE] These examples require the SqlServer module. To determine if it is installed, run Get-Module -Name SqlServer . To install, run Install-Module -Name SqlServer in an administrator session of PowerShell.
For more information, see SQL Server PowerShell Provider.
[!IMPORTANT] If you are opening a PowerShell window from within SQL Server Management Studio to connect to an installation of SQL Server, you can omit the credential portion as your credential in SSMS is automatically used to establish the connection between PowerShell and your SQL Server instance.
A. Full backup (local)
The following example creates a full database backup of the <myDatabase> database to the default backup location of the server instance Computer\Instance . Optionally, this example specifies -BackupAction Database.
Путеводитель по резервному копированию баз данных
– О, никакое убежище не выдержит попадания метеорита. Но ведь у вас, как и у каждого, есть резерв, так что можете не беспокоиться.
Станислав Лем, «Звёздные дневники Ийона Тихого»
Резервным копированием называется сохранение копии данных где-то вне основного места их хранения.
Главное назначение резервного копирования – восстановление данных после их потери. В связи с этим нередко приходится слышать, что при наличии реплики базы данных с неё всегда можно восстановить данные, и резервное копирование не нужно. На самом деле резервное копирование позволяет решить как минимум три задачи, которые не могут быть решены при помощи реплики, да и реплику без резервной копии не инициализировать.
Во-первых, резервная копия позволяет восстановить данные после логической ошибки. Например, бухгалтер удалил группу проводок или администратор БД уничтожил табличное пространство. Обе операции абсолютно легитимны с точки зрения базы данных, и процесс репликации воспроизведёт их в базе-реплике.
Во-вторых, современные СУБД – весьма надёжные программные комплексы, однако изредка всё же происходит повреждение внутренних структур базы данных, после которого доступ к данным пропадает. Что особенно обидно, такое нарушение происходит обычно при высокой нагрузке или при установке какого-нибудь обновления. Но как высокая нагрузка, так и регулярные обновления говорят о том, что база данных – отнюдь не тестовая, и данные, хранящиеся в ней, ценны.
Наконец, третья задача, решение которой требует наличия резервной копии, – это клонирование базы, например, для целей тестирования.
Резервное копирование баз данных так или иначе базируется на одном из двух принципов:
- Выборка данных с последующим сохранением в произвольном формате;
- Снимок состояния файлов БД и сохранение журналов.
Выгрузка данных
В наборе утилит, прилагающихся к любой СУБД, обязательно есть инструменты для выгрузки и загрузки данных. Данные сохраняются либо в текстовом формате, либо в двоичном формате, специфичном для конкретной СУБД. В таблице ниже приведён список таких инструментов:
| Двоичный формат | Текстовый формат | |
|---|---|---|
| Oracle | DataPump Export/DataPump Import Export/Import |
SQL*Plus/SQL*Loader |
| PostgreSQL | pg_dump, pg_dumpall/pg_restore | pg_dump, pg_dumpall/psql |
| Microsoft SQL Server | bcp | bcp |
| DB2 | unload/load | unload/load |
| MySQL | mysqldump, mysqlpump/mysql, mysqlimport | |
| MongoDB | mongodump/mongorestore | mongoexport/mongoimport |
| Cassandra | nodetool snapshot/sstableloader | cqlsh |
Текстовый формат хорош тем, что его можно редактировать или даже создавать внешними программами, а двоичный в свою очередь хорош тем, что позволяет быстрее выгружать и загружать данные за счёт распараллеливания загрузки и экономии ресурсов на преобразовании форматов.
Несмотря на простоту и очевидность идеи выгрузки данных, для резервирования нагруженных промышленных баз такой метод применяют редко. Вот причины, по которым выгрузка не подходит для полноценного резервного копирования:
- процесс выгрузки создаёт значительную нагрузку на систему-источник;
- выгрузка занимает много времени – к моменту окончания выгрузки она станет уже неактуальной;
- сделать согласованную выгрузку всей базы данных при высокой нагрузке практически невозможно, поскольку СУБД вынуждена хранить снимок своего состояния на момент начала выгрузки. Чем больше транзакций совершено с момента начала выгрузки, тем больше объём снимка (неактуальных копий данных в PostgreSQL, пространства undo в Oracle, tempdb в Microsoft SQL Server и т. п.);
- выгрузка сохраняет логическую структуру данных, но не сохраняет их физическую структуру – параметры физического хранения таблиц, индексы и др.; восстановление индексов при загрузке может занимать значительное время.
- высокая избирательность: можно выгрузить отдельные таблицы, отдельные поля и даже отдельные строки;
- выгруженные данные можно загрузить в базу данных другой версии, а если выгрузка сделана в текстовом формате, то и в другую базу данных.
Самым же распространённым методом резервного копирования баз данных является копирование файлов базы.
«Холодное» сохранение файлов БД
Очевидная идея – остановить базу данных и скопировать все её файлы. Такая резервная копия называется «холодной». Способ крайне надёжный и простой, но у него есть два очевидных недостатка:
- из «холодной» резервной копии можно восстановить только то состояние базы данных, которое было в момент останова; транзакции, сделанные после рестарта базы, в «холодную» резервную копию не попадут;
- далеко не у каждой базы данных есть технологическое окно, когда базу можно остановить.
- «холодная» копия иногда должна включать в себя и журналы. Методы определения журналов, которые должны попасть в «холодную» копию, индивидуальны для каждой СУБД. Например, в Oracle необходимо скопировать так называемые online redo, то есть фиксированное количество журнальных файлов в специальном каталоге, причём даже тогда, когда база остановлена корректно. В PostgreSQL нужно сохранить все журналы начиная с журнала, содержащего последнюю контрольную точку, информация о которой содержится в управляющем файле.
- каталог базы данных может содержать достаточно большие файлы временных табличных пространств, которые не обязательно включать в резервную копию. Кстати, это замечание верно и для «горячего» резервного копирования.
«Горячее» сохранение файлов
Большинство резервных копий современных баз данных выполняется путём копирования файлов базы данных без остановки базы. Здесь видны несколько проблем:
- В момент начала копирования содержимое базы данных может не совпадать с содержимым файлов, т. к. часть информации находится в кеше и ещё не записана на диск.
- Во время копирования содержимое базы может меняться. Если используются изменяемые структуры данных, то меняется содержимое файлов, а при использовании неизменяемых структур меняется набор файлов: новые файлы появляются, а старые удаляются.
- Поскольку запись данных в базу и чтение файлов БД никак не синхронизированы, программа резервного копирования может прочитать некорректную страницу, в которой половина будет от старой версии страницы, а другая половина – от новой.
- в Oracle это отдельная команда ALTER DATABASE/TABLESPACE BEGIN BACKUP;
- в PostgreSQL – функция pg_start_backup();
- в Microsoft SQL Server и DB2 подготовка к резервному копированию выполняется неявно в процессе выполнения команды BACKUP DATABASE;
- в MySQL Enterprise, Percoba Server, Cassandra и MongoDB подготовка неявно выполняется внешней утилитой – mysqlbackup, Percona XtraBackup, OpsCenter и Ops Manager соответственно.
Вот как выглядит подготовка к резервному копированию в СУБД с изменяемыми дисковыми структурами, т. е. во всех традиционных дисковых реляционных системах:
- Запоминается момент начала резервного копирования; резервная копия должна будет содержать журналы базы данных начиная с этого момента.
- Выполняется контрольная точка, то есть все изменения, которые произошли в страницах данных до запомненного момента, сбрасываются на диск. Это гарантирует, что журналы до момента начала резервного копирования при восстановлении не потребуются.
- Включается особый режим журналирования: если страница данных изменилась в первый раз после загрузки с диска, то вместо того, чтобы записывать в журнал изменения страницы, база запишет туда страницу целиком. При выполнении подготовительной процедуры все страницы вытесняются на диск, и поэтому при первом изменении блок всегда будет записан в журнал целиком. Но если в процессе резервного копирования страница снова будет вытеснена на диск, то следующее её изменение также приведёт к появлению в журнале полной копии страницы. Это гарантирует, что если вдруг при копировании файла с данными страница получится некорректной, применение журнала сделает его корректной вновь.
- Блокируется изменение заголовков файлов данных, то есть той его части, изменения которой не отражаются в журналах. Это гарантирует, что заголовок будет скопирован корректно, а потом к файлу данных корректно будут применены журналы.
По окончании резервного копирования нужно перевести базу данных обратно в обычное состояние. В Oracle это делается командой ALTER DATABASE/TABLESPACE END BACKUP, в PostgreSQL – вызовом функции pg_stop_backup(), а в других базах – внутренними подпрограммами соответствующих команд или внешних сервисов.
Вот как выглядит временнáя диаграмма процесса резервного копирования:
- Подготовка к резервному копированию (begin backup) занимает время, иногда значительное. Даже если используются зеркальные тома или файловые системы с возможностью изготовления снимков, процесс резервного копирования не будет мгновенным.
- Вместе с файлами данных необходимо сохранить журналы начиная с момента начала подготовки к резервному копированию и заканчивая моментом возврата базы в нормальное состояние.
- Восстановиться из этой резервной копии можно на момент возврата базы в нормальное состояние. Восстановление на более ранний момент невозможно.
- Данные из памяти сбрасываются на диск.
- Фиксируется список файлов, попадающих в резервную копию. До тех пор, пока процесс резервного копирования не закончится, базе запрещено удалять эти файлы, даже если они становятся не нужны.
Восстановление на точку
Резервная копия позволяет восстановить состояние базы данных на момент, когда завершилась команда возврата из режима резервного копирования. Однако авария, после которой потребуется восстановление, может произойти в любой момент. Задача восстановления состояния БД на произвольный момент называется «восстановлением на точку» (point-in-time recovery).
Чтобы обеспечить такую возможность, следует сохранять журналы БД начиная с момента окончания резервного копирования, а в процессе восстановления продолжить применять журналы к восстановленной копии. После того, как БД восстановлена из резервной копии на момент окончания копирования, состояние базы (файлов и кэшированных страниц) гарантированно корректно, поэтому особый режим журналирования не нужен. Применяя журналы до нужного момента, можно получить состояние базы данных на любую точку во времени.
Если скорость восстановления резервной копии ограничена лишь пропускной способностью диска, то скорость применения журналов обычно ограничена производительностью процессора. Если в основной базе данных изменения происходят параллельно, то при восстановлении все изменения выполняются последовательно – в порядке чтения из журнала. Таким образом время восстановления линейно зависит от того, насколько далеко точка восстановления отстоит от точки окончания резервного копирования. Из-за этого приходится довольно часто делать полные резервные копии – минимум раз в неделю для баз с небольшой транзакционной нагрузкой и до ежедневного копирования высоконагруженных баз.
Инкрементальное резервное копирование
Чтобы ускорить восстановление на точку, хотелось бы иметь возможность выполнять резервное копирование как можно чаще, но при этом не занимать лишнего места на дисках и не нагружать базу задачами резервного копирования.
Решение задачи – инкрементальное резервное копирование, то есть копирование только тех страниц данных, которые изменились с момента предыдущего резервного копирования.
Инкрементальное резервное копирование имеет смысл только для СУБД, использующих изменяемые структуры данных.
Инкремент может отсчитываться как от полной резервной копии (кумулятивная копия), так и от любой предыдущей копии (дифференциальная копия).
К сожалению, единой терминологии не существует, и разные производители используют разные термины:
| Дифференциальная | Кумулятивная | |
|---|---|---|
| Oracle | Differential | Cumulative |
| PostgresPro | Incremental | — |
| Microsoft SQL Server | — | Differential |
| IBM DB2 | Delta | Incremental |
| MySQL Enterprise | Incremental | Differential |
| Percona Server | Incremental | |
При наличии инкрементальных копий процесс восстановления на точку выглядит следующим образом:
- восстанавливается последняя полная резервная копия, сделанная до момента восстановления;
- поверх полной копии восстанавливаются инкрементальные копии;
- накатываются журналы с точки начала резервного копирования до точки восстановления.
Есть три способа создания инкрементальной копии:
- создание полной копии и вычисление разницы с предыдущей полной копией;
- разбор журналов, создание списка изменённых страниц и резервирование страниц, включённых в список;
- запрос изменённых страниц в базе данных.
Второй и третий способ отличаются механизмом определения списка изменённых страниц. Разбор журналов более ресурсоёмкий, плюс для его реализации необходимо знать структуру журнальных файлов. Спросить у самой базы, какие именно страницы изменились, проще всего, но для этого ядро СУБД должно иметь функциональность отслеживания изменённых блоков (block change tracking).
Впервые функциональность инкрементального резервного копирования была создана в ПО Oracle Recovery Manager (RMAN), появившемся в релизе Oracle 8i. Oracle сразу реализовал отслеживание изменённых блоков, поэтому необходимости в разборе журналов нет.
PostgreSQL не отслеживает изменённые блоки, поэтому утилита pg_probackup, разработанная российской компанией Postgres Professional, определяет изменённые страница путём анализа журнала. Однако компания поставляет и СУБД PostgresPro, которая включает расширение ptrack, отслеживающее изменение страниц. При использовании pg_probackup с СУБД PostgresPro утилита запрашивает изменённые страницы у самой базы – точно так же, как и RMAN.
Microsoft SQL Server так же, как и Oracle, отслеживает изменённые страницы, но команда BACKUP позволяет делать только полные и кумулятивные резервные копии.
В DB2 есть возможность отслеживания измененных страниц, но по умолчанию она выключена. После включения DB2 позволит делать полные, дифференциальные и кумулятивные резервные копии.
Важное отличие описанных в этом разделе средств (кроме pg_probackup) от файловых средств резервного копирования в том, что они запрашивают образы страниц у базы данных, а не читают данные с диска самостоятельно. Недостаток такого подхода – небольшая дополнительная нагрузка на базу. Однако этот недостаток с лихвой компенсируется тем, что прочитанная страница всегда корректна, поэтому нет необходимости во включении на время резервного копирования особого режима журналирования.
Ещё раз обратите внимание, что наличие инкрементальных копий не отменяет требований к наличию журналов для восстановления на произвольную точку во времени. Поэтому в промышленных базах данных журналы постоянно переписываются на внешний носитель, а резервные копии, полные и/или инкрементальные, создаются по расписанию.
Наилучшей на сегодня реализацией идеи инкрементального резервного копирования является программно-аппаратный комплекс (в терминологии Oracle – engineered system) Zero Data Loss Recovery Appliance – специализированное решение Oracle для резервного копирования собственной БД. Комплекс представляет собой кластер серверов с большим объёмом дисков, на которые установлена модифицированная версия ПО Recovery Manager и может работать как с другими программно-аппаратными комплексами Oracle (Database Appliance, Exadata, SPARC Supercluster), так и с базами Oracle на традиционной инфраструктуре. В отличие от «обычного» RMAN, в ZDLRA реализована концепция «вечного инкремента» (incremental forever). Система единственный раз создаёт полную копию базы данных, а потом делает только инкрементальные копии. Дополнительные модули RMAN позволяют объединять копии, создавая новые полные копии из инкрементальных.
К чести российских разработчиков нужно заметить, что и pg_probackup умеет объединять инкрементальные копии.
В отличие от многих похожих вопросов, вопрос «какой метод резервного копирования лучше» имеет однозначный ответ – лучше всего родная для используемой СУБД утилита, обеспечивающая возможность инкрементального копирования.
Для администратора БД гораздо более важными являются вопросы выбора стратегии резервного копирования и интеграция средств резервирования баз данных в корпоративную инфраструктуру. Но эти вопросы выходят за рамки данной статьи.
Особенности резервного копирования баз данных MS SQL
Резервному копированию баз данных в MS SQL отводится огромное значение. Правильно настроенное, оно поможет уберечь базу данных от повреждений и даже потери. Уделите несколько минут прочтению статьи и напомните (или узнайте) важные аспекты работы по бэкапам, а также как грамотно настроить в MS SQL Server резервное копирование баз данных. Это оградит от многих проблем.
Зачем это нужно
Грамотная отладка резервного копирования баз данных (БД) очень важна. Она помогает сохранить БД в случае каких-либо сбоев (программных, технических и т.д.), повреждений или разрушений в месте хранения. Особенно важно делать копии баз данных владельцам собственных онлайн-проектов, размещенных их на хостинге у провайдера. Бэкап станет «спасательным кругом» на тот случай, если будут утрачены важные данные.
Специалисты рекомендуют настраивать регулярное копирование, используя различные его типы. Лучше всего сохранять копии за последние семь дней.
Пользователь должен понимать, что механизмы резервного копирования БД не смогут выполнять его онлайн, в реальном времени. Для достижения этих целей существуют иные технологии.
Способы создания
В MS SQL Server резервные копии можно создавать несколькими способами. Рассмотрим используемые инструменты:
MS SQL Management Studio
Графический интерфейс SSMS прекрасно подходит для разовых операций. Он может применяться к различным базам данных.
Для этого необходимо:
- Открыть MS SQL Management Studio. Выбрать БД, которая будет копироваться и кликнуть по ней правой кнопкой мыши. Выбрать Задачи, после чего – Создать резервную копию.
- Откроется окошко, в котором необходимо оставить полный тип копий и прописать путь к резервному файлу. Если возникла необходимость – путь можно изменить, удалить, создать новый. Файл можно сохранить как на локальном диске, так и на сетевом.
- После успешного окончания процесса появится уведомление об этом.
Командная строка (sqlcmd)
Создание бэкапов с помощью командной строки sqlcmd используется для автоматизированного копирования любых данных. Может применяться в Windows и Linux.
Для данного способа понадобится утилита sqlcmd:
sqlcmd -S <server> -U <user> -P <password> -Q «BACKUP DATABASE [<database>] TO DISK = N'<file path>’ <options>»
Чтобы автоматизировать скрипт, следует воспользоваться планировщиком. В нем необходимо создать задание по его запуску согласно расписанию.
PowerShell
Создание бэкапов с помощью PowerShell может использоваться только на новых системах, т.к. на старых он не доступен. Специалисты рекомендуют использовать именно этот способ резервирования данных.
Для бэкапа необходимо импортировать модуль import-module sqlps –DisableNameChecking.
Можно воспользоваться синтаксисом: Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>
Как и в предыдущем способе, для запуска скрипта по графику, его следует размещать в планировщике.
Типы резервного копирования
В Microsoft SQL Server принята практика разных типов резервного копирования. Пользователям доступно:
- полное – делается резервная копия всей БД. Может выполняться различными способами;
- дифференциальное или разностное – осуществляется копирование данных с того момента, когда осуществлялось ее последнее полное резервирование. Осуществляется специальной командой с добавлением опции DIFFERENTIAL;
- логов или инкрементальное.
Рассмотрим их подробнее.
Полное копирование (Full Backup)
В SQL Server под полным копированием (Full Backup) понимают создание полной резервной БД, включая все данные и объекты системных таблиц. Полный бэкап не усекает (truncate) журнала транзакций. Она является основным типом создания бэкапов, которое должно предшествовать любому типами резервирования.
Полный бэкап выполняется с помощью различных инструментов (SSMS, T-SQL, PowerShell). Восстановить базу можно всего за один шаг, т.к. для этого не потребуется иных копий (разностных или инкрементальных).
Для полного копирования данных действует ряд ограничений:
- Если оно производилось поздними версиями SQL Server, то такие копии невозможно восстановить в более ранних версиях.
- В транзакциях, как в явных, так и неявных, инструкция BACKUP недопустима.
- С ростом объемов БД полный бэкап может требовать не только большего времени на выполнение операции, но и увеличения места на дисковом пространстве. Потому для больших баз данных рекомендуется проводить не только полное, но и дифференциальное копирование. Но стоит помнить, что прежде чем запускать его либо начинать формирование резервных копий журнала транзакций, следует осуществить хотя бы один полный бэкап данных.
- Системная хранимая процедура sp_spaceused поможет узнать размеры полного бэкапа базы данных.
- Все операции копирования, завершившиеся удачно, заканчиваются добавлением записи в журналы: системных событий и ошибок служб SQL Server. Частое формирование таких копий ведет к накапливанию подобных записей. Это чревато увеличением размеров журналов и затрудняет поиск иных сообщений. Потому, в тех случаях, когда записи журналов не влияют на эффективность скриптов, их можно вообще отключить. Для этого следует воспользоваться флажком трассировки 3226.
- Свойству TRUSTWORTHY в процессе создания резервной копии базы будет присвоено значение OFF.
- В версиях, начиная с SQL Server 2012 (11.x) и более поздних, не поддерживаются параметры PASSWORD и MEDIAPASSWORD. Но восстановление копий с паролями доступно.
Для пользователей, имеющих роли sysadmin, db_owner, db_backupoperator, по умолчанию доступны BACKUP DATABASE и BACKUP LOG. Помешать выполнению резервирования могут проблемы как с владельцем, так и с разрешениями у физических файлов на устройстве.
Полное копирование в SSMS
Создавая задачу в MSSQL Server Management Studio, можно сформировать соответствующий скрипт T-SQL BACKUP. Для этого следует нажать кнопку Скрипт, а затем указать его назначение.
- Когда в SQL Server компонент Database Engine подключится к нужному экземпляру Microsoft, следует развернуть в обозревателе дерево сервера.
- Если нужна пользовательская база данных, то следует развернуть узел База данных. Если потребуется обозначить системную БД – развернуть соответствующий узел.
- Отобрав базу данных, которая будет копироваться, щелкнуть на ней мышкой, выбрать Задачи и затем команду Создать резервную копию… .
- В появившемся окошке отобранная БД будет приведена в виде раскрывающегося списка. При необходимости, она может быть изменена на любую другую базу данных.
- В раскрывшемся списке Тип резервной копии следует выбрать необходимый вариант. По умолчанию выбран тип Полная.
- В разделе Компонент резервного копирования выбрать База данных.
- Произвести проверку, куда сохраняются файлы по умолчанию в разделе Назначение (в папке ../mssql/data).
Для выбора иного устройства можно воспользоваться раскрывающимся списком Создать резервную копию на. Для добавления объектов резервного копирования и/или целевых объектов, необходимо щелкнуть Добавить. Для увеличения скорости можно копии распределить между набором файлов.
Для просмотра содержимого, созданного ранее целевого объекта копирования, его необходимо выделить и кликнуть Содержимое. А для удаления такого объекта – кликнуть Удалить.
- При необходимости следует проверить все параметры, которые доступны на страницах параметров: носителя и резервного копирования.
- Кликните ОК для запуска процесса
- При успешном завершении бэкапа необходимо закрыть появившееся диалоговое окно, кликнув ОК.
Когда полный бэкап БД сделан, можно выполнять иные типы резервирования: дифференциальные и журналов транзакций. Когда резервное копирование выполняется на url-адрес на странице Параметры носителя, недоступным будет параметр Перезаписать носитель.
Полное копирование с помощью T-SQL
Для выполнения полного бэкапа базы данных с помощью Transact-SQL, необходимо выполнить соответствующие инструкции BACKUP DATABASE и указать:
- наименование БД для формирования копии;
- устройство, куда будут сохраняться скопированные данные.
Базовая структура синтаксиса T-SQL для создания полной копии БД выглядит следующим образом:
BACKUP DATABASE database TO backup_device [ , …n ] [WITH with_options [ , …o ] ] ;
- database – база данных, которая будет копироваться;
- backup_device [ , …n ] – показывает список устройств, которые будут использованы для резервирования. Может указываться как физическое (когда используются параметры DISK/ TAPE), так и логическое устройство (если оно определено);
- [WITH with_options [ , …o ] ] – указывает необходимое число параметров, o.
При необходимости следует указать нужное количество параметров WITH.
Резервная копия, созданная по команде BACKUP, сохраняется по умолчанию в набор носителей, созданный ранее. Все копии, созданные до этого, сохраняются. С помощью параметра NOINIT можно явно задать значение.
Для форматирования носителя копии используется параметр FORMAT в следующем предложении:
Он может использоваться при первичном обращении к носителю либо в том случае, когда требуется сделать перезапись всех данных, созданные ранее. Если потребуется, то можно новому носителю присвоить наименование и описание.
Однако при использовании в инструкции BACKUP предложения FORMAT следует проявить крайнюю осторожность. Неправильное использование этого предложения удалит все наборы, которые ранее были созданы и хранились на носителе.
Полное копирование с помощью PowerShell
Чтобы создать полный бэкап в PowerShell, следует использовать командлет (упрощенную команду) Backup-SqlDatabase. Чтобы отметить, что эта резервная копия является полной, необходимо задать параметр BackupAction вместе с Database (оно применяется по умолчанию). Этот параметр не относится к обязательным при полном резервировании БД.
Для использования PowerShell понадобится специальный модуль SqlServer. Выполнение команды Get-Module-Name SqlServer поможет выяснить – установлен модуль либо нет. Для установки модуля понадобится в сеансе PowerShell на правах администратора выполнить команду Install-Module-Name SqlServer.
Открывая PowerShell из SSMS, чтобы подключиться к SQL Server, не потребуются учетных данных. В них нет необходимости, т.к. для подключения между PowerShell и SQL Server используются учетные данные пользователя в SQL Server Management Studio.
Дифференциальное (разностное) копирование
Выборочное резервное копирование данных, появившихся после последнего полного бэкапа называется дифференциальным либо разностным. Оно полностью основывается на последней полного резервного набора.
Потому разностные резервные бэкапы могут использоваться только вместе с полной. Без последней, восстановление разностного бэкапа становится невозможным.
Советы по использованию дифференциальных бэкапов базы данных:
- Их следует использовать в тех случаях, когда на создание полного требуется много времени.
- Для уменьшения объемов созданных дифференциальных бэкапов, необходимо делать полный.
- После формирования полного бэкапа данных, все дифференциальные копии, сделанные ранее, становятся не актуальными.
При использовании дифференциального копирования рекомендуется придерживаться следующего плана его организации:
- полное осуществляется раз в определенное количество дней;
- разностное – каждый раз спустя определенное количество часов.
В том случае, когда оборот данных в день довольно высок, вышеприведенный план окажется неприменим, т.к. его реализация будет занимать много места на дисковом пространстве. К примеру, если вес полного бэкапа составляет 280 GB, а дифференциального, спустя 60 минут, – 4 GB, то по истечению суток вес дифференциальных наборов уже составит 96 GB. Потому, в приведенном примере использование дифференциального резервирования не оправданно.
Другие виды
Кроме вышеперечисленных, существуют иные виды копирования:
- При резервном копировании журнала транзакций выполняется копирование всех транзакций, которые имели место после выполнения предыдущего резервного копирования. После этого происходит урезание журнала, для освобождения дисковое пространство. Этот вид копирования относится к инкрементальному. Для полной модели восстановления необходима вся последовательность резервных наборов, начиная с полной.
- Бэкап Tail-Log, хоть и выделен отдельно, по сути является простой операцией, описанной в предыдущем пункте с опцией NORECOVERY. Его следует выполнять накануне восстановления копии журнала транзакций. Это позволит не упустить те транзакции, которые происходили после крайнего копирования до текущего момента.
- Бэкап Copy-only (создание резервной копии только для копирования) не является базовой ни для разностных, ни для копий журнала транзакций. Создание Copy-only применяется лишь когда необходимо снять полную резервную копию, не задев текущей цепочки резервных копий. Именно эти нюансы и отличают Copy-only от стандартного полного бэкапа данных.
- Частичное резервное копирование Partial backup применяется не часто. Его основная функция – копирование групп файлов read-only.
- Также применяется резервное копирование отдельных файлов и или их групп.
Срок действия
Настройка срока действия позволяет показать, через какое время можно будет удалить либо перезаписать резервную копию. Отметим, что данный вид настроек на оказывает влияния на срок восстановления базы. В том случае, когда срок закончился, восстановление базы данных из копии все еще будет возможен.
Выполняется данная настройка в процессе настройки резервного копирования, в основном окне.
Путь расположения
По умолчанию, все резервные копии сохраняются в специальный каталог с соответствующим названием. Для просмотра каталога или внесения изменений в него, необходимо:
- На корневом разделе SQL Server кликнуть правой кнопкой мышки и выбрать Свойства.
- Перейти к разделу Параметры баз данных. В нем опуститься к подразделу Места хранения, используемые базой данных по умолчанию. Именно здесь можно увидеть прописанный путь к месту размещения резервных наборов. Его можно изменить, воспользовавшись кнопкой (…), расположенной справа.
Общие советы
- Чтобы проверить успешность бэкапа, можно воспользоваться опцией проверки контрольной суммы BACKUP WITH CHECKSUM. Но для больших баз данных применение опции проблематично, т.к. она способна сильно загрузить систему.
- Бэкапы не следует выполнять на физические диски, где хранятся данные либо журнал транзакций.
- При использовании версий MS SQL Server 2008 и выше, рекомендуется выполнять сжатие копий БД средствами SQL.
- Копии лучше сохранять несколько дней. Если какая-нибудь окажется поврежденной, наличие старой будет как нельзя кстати. По истечении этого времени ненужные файлы с расширением bak следует удалить.
- Перед выполнением копирования рекомендуется проверять базы, воспользовавшись DBCC CHECKDB. Это позволит своевременно получить сообщение о возникновении проблем.
- Периодически следует обновлять статистику и реорганизацию индексов БД.
Как становится понятно, проблем с резервным копированием не должно возникать. Процесс легко настроить с помощью вышеописанных инструментов. Изучайте материал, применяйте на практике. При возникновении вопросов – обязательно задавайте. Делитесь своими мыслями и полезными советами.
Как сохранить базу SQL Server в SQL скрипт? Как сохранить проект SQL?
Не нужно недооц енивать возможности SQL-скриптов. Администраторы баз и SQL-разработчики знают, что их главная функция — автоматизировать рутинную работу и дать возможность при необходимости срочно восстановить отдельные SQL — объекты или всю базу сразу.
SQL-скрипты — это текстовые документы, поэтому фактически они могут быть созданы любым текстовым редактором, который поддерживает работу с расширением «.sql» или в специальных SQL-редаторах. Все эти программы отлично подходят, когда речь идет о создании простых SQL-скриптов с набором неких инструкций.
Но легче всего формировать SQL-инструкции в специализированных средах разработки, например в такой , как «SQL Server Management Studio».
Как сформировать SQL-скрипт в программе «SQL Server Management Studio»
-
Запустите среду разработки.
-
Запустите автоматическое формирование скриптов. Для этого внутри программы найдите базу данных, на основе которой будет формироваться скрипт. Потом нажмите на выбранную database правой кнопкой мышки, чтобы открылось дополнительное меню, в котором необходимо активировать пункт «Задачи» и подпункт «Сформировать скрипты».
-
После этого вам откроется мастер формирования скриптов. Где нужно будет отметить объекты базы данных, которые будут вноситься в скриптовый документ. Потом необходимо будет задать основные параметры, по которым будет формироваться скриптовый документ. После этого необходимо будет подтвердить и закрепить свой выбор инструкций и сохранить их.
Заключение
Сегодня мы достаточно подробно разобрали, как сохранить базу данных SQL в SQL-скрипт. Наиболее удобный вариант для этого действия — автоматическое создание скриптов в среде разработки. Обычно, SQL-базы собирают внутри скрипта с одной целью — сохранить саму базу, чтобы при острой необходимости можно было ее воссоздать и восстановить. По сути, сохранение базы данных в SQL-скрипте — это есть не что иное, как точка восстановления базы данных или бэкап, как еще называют эту точку.
Мы будем очень благодарны
если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.