По материалам статьи Rahul Sharma на sqlservercentral.com: " Copying a Database from Server to Server "

Метод 1: самый быстрый способ копирования - отсоединение базы данных от исходного сервера и затем прикрепление вместе с журналом на нужный сервер.

Отсоедините базу данных на исходном сервере (Измените, соответственно, имя базы данных):

Use Master GO Exec sp_detach_db "database_name", "true" GO

sp_detach_db отсоединяет базу данных от исходного сервера (у неё два параметра: @dbname, который является именем базы данных и @skipchecks, который является указанием для обновления статистики) и указав значение "true" для второго параметра (@skipchecks) этой хранимой процедуры, что бы удостоверится в том, что если модификация статистики не была выполнена перед отсоединением базы данных от сервера, она обновится после присоединения, что потребует некоторого времени. Скопируйте данные и журналы из каталога Data исходного сервера в каталог данных на новом сервере. Удостоверитесь, что Вы не имеете точно таких же баз данных на сервере адресата.... Если это так, отключите их.
Прикрепите данные и журналы на новый сервер. Выполните на этом сервере:

Use Master GO PRINT "Attaching Database" EXEC sp_attach_db @dbname = "database_name", @filename1 = "c:\mssql7\data\database_name.mdf", -- Это путь к файлу данных @filename2 = "d:\mssql7\data\database_name_log.ldf" -- Это путь к журналу

Этим Вы прикрепите базу данных к новому серверу, но учётные данные пользователей, для подключения к базе не будут скопированы с исходного на новый сервер. Вы можете использовать нижеследующий сценарий, чтобы перенести логины:

/* Установите связанный сервер (используя sp_addlinkedserver и sp_addlinkedsrvlogin), назвав его: sourceserver, и из которого стандартные логины входа в систему должны быть перенесены. Вы можете называть его, как Вам удобно и изменить также имя связанного сервера. Чтобы обеспечить доступу к данным связанного сервера, Вы должны использовать sp_serveroption */

declare @login sysname , @password sysname declare sourcelogins cursor for select name , password from sourceserver.master.dbo.syslogins where isntname = 0 and charindex("repl_" , name) = 0 and charindex("distributor" , name) = 0 and name != "sa" open sourcelogins while (@@fetch_status = 0) begin fetch sourcelogins into @login , @password exec sp_addlogin @login , @password , @encryptopt = "skip_encryption" end close sourcelogins deallocate sourcelogins go

При откреплении и прикреплении баз данных, я столкнулся с ещё одной проблемой, кроме потери связи пользователей и их логинов, описанной выше. Я обнаружил, что статистика останется не эффективной, если Вы выполните sp_updatestats на прикрепляемой базе данных. Так, я рекомендовал бы обновить статистику после того, как Вы уже прикрепили эту базу. Или воспользуйтесь альтернативным вариантом: когда Вы открепляете базу данных, удостоверьтесь, что второй параметр установлен в Ложь, что заставит статистику обновиться, и Вам не придётся обновлять её позже на новом сервере. Любой из этих путей прекрасно работает.
Этот метод также великолепно работает, если необходимо переместить базы данных на диск и затем прикрепить их на другой сервер, который не находится в сети. Также, это - очень быстрый путь копирования баз данных с сервера на сервер. Не забудьте прикрепить копируемую базу назад на исходный сервер, как только Вы скопировали данные и журналы с исходного сервера на сервер адресат.

Метод 2: Использование DTS.

DTS очень часто используется для перемещения баз данных с сервера на сервер. Вы можете использовать мастер экспорта/импорта DTS (SQL 7.0 и 2000). Мастер может использоваться для копирования схем, объектов (хранимые процедуры, представления и триггеры и т.д.), данных и также логинов. Или Вы можете использовать DTS Designer и создать задачу перемещения базы данных и задачу перемещения логинов (доступно только для SQL 2К). Также, Вы можете использовать мастер копирования баз данных (Copy Database Wizard), чтобы решить задачу перемещения базы (доступно только для SQL 2К). DTS - довольно мощный инструмент и если Вы его пока не использовали, Вы лишаете себя многих функциональных возможностей и лёгкости, с которой Вы могли бы решать сложные задачи.
В случае если Вы ищете хорошую ссылку на DTS, я предложил бы Вам почитать:
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/dts_overview.htm

Метод 3: Создайте схему и механизм переноса данных, использующий bcp/bulk insert.

Создайте схему на сервере приёмнике данных, если Вы уже имеете скрипт для создания схемы и затем используете bcp или bulk insert, чтобы скачать данные. Оба операции - нерегистрируемые, так что они отработают очень быстро. Главное различие между bcp и bulk insert - это то, что bulk insert не может экспортировать данные, а bcp может.
Вы можете использовать bcp, чтобы экспортировать данные в плоский файл и затем импортировать данные в новую базу из плоского файла, используя bcp или bulk insert. Даже при том, что bulk insert является быстрым способом закачки данных, он имеет большое количество ограничений.

Метод 4: Традиционный путь: Backup и Restore.

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

Метод 5: Использование распределённых запросов.

Вы сначала должны создать схему на сервере приёмнике данных, используя ваши скрипты создания схем. После этого Вы можете организовать связанный сервер и написать инструкции вставки, которые будут вставлять данные из источника на новый сервер, используя функции openrowset и openquery для запросов к связанным серверам. Вы должны удостовериться, что foreign key и check constraints отключены до того, как Вы начнёте закачивать данные и затем подключить их, когда всё будет сделано. Этот метод самый медленный из всех упомянутых в этой статье. Зато, с помощью него можно переносить данные на SQL Server из гетерогенных источников, например: Oracle, Sybase, DB2 и т.д.

Обширный функционал Bacula Enterprise Edition, помимо прочего, позволяет быстро и просто создавать бэкапы БД под . Например, речь идет об инструменте, с помощью которого можно осуществлять резервное копирование MS SQL Server. Сделать бэкап MS SQL пользователь может, создавая резервные копии специфических баз данных MS SQL больших объемов, используемых платформой Windows, при меньших затратах на ПО сторонних производителей, с возможностью восстановления данных до определенного момента времени (PITR-восстановление) на сетевой и локальный диск.

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

Скрипт бэкапа MS SQL Bacula Systems функционирует независимо от VSS. Это значит, что инструмент резервного копирования MS SQL не использует снапшоты VSS для создания бэкапов. Поэтому пользователь может задать следующее значение “Enable VSS = no” в Bacula FileSet. Эффективное создание бэкапов MS SQL Server и их восстановление с помощью данного решения достигаются за счет использования Microsoft API для SQL Server. Благодаря этому Bacula Systems может поддерживать работу механизмов обеспечения защиты и все типы проверки подлинности, реализованные в Microsoft SQL Server.

Резервное копирование журнала транзакций MS SQL и восстановление MS SQL на момент времени: ПО Bacula Enterprise Edition позволяет восстанавливать блоки данных MS SQL или конкретные настройки до определенного момента времени. Благодаря реализации моделей полного восстановления и восстановления с неполным протоколированием вы сможете восстанавливать MS SQL, используя PITR-восстановление, либо использовать LSN для восстановления системы до конкретного состояния. Вы можете восстанавливать определенное состояние базы данных MS SQL на любой конкретный момент времени с точностью до секунды. В случае бэкапа журнала транзакций MS SQL, при восстановлении состояние БД будет восстанавливаться из различных выбранных бэкапов.

Краткий обзор функций 
 автоматического бэкапа и восстановления MS SQL с Bacula Enterprise

Компания Bacula Systems создала плагин для резервного копирования MS SQL Server для совместного использования с Bacula Enterprise Edition. Бэкап MS SQL Server с Bacula обладает следующими функциями:

• Поддержка полного и дифференциального резервного копирования MS SQL
• Поддержка инкрементального резервного копирования MS SQL
• Резервное копирование MS SQL на сетевой и локальный диск
• Резервное копирование MS SQL по расписанию
• Создание бэкапов на уровне базы данных MS SQL Server
• Возможность включать/исключать БД из процедуры создания бэкапов
• Поддержка создания бэкапов БД «только для чтения»
• Восстановление MS SQL бэкапов на диск
• Отправка потока резервной копии напрямую в Storage Daemon
• Восстановление MS SQL на момент времени

Обзор и настройка резервного копирования MS SQL 2008, 2008 R2, 2012 и 2014

В данном документе представлены решения для Bacula Enterprise Edition 8.4 и более поздних версий, которые не поддерживаются ранними версиями ПО. Резервное копирование базы MS SQL был протестировано и поддерживается MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. Возможна работа резервного копирования MS SQL от Bacula с SQL Express.

Глоссарий резервного копирования MS SQL 2008, 2008 R2, 2012 и 2014

• MS SQL означает Microsoft SQL Server.
• Журнал транзакций (transaction log). Любая база данных MS SQL Server имеет журнал транзакций, в который записываются все транзакции и модификации БД, выполненные в ходе таких транзакций. Журнал транзакций – важный элемент БД. В случае отказа системы журнал транзакций может потребоваться для восстановления БД до рабочего состояния. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms190925.aspx .
• Дифференциальное резервное копирование базы данных MS SQL Server. Дифференциальный бэкап основан на последнем полном . В ходе выполнения дифференциального бэкапа захватываются только те данные, которые были изменены с момента создания последнего полного бэкапа. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en- us/library/ms175526.aspx .
• Полное резервное копирование базы данных MS SQL Server. В ходе полного бэкапа БД создается резервная копия всей базы данных. Бэкап включает часть журнала транзакций с целью восстановления полной БД из резервной копии. Полные бэкапы БД содержат БД на момент завершения создания резервной копии. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en- us/library/ms186289.aspx .
• Бэкап «только для копирования» (CopyOnly). Бэкапы «только для копирования» представляют собой бэкапы MS SQL, независящие от обычной последовательности создания традиционных резервных копий SQL Server. Иногда полезно создавать бэкапы для особых нужд, не влияя на общий процесс резервного копирования и восстановления БД. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms191495.aspx .
• VDI (Интерфейс виртуального устройства) – это технология Microsoft, позволяющая создавать именованный канал между программами.
• стандартные маски задают наборы строк с подстановочными знаками. Например, стандартная маска production* будет включать строки production1 и production2.
• строка
• целое число.
• LSN Каждая запись в журнале транзакций MS SQL Server обозначается с помощью уникального регистрационного номера транзакции (LSN). Более подробную информацию вы найдете по ссылке https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx .

Резервное копирование MS SQL Server 2008, 2008 R2, 2012 и 2014

Полное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014

В ходе полного резервного копирования базы данных MS SQL сохраняются файлы БД и журнал транзакций, что позволяет полностью защитить базу MS SQL на случай отказа носителя. В случае повреждения одного или более файлов восстановление базы MS SQL из бэкапа позволит восстановить все совершенные транзакции. Также будет произведен откат всех транзакций, находившихся в процессе выполнения. В данном режиме производится создание бэкапов БД master и mbdb.

Дифференциальное резервное копирование баз данных MS SQL Server 2008, 2008 R2, 2012 и 2014

Дифференциальный бэкап базы MS SQL Server основан на самом последнем полном бэкапе базы данных MS SQL. В ходе создания дифференциального бэкапа MS SQL захватываются только те данные, которые были изменены с момента создания последнего полного бэкапа MS SQL. Для функции дифференциального бэкапа MS SQL крайне важна последовательность бэкапов. Если по какой-то причине полный бэкап, на который ссылается MS SQL, не доступен, дифференциальные бэкапы базы данных MS SQL Server нельзя будет использовать. Резервное копирование MS SQL от Bacula использует определенные методы для решения данной проблемы. Поэтому, в случае возникновения сложностей, статус дифференциального бэкапа БД может быть автоматически повышен до полного бэкапа.

Резервное копирование журнала транзакций MS SQL 2008, 2008 R2, 2012 и 2014

Функция создания бэкапа журнала транзакций MS SQL реализуется на инкрементальном уровне с помощью ПО Bacula. БД MS SQL должна быть сконфигурирована с помощью моделей полного восстановления и восстановления с неполным протоколированием. Если MS SQL использует простую модель восстановления, файл журнала транзакций будет «урезаться» после каждой контрольной точки, и бэкап журнала транзакций не позволит реализовать восстановление до выбранной конкретной точки, т.е. PITR-восстановление. Можно будет восстановить базу данных MS SQL полностью, но нельзя будет выбрать контрольную точку. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms189275.aspx.

Настройка резервного копирования MS SQL и конфигурирование БД

Необходимо всегда создавать резервную копию БД master. Если БД master будет так или иначе повреждена, например, в результате отказа носителя, возможно, не получится запустить инстанс MS SQL. В таком случае, необходимо восстановить БД master, и только потом восстановить из резервной копии саму БД. Возможно создание только полных бэкапов базы MS SQL. Более подробную информацию вы найдете по ссылке https://technet.microsoft.com/en-s/library/aa213839%28v=sql.80%29.aspx.

Восстановление базы MS SQL из бэкапа

Вы можете использовать все стандартные способы запуска процедуры восстановления базы MS SQL из бэкапа. Однако вы должны убедиться в том, что, в случае восстановления дифференциальных данных, будет также восстановлен полный предыдущий бэкап базы MS SQL. В таком случае восстановление происходит автоматически, если вы запускаете его в консоли bconsole с помощью вариантов восстановления 5 или 12. В сгенерированной файловой структуре вам необходимо отметить восстановление полных БД или инстансов БД.

Варианты восстановления базы MS SQL из бэкапа

ПО Bacula Enterprise Edition позволяет пользователям использовать множество вариантов восстановления MS SQL и применять самые различные способы «отката» БД. Наиболее часто используемые варианты восстановления описаны ниже:

• параметр Where: В случае с Bacula Enterprise Edition, данный параметр позволяет администратору восстанавливать БД в конкретном месте.
• параметр Replace: Используется для того, чтобы определить, как ПО Bacula должно вести себя с текущей БД при восстановлении. Резервное копирование MS SQL от Bacula также позволяет использовать еще несколько опций при восстановлении, например:
• Instance: Поскольку MS SQL использует несколько инстансов, бэкап базы MS SQL от Bacula позволяет выбирать, какой из инстансов следует восстанавливать. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа. По умолчанию, используется инстанс с именем “MSSQLSERVER”.
• Database. Данная опция указывает имя БД для восстановления и она использует значение, заданное в момент создания БД. Данный параметре является опциональным. По умолчанию резервное копирование баз данных SQL Server использует параметр Where для определения имени новой БД. Если обоим параметрам Where и Database назначены валидное имя БД, то параметр Database будет использоваться.
• User. Имя пользователя, используемое для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
• Password. Пароль, используемый для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
• Domain. Домен, используемый для подключения к инстансу базы данных MS SQL. Данный параметр является опциональным, и, если он не задан, при восстановлении будет использоваться значение, заданное при создании бэкапа.
• Recovery. Параметр позволяет определить, будет ли произведен откат БД к предыдущему состоянию при восстановлении или нет. По умолчанию при восстановлении БД будет произведет откат к предыдущему состоянию.
• Stop_before_mark. Условие WITH STOPBEFOREMARK = Используется для того, чтобы указать, что запись в журнале транзакций, которая находится непосредственно перед флагом, и является точкой восстановления. Точкой восстановления может служить дата и время, номер LSN или имя флага mark_name.
• Stop_at_mark. Условие WITH STOPATMARK =Используется для того, чтобы показать, что помеченная транзакция является точкой восстановления. STOPATMARK перемещается вперед к флагу и включает повтор помеченной транзакции. Точкой восстановления может служить дата и время, номер LSN или имя флага mark_name.
• Stop_at=. Условие WITH STOPAT = используется для того, чтобы указать, что точкой восстановления является дата/время.
• Restrict_user. Условие WITH RESTRICT_USER используется для ограничения доступа к восстановленной БД. По умолчанию используется значение no.

В программе , созданной Bacula Systems, настройка резервного копирования MS SQL находятся на вкладке восстановления.

Рисунок 1: Вкладка восстановления БД при использовании программы BWeb Management Suite

Восстановление MS SQL на момент времени

Данный вопрос относится только к тем БД SQL, которые используют модели полного восстановления и восстановления с неполным протоколированием. В случае модели восстановления с неполным протоколированием, если бэкап журнала содержит изменения, сделанные во время операций массовой обработки данных, то невозможно будет выполнить восстановление на какой-либо момент времени в пределах этого бэкапа. БД должна быть восстановлена до конца журнала транзакций, бэкап которого был создан. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms179451.aspx.

Восстановление MS SQL на момент времени можно совершать непосредственно из плагина бэкапа MS SQL. Также можно восстанавливать файлы локально и выполнять операции из консоли управления Microsoft SQL Server Mangement Console, чтобы иметь возможность использовать больше возможностей.

LSN

LSN номера используются для создания последовательности восстановления MS SQL с целью отслеживания момента времени, до которого данные были восстановлены. При восстановлении MS SQL из бэкапа данные восстанавливаются до LSN номера, соответствующего моменту времени, в который был выполнен бэкап. Более подробную информацию вы найдете по ссылке https://msdn.microsoft.com/en-us/library/ms190925.aspx.

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

• При выводе описания заданий по созданию бэкапа с помощью ПО Bacula
• В названии файла журнала
• В таблице msdb.backupset
• В таблице msdb.backupfile

При выполнении задания по созданию бэкапа базы MS SQL при выводе описания задания отобразится следующая информация о LSN номерах:

Номер First LSN соответствует последнему LSN номеру последнего бэкапа журнала транзакций. Таким бэкапом может являться самый первый полный бэкап или последний бэкап (инкрементальный).

Номер Last LSN соответствует последней транзакции, зарегистрированной в журнале.

В случае бэкапа журнала транзакций (инкрементального), название файла, связанного с этой БД, в задании по созданию инкрементального бэкапа будет выглядеть следующим образом:

Число в названии, в нашем случае 42000162001, соответствует последнему LSN номеру предыдущего задания (по созданию полного или инкрементального бэкапа).

Рисунок 2: Первый номер LSN, последний номер LSN и номера LSN в названии файлов

Как показано в примере на рисунке 2, если администратору необходимо восстановить базу данных MS SQL в состояние, соответствующем LSN номеру 14, можно выполнить следующие действия:

• В меню восстановления БД используйте опцию 5
• Выберите последний файл полного бэкапа “data.bak” (LSN: 10)
• Выберите инкрементальный бэкап “log-10.trn”

Или, если последний полный бэкап MS SQL Server не доступен, однако доступен предыдущий полный бэкап, то:

• Используйте опцию восстановления 3, выберите соответствующие значения jobids
• Выберите директорию БД “/@mssql/db29187”
• Выберите файл полного бэкапа “data.bak” (LSN: 2)
• Выберите инкрементальные бэкапы “log-2.trn”, “log-3.trn”, “log-10.trn”
• Задайте параметр stop_at_mark равный “lsn:14”
• Запустите задачу по восстановлению бэкапа

Сценарии восстановления MS SQL

Описание	Where	Database	Пример
Восстановить файлы на диск	Путь		where=c:/tmp
Восстановить исходную БД			where=/
Восстановить с новым именем	Имя		where=newdb
Восстановить с новым именем		Имя	database=newdb
Восстановить с новым именем и переместить файлы		Имя

Таблица 1: Сценарии восстановления MS SQL

2.3.1 Восстановление базы MS SQL с исходным именем

Чтобы восстановить БД с исходным именем, параметр Where должен быть не задан (пустое значение), либо должно быть задано значение “/”, а параметру Replace должно быть присвоено значение Always , или же сначала необходимо удалить исходную БД.

Восстановление бэкапа MS SQL с новым именем

Чтобы восстановить бэкап базы данных MS SQL с новым именем, возможно, сначала потребуется переместить файлы БД на диск. Все зависит от того, существует ли еще исходная БД.

Если исходная БД более не доступна, то параметр where , либо поле “Plugin Options” может содержать название новой БД. Резервное копирование MS SQL от Bacula автоматически создаст БД с новым именем.

Если исходная БД все еще пока требуется, параметр where будет использоваться для перемещения файлов на диск, и необходимо будет задать название новой БД с помощью меню “Plugin Options”. В дереве восстановления необходимо выбрать файл layout.dat.

Используя каталог My Catalogue

Запустите задачу восстановления MS SQL:

Используя каталог My Catalogue, запустите задачу восстановления базы MS SQL:

Восстановление MS SQL на локальный диск

Если указать where=c:/path/ , файлы будут восстановлены на локальный диск, и администратор базы данных MS SQL сможет использовать процедурное расширение TSQL для консоли управления Microsoft SQL Server Mangement Console для восстановления БД. Команды SQL, необходимые для восстановления БД, перечислены в описании Job output как показано на рисунке ниже.

Восстановление базы данных MS SQL «master»

Инструкции по восстановлению БД “master” подробно изложены на странице: https://technet.microsoft.com/en-us/library/aa213839%28v=sql.80%29.aspx

База данных MS SQL в состоянии восстановления

По завершению восстановления MS SQL, если опциональному параметру Recovery было присвоено значение No , восстановленная БД будет находиться в состоянии «восстановления». Чтобы завершить процесс восстановления, необходимо запустить процедуру отката БД. Для этого используйте следующую команду SQL.

Обновлено: 23.06.2017 Опубликовано: 22.06.2017

Есть несколько способов создания резервной копии MS SQL. Для разовых операций прекрасно подойдет графический инструмент SQL Management Studio. Для автоматизации — Powershell или cmd. Данные операции применяются к любым базам, как для 1С, так и любых других приложений.

С помощью графического интерфейса

Открываем MS SQL Management Studio. Кликаем правой кнопкой мыши по базе, для которой хотим сделать резервную копию - Задачи - Создать резервную копию :

В открывшемся окне оставляем полный тип копий и путь к резервному файлу (при необходимости, можно его поменять, удалив и создав снова. Можно указать как локальный диск, так и сетевой):

После завершения процесса мы увидим сообщение «Резервное копирование базы... успешно завершено».

С помощью командной строки (cmd)

Выполняется при помощи команды sqlcmd.

Синтаксис:

sqlcmd -S -U -P Q "BACKUP DATABASE [] TO DISK = N""

Пример готового скрипта

@echo off
set dd=%DATE:~0,2%
set mm=%DATE:~3,2%
set yyyy=%DATE:~6,4%
set curdate=%dd%-%mm%-%yyyy%
set username=sa
set password=my_pass

Set db=work1

Set db=work2
sqlcmd -S localhost -U %username% -P %password% -Q "BACKUP DATABASE [%db%] TO DISK = N"D:\Backup\MSSQL\%db%_%curdate%.bak" WITH NOFORMAT, NOINIT, NAME = N"%db%-full", SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"

* в данном примере мы подключаемся к локальному SQL серверу под учетной записью sa с паролем my_pass и делаем резервную копию баз work1 и work2 . Резервные копии размещаем по пути D:\Backup\MSSQL . Имя файлов резервных копий work1_<текущая дата>.bak и work2_<текущая дата>.bak

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

С помощью Powershell

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

Для выполнения команды, сначала импортируем модуль:

Синтаксис:

Backup-SqlDatabase -ServerInstance <имя SQL сервера> -Database <имя базы> -BackupFile <путь к файлу с резервной копией>

Пример скрипта на powershell

$server = "SQL01"
$curdate = Get-Date -Format yyyyMMdd

import-module sqlps -DisableNameChecking

$db = work1
Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile $db_$curdate.bak

* где выполняется резервное копирования базы work1 на сервере SQL01

Также как и для cmd, данный скрипт можно поместить в планировщик для запуска по расписанию.

Срок действия резервного набора данных

Данная настройка позволяет указать, через какой промежуток времени резервную копию можно удалить (перезаписать). Важно понимать, что настройка не влияет на сам период восстановления — если срок истек, восстановиться из набора можно.

Задать параметр можно в основном окне при создании резервной копии:

Путь расположения резервных копий

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

Кликаем правой кнопкой мыши по корневому разделу SQL Server и выбираем свойства:

Переходим в раздел Параметры баз данных (1) - в подразделе «Места хранения, используемые базой данных по умолчанию» мы увидим путь до места размещения резервных копий (2), который можно поменять кнопкой справа (3):

Серверы баз данных - одни из ключевых в любой организации. Именно они хранят информацию и предоставляют выдачу по запросу, и крайне важно сохранить БД в любой ситуации. Базовая поставка обычно включает нужные утилиты, но админу, не сталкивавшемуся до этого с БД, придется некоторое время разбираться с особенностями работы, чтобы обеспечить автоматизацию.

Виды бэкапов баз данных

Для начала разберемся с тем, какие вообще бывают бэкапы. Сервер баз данных не является обычным десктопным приложением, и, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable), используется ряд технологий, а поэтому создание и восстановление БД из архива имеет свои особенности. Существуют три различных подхода к резервному копированию данных, каждый из которых имеет свои плюсы и минусы.

При логическом, или SQL, бэкапе (pg_dump, mysqldump, SQLCMD) создается мгновенный снимок содержимого базы с учетом транзакционной целостности и сохраняется в виде файла с SQL-командами (можно выбрать всю базу или отдельные таблицы), при помощи которого можно воссоздать базу данных на другом сервере. На это требуется время (особенно для больших баз) для сохранения и восстановления, поэтому очень часто эту операцию выполнять нельзя и ее производят во время минимальной нагрузки (например, ночью). При восстановлении администратору необходимо будет выполнить несколько команд, чтобы подготовить все необходимое (создать пустую базу данных, учетные записи и прочее).

Физический бэкап (уровня файловой системы) - копирование файлов, которые СУБД использует для хранения данных в базе данных. Но при простом копировании игнорируются блокировки и транзакции, которые, скорее всего, будут неправильно сохранены и нарушены. При попытке присоединить этот файл он будет в несогласованном состоянии и приведет к ошибкам. Чтобы получить актуальный бэкап, базу данных нужно остановить (можно уменьшить время простоя, использовав два раза rsync - вначале на работающей, потом на остановленной). Недостаток этого метода очевиден - нельзя восстановить определенные данные, только всю базу данных. При старте БД, восстановленной из архива файловой системы, нужно будет провести проверку на целостность. Здесь используются разные вспомогательные технологии. Например, в PostgreSQL логи упреждающей журнализации WAL (Write Ahead Logs) и специальная функция (Point in Time Recovery - PITR), позволяющая вернуться к определенному состоянию базы. С их помощью легко реализуется третий сценарий, когда бэкап уровня файловой системы объединяется с резервной копией WAL-файлов. Вначале восстанавливаем файлы резервной копии файловой системы, а затем при помощи WAL база приводится к актуальному состоянию. Это чуть более сложный подход для администрирования, но зато нет проблем с целостностью БД и восстановлением баз до определенного времени.

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

Barman

Лицензия: GNU GPL

Поддерживаемые СУБД: PostgreSQL

PostgreSQL поддерживает возможности физического и логического бэкапа, добавляя к ним еще один уровень WAL (см. врезку), который можно назвать непрерывным копированием. Но управлять при помощи штатных инструментов несколькими серверами не очень удобно даже админу со стажем, а в случае сбоя счет идет на секунды.

Barman (backup and recovery manager) - внутренняя разработка компании 2ndQuadrant, предоставляющей услуги на базе PostgreSQL. Предназначен для физического бэкапа PostgreSQL (логический не поддерживает), архивирования WAL и быстрого восстановления после сбоев. Поддерживаются удаленный бэкап и восстановление нескольких серверов, функции point-in-time-recovery (PITR), управление WAL. Для копирования и подачи команд на удаленный узел используется SSH, синхронизация и бэкап при помощи rsync позволяет сократить трафик. Также Barman интегрируется со стандартными утилитами bzip2, gzip, tar и подобными. В принципе, можно использовать любую программу сжатия и архивирования, интеграция не займет много времени. Реализованы различные сервисные и диагностические функции, позволяющие контролировать состояние сервисов и регулировать полосу пропускания. Поддерживаются Pre/Post-скрипты.

Barman написан на Python, управление политиками резервного копирования производится при помощи понятного INI-файла barman.conf, который может находиться в /etc или домашнем каталоге пользователя. В поставке идет готовый шаблон с подробными комментариями внутри. Работает только на *nix-системах. Для установки в RHEL, CentOS и Scientific Linux следует подключить EPEL - репозиторий, в котором содержатся дополнительные пакеты. В распоряжении пользователей Debian/Ubuntu официальный репозиторий:

$ sudo apt-get install barman

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

Sypex Dumper

Лицензия: BSD

Поддерживаемые СУБД: MySQL

Вместе с MySQL поставляются утилиты mysqldump, mysqlhotcopy, позволяющие легко создать дамп базы данных, они хорошо документированы, и в интернете можно найти большое количество готовых примеров и фронтендов. Последние позволяют новичку быстро приступить к работе. Sypex Dumper представляет собой PHP-скрипт, позволяющий легко создать и восстановить копию базы данных MySQL. Создавался для работы с большими базами данных, работает очень быстро, понятен и удобен в использовании. Умеет работать с объектами MySQL - представлениями, процедурами, функциями, триггерами и событиями.

Еще один плюс, в отличие от других инструментов, при экспорте производящих перекодирование в UTF-8, - в Dumper экспорт производится в родной кодировке. Результирующий файл занимает меньше места, а сам процесс происходит быстрее. В одном дампе могут быть объекты с разными кодировками. Причем легко импорт/экспорт произвести в несколько этапов, останавливая процесс во время нагрузки. При возобновлении процедура начнется с места остановки. При восстановлении поддерживается четыре варианта:

• CREATE + INSERT - стандартный режим восстановления;
• TRUNCATE + INSERT - меньше времени на создание таблиц;
• REPLACE - восстанавливаем в рабочей базе старые данные, не затирая новые;
• INSERT IGNORE - добавляем в базу удаленные или новые данные, не трогая существующие.

Поддерживается сжатие копии (gzip или bzip2), автоудаление старых бэкапов, реализован просмотр содержимого дамп-файла, восстановление только структуры таблиц. Имеются и сервисные функции по управлению БД (создание, удаление, проверка, восстановление БД, оптимизация, очистка таблиц, работа с индексами и другое), а также файл-менеджер, позволяющий копировать файлы на сервер.

Управление производится при помощи веб-браузера, интерфейс с использование AJAX локализован из коробки и создает впечатление работы с настольным приложением. Также возможно запускать задания из консоли и по расписанию (через cron).

Для работы Dumper понадобится классический L|WAMP-сервер, установка обычная для всех приложений, написанных на PHP (копируем файлы и устанавливаем права), и не будет сложной даже для новичка. Проект предоставляет подробную документацию и видеоуроки, демонстрирующие работу с Sypex Dumper.

Есть две редакции: Sypex Dumper (бесплатно) и Pro (10 долларов). Вторая имеет больше возможностей, все отличия приведены на сайте.

SQL Backup And FTP

Лицензия:

Поддерживаемые СУБД: MS SQL Server

MS SQL Server - одно из популярных решений, а потому встречается достаточно часто. Задание резервного копирования создается при помощи среды SQL Server Management Studio, собственно Transact-SQL и командлетов модуля SQL PowerShell (Backup-SqlDatabase). На сайте MS можно найти просто огромное количество документации, которая позволяет разобраться с процессом. Документация хотя и полная, но очень специфическая, а информация в интернете часто противоречит друг другу. Новичку действительно вначале потребуется потренироваться, «набив руку», поэтому, даже несмотря на все сказанное, сторонним разработчикам есть где развернуться. К тому же бесплатная версия SQL Server Express не может похвастаться встроенными инструментами для резервного копирования. Для более ранних версий MS SQL (до 2008) можно найти бесплатные утилиты, например SQL Server backup , но в большинстве подобные проекты уже коммерциализировались, хотя и предлагают всю функциональность часто за символическую сумму.

Например, разработка SQL Backup And FTP и One-Click SQL Restore соответствует принципу «настроил и забыл». Обладая очень простым и понятным интерфейсом, они позволяют создавать копии баз данных MS SQL Server (включая Express) и Azure, сохранять зашифрованные и сжатые файлы на FTP и облачных сервисах (Dropbox, Box, Google Drive, MS SkyDrive или Amazon S3), результат можно тут же просмотреть. Возможен запуск процесса как вручную, так и по расписанию, отправка сообщения о результате задания по email, запуск пользовательских скриптов.

Поддерживаются все варианты бэкапа: полный, дифференциальный, журнал транзакций, копирование папки с файлами и многое другое. Старые резервные копии удаляются автоматически. Для подключения к виртуальному узлу используется SQL Management Studio, хотя здесь могут быть нюансы и это будет работать не во всех таких конфигурациях. Для загрузки предлагается пять версий - от бесплатной Free до навороченной Prof Lifetime (на момент написания этих строк стоила всего 149 долларов). Функционала Free вполне достаточно для небольших сетей, в которых установлено один-два SQL-сервера, все основные функции активны. Ограничено количество резервных БД, возможность отправки файлов на Google Drive и SkyDrive и шифрование файлов. Интерфейс хотя и не локализован, но очень прост и понятен даже новичку. Нужно лишь подключиться к SQL-серверу, после чего будет выведен список баз данных, следует отметить нужные, настроить доступ к удаленным ресурсам и указать время выполнения задания. И все это в одном окне.

Но есть одно «но». Сама программа не предназначена для восстановления архивов. Для этого предлагается отдельная бесплатная утилита One-Click SQL Restore, понимающая и формат, созданный командой BACKUP DATABASE. Админу необходимо лишь указать архив и сервер, на который восстановить данные, и нажать одну кнопку. Но в более сложных сценариях придется использовать RESTORE.

Особенности бэкапа MS SQL Server

Создание резервной копии и восстановление СУБД имеет свои отличия, которые нужно учитывать, особенно их много при переносе архива на другой сервер. Для примера разберем некоторые нюансы MS SQL Server. Для архивирования при помощи Transact-SQL следует использовать команду BACKUP DATABASE (есть и разностная DIFFERENTIAL) и журнал транзакций BACKUP LOG.

Если резервная копия разворачивается на другом сервере, нужно убедиться, что присутствуют те же самые логические диски. Как вариант - можно вручную прописать правильные пути для файлов базы данных, используя опцию WITH MOVE команды RESTORE DATABASE.

Простая ситуация - бэкап и перенос баз на другие версии SQL Server. Эта операция поддерживается, но в случае SQL Server будет работать, если версия сервера, на которой разворачивается копия, такая же или новее, чем та, на которой она создавалась. Причем есть ограничение: новее не более чем на две версии. После восстановления БД будет находиться в режиме совместимости с той версией, с которой осуществлялся переход, то есть новые функции будут недоступны. Это легко поправить, изменив COMPATIBILITY_LEVEL. Можно это сделать при помощи GUI или SQL.

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

Определить, на какой версии была создана копия, можно, просмотрев заголовок файла архива. Чтобы не экспериментировать, при переходе на новую версию SQL Server следует запустить бесплатную утилиту Microsoft Upgrade Advisor.

Iperius

Лицензия: коммерческая, есть версия Free

Поддерживаемые СУБД: Oracle 9–11, XE, MySQL, MariaDB, PostgreSQL и MS SQL Server

Когда приходится управлять несколькими типами СУБД, без комбайнов не обойтись. Выбор большой. Например, Iperius - легкая, очень простая в использовании и одновременная мощная программа для резервного копирования файлов, имеющая функцию горячего резервирования баз данных без прерывания в работе или блокирования. Обеспечивает полный или инкрементальный бэкап. Может создавать полные образы дисков для автоматической переустановки всей системы. Поддерживает резервное копирование на NAS, USB-устройства, стример, FTP/FTPS, Google Drive, Dropbox и SkyDrive. Поддерживает сжатие zip без ограничения в размере файлов и AES256-шифрование, запуск внешних скриптов и программ. Включает весьма функциональный планировщик заданий, возможно параллельное или последовательное выполнение нескольких заданий, результат отправляется на email. Поддерживаются многочисленные фильтры, переменные для персонализации путей и настроек.

Возможность закачки по FTP позволяет легко обновлять информацию на нескольких веб-сайтах. Открытые файлы резервируются при помощи технологии VSS (теневого копирования томов), что позволяет производить горячий бэкап не только файлов СУБД, но и других приложений. Для Oracle также задействуется средство организации резервного копирования и восстановления данных RMAN (Recovery Manager). Чтобы не перегружать канал, есть возможность настройки полосы пропускания. Управление резервированием и восстановлением производится при помощи локальной и веб-консоли. Все функции на виду, поэтому для настройки задания потребуется лишь понимание процесса, в документацию заглядывать даже не придется. Просто следуем подсказкам мастера. Также можно отметить менеджер учетных записей, что очень удобно при большом количестве систем.

Базовые функции предлагаются бесплатно, но возможность резервирования БД заложена только в версиях Advanced DB и Full. Поддерживается установка от XP до Windows Server 2012.

Handy Backup

Лицензия: коммерческая

Поддерживаемые СУБД: Oracle, MySQL, IBM DB2 (7–9.5) и MS SQL Server

Одна из самых мощных систем управления реляционными базами данных - IBM DB2, имеющая уникальные функции по масштабированию и поддерживающая множество платформ. Поставляется в нескольких редакциях, которые построены на одной базе и отличаются функционально. Архитектура баз данных DB2 позволяет управлять практически всеми типами данных: документами, XML, медиафайлами и так далее. Особо популярна бесплатная DB2 Express-C. Бэкап очень прост:

Db2 backup db sample

Или снапшот, использующий функцию Advanced Copy Services (ACS):

Db2 backup db sample use snapshot

Но нужно помнить, что в случае снимков мы не можем восстанавливать (db2 recover db) отдельные таблицы. Есть и возможности по автоматическому бэкапу, и многое другое. Продукты хорошо документированы, хотя в русскоязычном интернете руководства встречаются редко. Также далеко не во всех специальных решениях можно найти поддержку DB2.

Например, Handy Backup позволяет выполнять бэкап нескольких типов серверов баз данных и сохранять файлы практически на любой носитель (жесткий диск, CD/DVD, облачное и сетевое хранилище, FTP/S, WebDAV и другие). Возможен бэкап баз данных через ODBC (только таблицы). Это одно из немногих решений, поддерживающих DB2, и к тому же имеет логотип «Ready for IBM DB2 Data Server Software». Вся процедура выполняется при помощи обычного мастера, в котором необходимо лишь выбрать нужный пункт и сформировать задачу. Сам процесс настройки настолько прост, что разобраться сможет и новичок. Можно создать несколько заданий, которые будут запускаться по расписанию. Результат фиксируется в журнале и отправляется по email. Во время работы задания остановка сервиса не требуется. Архив автоматически сжимается и шифруется, что гарантирует его безопасность.

Работу с DB2 поддерживают две версии Handy Backup - Office Expert (локальный) и Server Network (сетевой). Работает на компьютерах под управлением Win8/7/Vista/XP или 2012/2008/2003. Сам процесс развертывания несложен для любого админа.

А также: бэкап SQL, бэкап 1С.

Серверная 1С содержит данные в базе данных, которая находится на SQL сервере. Сегодня мы рассматриваем вариант MS SQL 2005/2008.

Чтобы данные не были потеряны в случае сгоревшего диска сервера или других форс-мажорных ситуаций – необходимо с самого начала делать бэкапы (backup).

Делать ручками каждый день Backup SQL базы 1С конечно никто не хочет. Для этого есть автоматические средства. Познакомимся с ними.

Настройка Backup SQL

Настройка Backup SQL для базы 1С ничем не отличается от настройки бэкапа для любой другой базы данных.

Для настройки запустите MS SQL Management Studio. Эта программа находится в группе программ MS SQL.

Добавление задания бэкапа SQL базы 1С

Задания автоматического бэкапа баз SQL находятся в ветке Management / Maintenance plans.

Чтобы добавить новое задание бэкапа щелкните на группу Maintenance plans правой кнопкой мыши и выберите New Maintenance Plan.

Введите название задания. Название имеет значение только для Вас. На всякий случай лучше использовать английские символы.

Настройка задания бэкапа SQL базы 1С

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

Список вариантов операций выведен слева внизу. Выберите Back Up Database Task двойной кнопкой мыши или просто перетащите вправо.

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

В окне настройки выберите нужные базы SQL 1С (можно сразу несколько или по одной).

Выберите место сохранения бэкапа базы SQL 1С. Необходимо выбрать физически другой винчестер. Организационно можно поставить галочку «Создать подпапки».

Теперь настроим расписание backup. Расписание backup по-умолчанию добавилось само. Но Вы можете добавить несколько расписаний (например, одно – ежедневное, одно – еженедельное и т.п.). Нажмите кнопку настройки расписания backup.

На скриншоте пример ежедневного Backup SQL базы 1С в 3 ночи.

Чтобы расписание backup в списке было красиво-понятным, его можно изменить.

Сохранение задания бэкапа SQL базы 1С

Нажмите записать. Задание появится слева в списке.

Это важно! Проверьте правильность создания задания Backup SQL базы. Для этого нажмите на задании правой кнопкой и выберите Execute.

В результате должен появится файл бэкапа по указанному пути. Если что-то не так – удалите задание (Del) и начните с начала.