Как сделать копию sql server
Создание резервной копии журнала транзакций Back Up a Transaction Log
Перед началом Before You Begin
Ограничения Limitations and restrictions
Инструкция BACKUP не разрешена в явных и неявных транзакциях. The BACKUP statement is not allowed in an explicit or implicit transaction. Явными транзакциями являются транзакции, для которых явно назначаются запуск и остановка. An explicit transaction is one in which you explicitly define both the start and end of the transaction.
Рекомендации Recommendations
Если в базе данных используется полная модель восстановления или модель восстановления с неполным протоколированием, то необходимо регулярно создавать резервную копию журнала транзакций, чтобы защитить данные и предотвратить переполнение журнала транзакций. If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data, and to prevent the transaction log from filling. При этом журнал усекается и поддерживает восстановление базы данных на определенный момент времени. This truncates the log and supports restoring the database to a specific point in time.
По умолчанию каждая успешная операция резервного копирования добавляет запись в журнал ошибок служб SQL Server SQL Server и в журнал системных событий. By default, every successful backup operation adds an entry in the SQL Server SQL Server error log and in the system event log. Если создание резервной копии журналов производится очень часто, сообщения об успешном завершении накапливаются очень быстро. Это приводит к увеличению журналов ошибок, затрудняя поиск других сообщений. If you back up the log frequently, these success messages accumulate quickly, resulting in huge error logs, making finding other messages difficult. Если работа существующих скриптов не зависит от этих записей, то их можно отключить с помощью флага трассировки 3226. См. статью Флаги трассировки (Transact-SQL). In such cases you can suppress these log entries by using trace flag 3226, if none of your scripts depend on those entries, see Trace Flags (Transact-SQL).
Permissions Permissions
Нужные разрешения BACKUP DATABASE и BACKUP LOG назначаются по умолчанию членам предопределенной роли сервера sysadmin и предопределенным ролям базы данных db_owner и db_backupoperator. The BACKUP DATABASE and BACKUP LOG permissions needed are granted by default to members of the sysadmin fixed server role, and the db_owner and db_backupoperator fixed database roles. Перед началом убедитесь в наличии необходимых разрешений. Check for the correct permissions before you begin.
Использование среды SQL Server Management Studio Using SQL Server Management Studio
Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
В списке База данных проверьте имя базы данных. In the Database list box, verify the database name. При необходимости можно выбрать другую базу данных из списка. You can optionally select a different database from the list.
Выберите Журнал транзакций в списке Тип резервного копирования. In the Backup type list box, select Transaction Log.
(Необязательно) В текстовом поле Описание введите описание резервного набора данных. (optional) In the Description text box, enter a description of the backup set.
Укажите, когда истекает срок действия резервного набора данных. Specify when the backup set will expire:
Чтобы задать срок действия резервного набора данных, выберите пункт После (параметр по умолчанию) и введите срок действия набора в днях с момента его создания. To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. Это значение может быть задано в диапазоне от 0 до 99 999 дней. Значение 0 означает, что срок действия резервного набора данных не ограничен. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
Чтобы указать дату истечения срока действия резервного набора данных, выберите пункт На и введите дату истечения срока действия резервного набора данных. To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.
Чтобы удалить носитель резервной копии, выберите его и нажмите кнопку Удалить. To remove a backup destination, select it and click Remove. Чтобы просмотреть содержимое носителя резервной копии, выберите его и щелкните Содержимое. To view the contents of a backup destination, select it and click Contents.
Создать резервную копию в существующем наборе носителей Back up to the existing media set
Для этого параметра выберите вариант Добавить в существующий резервный набор данных или Перезаписать все существующие резервные наборы данных. См. статью Наборы носителей, семейства носителей и резервные наборы данных (SQL Server). For this option, click either Append to the existing backup set or Overwrite all existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).
(Необязательно) Выберите Проверить имя набора носителей и срок действия резервного набора данных, чтобы при выполнении операции резервного копирования проверялся срок действия набора носителей и резервного набора данных. (optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
(Необязательно) Введите имя в текстовом поле Имя набора носителей. (optional) Enter a name in the Media set name text box. Если имя не указано, создается набор носителей с пустым именем. If no name is specified, a media set with a blank name is created. Если имя набора носителей указано, то для носителя (ленточного или дискового) проверяется совпадение введенного и существующего имени. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
Если оставить имя носителя пустым и установить рядом с ним флажок для проверки, имя носителя при успешном завершении также станет пустым. If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.
Создать резервную копию в новом наборе носителей и удалить все существующие резервные наборы данных Back up to a new media set, and erase all existing backup sets
Для этого параметра введите имя в текстовом поле Имя нового набора носителей и при необходимости введите описание набора носителей в текстовом поле Описание нового набора носителей. См. статью Наборы носителей, семейства носителей и резервные наборы данных (SQL Server). For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box, see Media Sets, Media Families, and Backup Sets (SQL Server).
В разделе Надежность можно установить следующие флажки. In the Reliability section, optionally, check:
Проверить резервную копию после завершения. Verify backup when finished.
Рассчитать контрольную сумму перед записью на носитель и (необязательно) Продолжить при ошибке контрольной суммы. Perform checksum before writing to media and (optional) Continue on checksum error.
В разделе Журнал транзакций можно установить следующие флажки. In the Transaction log section:
Для повседневного резервного копирования журналов оставьте вариант по умолчанию Обрезать журнал транзакций путем удаления неактивных записей. For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
Для создания резервной копии заключительного фрагмента журнала (активного журнала) включите параметр Выполнять резервное копирование заключительного фрагмента журнала, оставляя базу данных в состоянии восстановления. To back up the tail of the log (the active log), check Back up the tail of the log, and leave database in the restoring state.
Резервное копирование заключительного фрагмента журнала выполняется после сбоя, чтобы предотвратить потерю сделанной работы. A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. Резервное копирование активного журнала (резервное копирование заключительного фрагмента журнала) следует выполнять как после сбоя, так и перед началом восстановления базы данных, а также при сбое базы данных-получателя. Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. Выбор этого параметра равносилен применению параметра NORECOVERY в инструкции BACKUP LOG языка Transact-SQL. Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL.
Дополнительные сведения о резервных копиях заключительного фрагмента журнала см. в разделе Резервные копии заключительного фрагмента журнала (SQL Server). For more information about tail-log backups, see Tail-Log Backups (SQL Server).
Сведения о том, как просмотреть текущую настройку сжатия резервных копий по умолчанию, см. в разделе Параметр конфигурации сервера «Просмотр или настройка параметра сжатия резервных копий по умолчанию». To view the current backup compression default, see View or Configure the backup compression default Server Configuration Option.
Triple DES Triple DES
Использование Transact-SQL Using Transact-SQL
Выполните инструкцию BACKUP LOG для создания резервной копии журнала транзакций, указав следующее: Execute the BACKUP LOG statement to back up the transaction log, specifying the following:
имя базы данных, которой принадлежит журнал транзакций, резервную копию которого необходимо создать; The name of the database to which the transaction log that you want to back up belongs.
Устройство резервного копирования, на которое записывается резервная копия журнала транзакций. The backup device where the transaction log backup is written.
Использование PowerShell Using PowerShell
Резервные копии только для копирования Copy-Only Backups
Резервные копии только для копирования имеют следующие типы. The types of copy-only backups are as follows:
Полные резервные копии только для копирования (все модели восстановления). Copy-only full backups (all recovery models)
Резервная копия только для копирования не может служить в качестве базовой копии для разностного копирования или разностного резервного копирования и не влияет на базовую копию для разностного копирования. A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.
Операция восстановления полной резервной копии только для копирования аналогична операции восстановления любой полной резервной копии. Restoring a copy-only full backup is the same as restoring any other full backup.
Резервные копии журналов только для копирования (модель полного восстановления и модель восстановления с неполным протоколированием). Copy-only log backups (full recovery model and bulk-logged recovery model only)
Резервная копия журналов только для копирования сохраняет текущую точку архивирования журнала и, следовательно, не влияет на последовательность обычных резервных копий журналов. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Никакой необходимости в резервных копиях журналов только для копирования обычно нет. Copy-only log backups are typically unnecessary. Вместо этого можно создать новую обычную резервную копию журналов (с параметром WITH NORECOVERY), затем использовать ее совместно со всеми остальными ранее созданными резервными копиями журналов, которые необходимы для последовательности восстановления. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. Однако резервная копия журналов только для копирования иногда может быть полезна для выполнения восстановления в сети. However, a copy-only log backup can sometimes be useful for performing an online restore. Пример см. в разделе Пример. Оперативное восстановление файла, доступного для чтения и записи (модель полного восстановления). For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).
Журнал транзакций никогда не усекается после создания резервной копии только для копирования. The transaction log is never truncated after a copy-only backup.
В Управляемом экземпляре SQL Azure резервная копия только для копирования не может быть создана для базы данных, зашифрованной с помощью управляемого службой прозрачного шифрования данных (TDE). In Azure SQL Managed Instance copy-only backup cannot be created for a database encrypted with service-managed Transparent Data Encryption (TDE). Управляемое службой TDE использует для шифрования данных внутренний ключ, который не может быть экспортирован, поэтому вы не сможете восстановить резервную копию в другом месте. Service-managed TDE uses internal key for encryption of data, and that key cannot be exported, so you could not restore the backup anywhere else. Для создания резервных копий зашифрованных баз данных, предназначенных только для копирования, рассмотрите возможность использования управляемого пользователем TDE, но при этом убедитесь, что ключ шифрования доступен для последующего восстановления. Consider using customer-managed TDE instead to be able to create copy-only backups of encrypted databases, but make sure to have encryption key available for later restore.
Создание резервной копии только для копирования To Create a Copy-Only Backup
Примеры Examples
A. A. Использование среды SQL Server Management Studio Using SQL Server Management Studio
В этом примере резервная копия только для копирования для базы данных Sales будет заархивирована на диск в папку резервных копий по умолчанию. In this example, a copy-only backup of the Sales database will be backed up to disk at the default backup location.
В обозревателе объектов подключитесь к экземпляру компонента SQL Server Database Engine и разверните его. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Нажмите кнопку ОК. Click OK.
Б. B. Использование Transact-SQL Using Transact-SQL
В этом примере создается резервная копия только для копирования для базы данных Sales с использованием параметра COPY_ONLY. This example creates a copy-only backup for the Sales database utilizing the COPY_ONLY parameter. Также создается резервная копия только для копирования для журнала транзакций. A copy-only backup of the transaction log is taken as well.
Если параметр COPY_ONLY указан одновременно с параметром DIFFERENTIAL, он не имеет эффекта. COPY_ONLY has no effect when specified with the DIFFERENTIAL option.
В. C. Использование PowerShell Using PowerShell
Связанные задачи Related Tasks
Создание полной резервной копии или резервной копии журнала To create a full or log backup
Просмотр резервных копий только для копирования To view copy-only backups
Настройка и использование поставщика SQL Server PowerShell To set up and use the SQL Server PowerShell provider
Резервное копирование и восстановление MS SQL Server
Приветствую, в данной заметке будет рассмотрено создание резервных копий и восстановление в MS SQL Server
Создание резервных копий
1 Резервное копирование системных баз данных.
2 Полное резервное копирование базы данных.
3 Разностное резервное копирование базы данных.
4 Резервное копирование журнала транзакций базы данных.
5 Резервное копирование файловых групп базы данных.
Восстановление из резервных копий
6 Восстановление из полной резервной копии.
7 Восстановление из разностной резервной копии.
8 Восстановление журнала транзакций.
9 Восстановление файловых групп.
10 Восстановление системных баз данных.
Создание резервных копий
1Резервное копирование системных баз данных.
Список системных баз: master, model, msdb, tempdb.
Master: содержит сведения обо всех базах данных на сервере. Резервное копирование необходимо делать каждый раз, когда создаются, удаляются или изменяются пользовательские базы данных.
Model: Используется в качестве шаблона для создаваемых баз данных. Резервное копирование необходимо при изменении настройки самой базы model.
Msdb: Содержит сведения о заданиях и для агента сервера MS SQL Server. копирование необходимо делать каждый раз при добавлении задания для агента сервера MS SQL Server.
Tempdb: Хранит временные данные например для транзакций. Уничтожается и создается при перезапуске экземпляра MS SQL Server. Резервное копирование делать нет смысла.
Создадим новый запрос:
Выполним следующий запрос:
BACKUP DATABASE master
TO DISK = ‘C:\sql\master.bak’
BACKUP DATABASE model
TO DISK = ‘C:\sql\model.bak’
BACKUP DATABASE msdb
TO DISK = ‘C:\sql\msdb.bak’
Как видим, на диск ‘C’ было произведено успешное резервное копирование системных баз данных.
2Полное резервное копирование базы данных.
Включает в себя файлы данных и журнал транзакций. По сути является базой данных на момент создания резервной копии базы данный MS SQL Server.
Резервное копирование данных в базе.
Резервное копирование изменений, возникающих во время резервного копирования
Резервное копирование транзакций, не зафиксированных в журнале транзакций.
Способ 1(Графический интерфейс):
Выберем «создать резервную копию»
Указываем куда копировать и модель – полная.
Способ 2(Запрос SQL):
BACKUP DATABASE sbase
TO DISK = ‘C:\sql\sbase.bak’
3 Разностное резервное копирование базы данных.
Включает в себя все изменения базы данных с момента последнего полного резервного копирования.
Нельзя восстановить без полной резервной копии. После каждого запуска разностного копирования, размер резервной копии возрастает из-за количества транзакций с момента полного резервного копирования.
При создании разностного резервного копирования выполняются следующие действия:
Создание резервных копий баз данных, которые изменились с момента полного резервного копирования.
Создание резервных копий всех операций, выполняющихся во время разностного резервного копирования и всех транзакций не зафиксированных в журнале транзакций.
—Создадим таблицу test
INSERT INTO test (id,name)
Далее по аналогии с полным запустим задачу резервного копирования, но модель выберем – разностную:
Проведем полный бэкап, добавим еще данных, проведем разностный бэкап:
—Делаем полный бэкап
BACKUP DATABASE sbase
TO DISK = ‘C:\sql\sbase_razh2’
—Добавим еще данные
INSERT INTO test (id,name)
BACKUP DATABASE sbase
TO DISK = ‘C:\sql\sbase_razh3’
Вывод, не надо доводить разностное копирование до больших объемов, иначе оно теряет свой смысл быстрого восстановления данных.
4 Резервное копирование журнала транзакций базы данных.
Содержат все изменения базы данных при первичном резервном копировании лога транзакций, или изменения с последней успешной резервной копии журнала транзакций.
Не имеет смысла, если хоть раз не выполнялось полное резервное копирование, т.к. резервную копию лога невозможно будет восстановить при отсутствии полной резервной копии.
В процессе выполняются следующие действия:
Создается копия журнала транзакций от последнего резервного копирования лога до конца текущего.
Очищаются части журнала транзакций до начала активной части и отбрасываются сведения в неактивной части.
По аналогии с полным и разностным копированием запускаем задачу, но тип выбираем – лог транзакций:
Или с помощью запроса:
TO DISK = ‘C:\sql\sbase_tran.bak’
5 Резервное копирование файловых групп базы данных.
По сути являются именованными коллекциями файлов и используются для упрощения размещения данных и выполнения задач администрирования.
Файлы журналов не входят в состав файловых групп.
Управление пространством журнала отделено от управления пространством данных, возможно только полное и разностное резервное копирование файлов и файловых групп.
Пример полного копирования:
По аналогии с другими видами копирования запускаем мастер:
Тоже, только запросом:
BACKUP DATABASE sbase
TO DISK = ‘C:\sql\primary.bak’
Про восстановление можно почитать на русском языке :
Стадия копирования данных: копирование всех страниц данных, журнала и индекса с резервного носителя в файлы базы данных.
Стадия повтора: журнальные транзакции применяются к данным, скопированным из резервной копии, чтобы произвести их накат до точки восстановления. В этой точке базы данных обычно имеются незафиксированные транзакции, и потому база находится в непригодном для работы состоянии. В этом случае следует производить в процесс восстановления базы данных стадию отмена.
Стадия отката: производит откат незафиксированных транзакций и делает базу данных доступной для пользователей. После завершения стадии отката восстановление последующих резервных копий становится невозможным. Затем в процессе восстановления база данных переводится в активный режим.
Режим WITH RECOVERY включает и стадию повтора, и стадию отката и восстанавливает базу данных. Более поздние резервные копии восстановить невозможно. Это значение по умолчанию.Если набор данных наката не был восстановлен в достаточной степени, чтобы обеспечить согласованность с базой данных, стадия отката выполнена быть не может. Компонент Database Engine выдает ошибку и прекращает восстановление. Если весь набор данных наката согласован с базой данных, то выполняется восстановление, после чего базу данных можно перевести в режим в сети.
Предложение WITH NORECOVERY позволяет пропустить стадию отката, чтобы сохранить незафиксированные транзакции. Пропуск стадии отката позволяет восстановить другие резервные копии, чтобы выполнить накат базы данных на более поздний момент времени. Иногда инструкция RESTORE WITH NORECOVERY выполняет накат данных до момента, пока они не будут согласованы с базой данных. В таких случаях компонент Database Engine выдает информационное сообщение, указывающее, что набор данных наката теперь можно восстановить при помощи параметра RECOVERY. Другими словами, параметр NORECOVERY нужно использовать, когда для восстановления базы используются несколько восстанавливаемых резервных копий, за исключением последней восстанавливаемой резервной копии. После применения параметра NORECOVERY, база данных переходит в состояние восстановления.
6 Восстановление из полной резервной копии.
Или с помощью запроса:
RESTORE DATABASE sbase
7 Восстановление из разностной резервной копии.
В начале восстанавливается полная копия(например в прошлом шаге мы это уже сделали), а далее восстановим разностную копию.
Графический интерфейс аналогичен с предыдущим примером за исключением типа выбираемой копии, а запрос будет таков на примере наших разностных копий:
RESTORE DATABASE sbase
FROM DISK = ‘C:\sql\sbase_razh2’
WITH FILE = 1, NORECOVERY, REPLACE
RESTORE DATABASE sbase
FROM DISK = ‘C:\sql\sbase_razh3’
WITH FILE = 1, RECOVERY
8 Восстановление журнала транзакций.
В начале следует восстановить базу данных из полной резервной копии, затем накатить на базу последовательно резервные копии журнала транзакций.
Графический вариант интуитивно понятен, будет продемонстрирован только SQL запрос:
Для того, чтоб все отработало корректно, вернемся разностному бэкапу 2, и после него накатим журнал транзакций:
RESTORE DATABASE sbase
FROM DISK = ‘C:\sql\sbase’
WITH FILE = 1, NORECOVERY, REPLACE
RESTORE DATABASE sbase
FROM DISK = ‘C:\sql\sbase_razh2’
WITH FILE = 1, NORECOVERY, REPLACE
FROM DISK = ‘C:\sql\tran.bak’
WITH FILE = 1, NORECOVERY
9 Восстановление файловых групп.
Графический вариант показан не будет, он довольно интуитивен, запрос SQL:
RESTORE DATABASE sbase FILEGROUP = ‘PRIMARY’
FROM DISK = ‘C:\sql\primary.bak’
WITH PARTIAL, RECOVERY, REPLACE
Так как мы восстанавливали только часть базы – файловую группу, то мы использовали параметр «PARTIAL».
10 Восстановление системных баз данных.
Если экземпляр SQL сервера доступен, то системные базы восстанавливаются согласно приведенной таблице:
Системная база данных
Запускаем экземпляр сервера в однопользовательском режиме. Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных. После восстановления следует перезапустить экземпляр SQL сервера.
Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.
Восстановление базы осуществляется так же, как полное восстановление пользовательской базы данных.
Запускаем экземпляр сервера в однопользовательском режиме: выключим и включим экземпляр сервера с параметром запуска /m, введя в командной строке Windows (CMD):
net stop MSSQLSERVER
net start MSSQLSERVER /m
Подключимся к серверу и запустим процесс восстановления базы.
sqlcmd
RESTORE DATABASE master FROM DISK = ‘C:\sql\sbase.bak’ WITH REPLACE;
Вернем экземпляр SQL в состояние «в сети».
Стартуем сервер в многопользовательском режиме: