Восстановление базы данных sql из резервной копии: Восстановление базы данных из резервной копии в MS SQL Server 2012

Содержание

Восстановление главной базы данных SQL

Восстановление главной базы данных SQL

Восстановление главной базы данных SQL

Повреждение главной базы данных может проявляться
следующим образом:

  • Невозможно запустить SQL.

  • Сбой сегментации или ошибки ввода-вывода.

  • Отчет, созданный утилитой проверки согласованности
    базы данных SQL Database Consistency Checker (DBCC).

Если удается запустить
SQL, можно восстановить последнюю резервную копию главной базы
данных с помощью опции Автоматическое восстановление главной базы
данных в окне Свойства задания восстановления SQL программы Backup
Exec, а затем при необходимости восстановить другие базы
данных.

При критическом
повреждении главной базы данных, если невозможно запустить SQL,
вместо запуска утилиты Rebuild Master или переустановки SQL можно
заменить поврежденные или утерянные базы данных копиями главной и
модельной баз данных, которые автоматически создаются и обновляются
программой Backup Exec при выполнении резервного копирования этих
баз данных. После запуска SQL можно восстановить последнюю копию
главной базы данных с помощью опции Автоматическое восстановление
главной базы данных программы Backup Exec, а затем при
необходимости восстановить другие базы данных.

Если не были созданы
копии главной и модельной баз данных, следует повторно создать
главную базу данных с помощью утилиты Microsoft rebuildm.exe и
запустить SQL.

Поскольку при
восстановлении резервной копии все изменения, внесенные в главную
базу данных после проведения последнего резервного копирования,
будут утеряны, необходимо повторно внести необходимые изменения.
Если после резервного копирования главной базы данных были созданы
какие-либо пользовательские базы данных, доступ к этим базам данных
невозможен до их восстановления из резервных копий либо до
повторного подключения к SQL.

Как перезапустить SQL при использовании копий баз
данных

  1. Убедитесь, что имеются
    копии баз данных.

    Копии базы данных
    называются master$4idr, mastlog$4idr, model$4idr и modellog$4idr и
    находятся в следующих каталогах:

    В экземпляре SQL 2000 с параметрами по умолчанию, базы данных
    находятся в следующем каталоге:

    C:\Program Files\Microsoft SQL
    Server\MSSQL\Data\*. *

    В именованном экземпляре SQL 2000 базы данных находятся в
    следующем каталоге:

    C:\Program Files\Microsoft SQL
    Server\MSSQL$Instance_Name\Data\*.*

    В первом экземпляре SQL 2005 базы данных находятся в следующем
    каталоге:

    C:\Program Files\Microsoft SQL
    Server\MSSQL.1\MSSQL\Data\*.*

    Во втором экземпляре SQL 2005 базы данных находятся в следующем
    каталоге:

    C:\Program Files\Microsoft SQL
    Server\MSSQL.2\MSSQL\Data\*.*

    В экземпляре SQL 7.0 с параметрами по умолчанию, базы данных
    находятся в следующем каталоге:

    C:\MSSQL7\Data

    В экземпляре SQL 2008 с параметрами по умолчанию, базы данных
    находятся в следующем каталоге:

    C:\Program Files\Microsoft SQL
    Server\MSSQL10.##~_#lt;имя-экземпляра##~_#gt;\MSSQL\Data

    При необходимости
    восстановите копии главной и модельной баз данных из наборов данных
    резервного копирования в исходный каталог этих баз данных.

  2. С помощью Проводника Windows откройте каталог
    данных по умолчанию и удалите следующие файлы:

    • master.mdf

    • mastlog.ldf

    • model.mdf

    • modellog.ldf.

  3. Откройте командную строку
    и удалите исходные главную и модельную базы данных и их журналы
    транзакций.

  4. Переименуйте копии баз
    данных, восстановив исходные имена.

    Эти имена указаны
    ниже:

    Имя копии базы данных

    Исходное имя базы данных

    master$4idr

    master.mdf

    master$4idr

    mastlog.ldf

    model$4idr

    model.mdf

    modellog$4idr

    modellog. ldf

    Не используйте файлы,
    доступные только для чтения. Это приведет к сбою при запуске служб
    SQL.

  5. В SQL 2000 или SQL 2005
    запустите SQL Server с помощью функции SQL Service Control Manager.
    При работе с SQL 7.0 запустите SQL с помощью SQL Server Service
    Manager.

  6. Для восстановления
    последних изменений в главной базе данных выполните следующие
    действия:

Как восстановить главную базу данных

  1. В панели навигации
    щелкните на стрелке рядом со значком Восстановление.

  2. Выберите .

  3. В панели свойств найдите
    раздел Источник и нажмите .

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

  5. В панели Свойства в
    разделе Параметры выберите .

  6. В окне Свойства задания
    восстановления для SQL выберите .

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

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

    Если у программы Backup
    Exec нет доступа к записям реестра SQL,
    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server и
    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer, может оказаться
    невозможным восстановление в каталог по умолчанию и будет
    недоступна опция Автоматическое восстановление главной базы данных
    в окне свойств задания восстановления для SQL. Для того чтобы
    убедиться в наличии прав доступа у программы Backup Exec,
    проверьте, используются ли в учетной записи права доступа
    администратора к системе, в которой запущен экземпляр SQL.

  7. Выберите способ проверки
    согласованности, выполняемой после восстановления.

  8. Запустите задание
    восстановления.

    После восстановления SQL
    перезапускается в многопользовательском режиме.

  • Перевести на английский

Базовые принципы восстановления для администратора | Windows IT Pro/RE

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

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

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

Типы восстановления

Система Microsoft SQL Server предусматривает использование нескольких видов резервных копий. Подобным же образом в этой системе может применяться несколько типов восстановления базы данных, которая вышла из строя, или части базы данных.

В Microsoft SQL Server используются следующие базовые типы восстановления:

  • восстановление базы данных;
  • восстановление журналов регистрации транзакций;
  • восстановление файлов и групп файлов;
  • восстановление страниц.

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

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

Восстановление базы данных

Чтобы восстановить базу данных, требуется по крайней мере один резервный файл, и это должен быть файл с полной резервной копией. При использовании одного резервного файла, если процесс пройдет нормально, вы получите базу данных, приведенную к состоянию на момент формирования этой резервной копии. Это будет копия базы данных в указанный момент за вычетом неподтвержденных транзакций. Восстановление баз данных — общепринятый метод перемещения копий баз данных с сервера на сервер для выполнения таких операций, как тестирование и диагностика; при этом размеры базы данных не должны превышать разумные пределы («разумные пределы» предполагают практическую возможность работы с базой данных с учетом ограничений по времени и объему дискового пространства, необходимого для ее перемещения).

Однако возможности администратора ни в коем случае не ограничиваются сценарием, когда он располагает всего лишь одним резервным файлом и может вернуть базу данных только в то состояние, в каком она находилась в момент получения последней резервной копии. Вы можете объединить несколько резервных копий (различных типов), чтобы успешно привести базу данных в состояние на момент, непосредственно предшествующий сбою или повреждению, если у вас возникнет такое желание. Нужно только выбрать правильную стратегию резервного копирования и восстановления. Если ваша база данных находится в режиме протоколирования, вы можете сформировать цепочку резервных копий для выполнения этой задачи: привести базу данных в состояние, предшествующее точке сбоя. В данном случае это делается так: сначала нужно применить основную резервную копию вашей базы данных, затем, возможно, разностную резервную копию и, наконец, одну или несколько резервных копий журналов регистрации транзакций до интересующего вас момента. Этот момент может совпадать с концом последней применяемой копии журнала регистрации транзакций или приходиться на середину этой последней копии журнала.

Базовая резервная копия подготовит вашу платформу к процессу восстановления. Здесь вы можете остановиться, если для удовлетворения ваших потребностей в деле восстановления вам не нужно будет использовать последующие резервные копии. Если же вам нужно двигаться в сторону восстановления на определенный момент, то в вашем распоряжении набор разностных резервных копий и копий журнала регистрации транзакций. Как отмечалось в предыдущей статье, в разностные копии включаются все страницы базы данных, измененные с момента получения последней полной резервной копии. Это означает, что если полные резервные копии формируются у вас по понедельникам в полдень, а разностные резервные копии — в 11 часов утра ежедневно, то размеры каждой последующей разностной копии будут увеличиваться до полудня следующего понедельника, когда будет создана очередная полная резервная копия. Я исхожу из того, что работа с базой данных осуществляется на регулярной основе и что в ходе этой работы либо модифицируются данные с помощью операторов INSERT, UPDATE или DELETE, либо изменяется структура объектов, входящих в базу данных. При таком графике, если у вас происходит сбой в среду в 13:00, вы первым делом восстановите полную резервную копию, полученную в понедельник, а затем разностную резервную копию от среды. Далее вы примените резервные копии журнала регистрации транзакций с этого момента, с тем чтобы привести базу данных к состоянию на момент, предшествующий 13:00. Надеюсь, вы резервируете журналы регистрации транзакций, если для вас важно иметь возможность восстановления по этой схеме?

Теперь рассмотрим такую ситуацию. Сбой у вас произошел в среду в 9 часов утра. Теперь вам предстоит выполнить несколько больший объем работ, так как вы не сможете использовать полученную в среду разностную копию в качестве отправной точки для повторения транзакций из копий журнала регистрации транзакций, ведь ваша разностная копия была создана уже по прошествии точки сбоя, к которой вы хотите привести базу данных. Вам придется использовать полную резервную копию за понедельник, а затем разностную копию за вторник. После этого вам потребуется применить столько резервных копий журнала транзакций, сколько нужно для того, чтобы «добраться» до 9 утра в среду от точки формирования разностной копии, полученной во вторник.

Я описал эти процессы, поскольку вам необходимо иметь представление о двух основных положениях, касающихся разностных резервных копий: во-первых, вы не должны применять несколько разностных резервных копий, и, во-вторых, разностные копии «не понимают», когда именно была совершена транзакция, модифицировавшая данные или структуру базы данных. Пытаясь привести базу данных к состоянию на определенный момент времени, вы не сможете остановиться на полпути в процессе восстановления из разностной резервной копии.

Восстановление, без восстановления, ожидание

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

1. Восстановление

Когда база данных находится в восстановленном состоянии, это означает, что она готова к работе и у вас нет возможности восстанавливать дополнительные копии с целью сдвинуть состояние базы далее по оси времени. Чтобы выполнить цепочку операций по восстановлению, вы не можете нарушить зафиксированную в журнале последовательность транзакций, которые изменяли эту базу. Данную журнальную последовательность можно представить как историю базы данных. При регистрации каждой транзакции присваивается специальный номер, log sequence number (LSN). В режиме восстановления с протоколированием при восстановлении полных, разностных резервных копий, а также резервных копий журнала регистрации транзакций эти резервные копии должны быть упорядочены и применены таким образом, чтобы порядок номеров LSN не нарушался. Если бы вы позволили базе данных перейти в состояние восстановления и пользователи начали бы выполнять новые транзакции с использованием объектов этой базы данных, вы не смогли бы в дальнейшем применять резервные копии, поскольку в такой ситуации цепочка была бы разорвана новыми транзакциями, получающими новые номера LSN. Чтобы перевести базу данных в восстановленное состояние в конце процесса восстановления, нужно использовать ключевое слово RECOVERY в предложении WITH команды на восстановление. Синтаксис мы рассмотрим в конце статьи.

2. Без восстановления

Если база данных приведена в состояние восстановления, это отображается в обозревателе объектов среды SQL Server Management Studio. Это означает, что база данных находится в состоянии, в котором по крайней мере один резервный файл был восстановлен и база данных готова принять следующую резервную копию в цепочке, будь то разностная резервная копия или резервная копия журнала регистрации транзакций. Кроме того, это может означать, что пользователь, восстанавливавший последнюю резервную копию, не привел базу данных в состояние восстановления с помощью упомянутого выше ключевого слова RECOVERY. База данных в состоянии восстановления может принимать последующие резервные файлы в процессе восстановления, но не может санкционировать какие-либо действия над собой со стороны конечных пользователей, включая даже операции считывания, которые никоим образом не изменяют данные. Если база данных пребывала в восстановленном состоянии, ее нельзя привести в состояние восстановления, в котором она будет принимать новые действия в процессе восстановления, по причинам, изложенным выше. Если вы хотите, чтобы база данных в процессе восстановления могла принимать дополнительные разностные резервные копии или резервные копии журналов регистрации транзакций, проследите за тем, чтобы в предложение WITH команды восстановления было включено ключевое слово NO_RECOVERY.

3. Ожидание

База данных в режиме ожидания пребывает в состоянии неопределенности: она допускает выполнение пользователями операций чтения, а если ее чуть-чуть «подтолкнуть» к этому, может предпринимать дополнительные действия по восстановлению. Чтобы гарантировать такую возможность, а также обеспечить стабильное и единообразное состояние данных для работы с запросами, база данных должна отменить все незавершенные транзакции. Если бы она отменила эти транзакции, вы не смогли бы восстанавливать последующие резервные копии, поскольку цепочка номеров LSN была бы разорвана. Так вот, в этих условиях SQL Server сохраняет упомянутые незавершенные транзакции в файле UNDO. Прежде чем приступать к восстановлению последующих журналов регистрации транзакций, SQL Server придется привести базу данных в состояние восстановления, применить хранимые в файле UNDO транзакции и затем, наконец, восстановить следующий журнал регистрации транзакций в цепочке резервирования. Чтобы привести базу данных в состояние ожидания, необходимо ввести в предложение WITH инструкции восстановления ключевое слово STANDBY, а также указать путь к файлу UNDO.

Резервные копии журнала регистрации транзакций

К этому моменту я уже наметил основные контуры концепции резервных копий журналов регистрации транзакций. Вы можете использовать эти резервные файлы для повторения транзакций восстанавливаемой базы данных до любого момента времени внутри периода, охватываемого данной резервной копией журнала регистрации транзакций. Все незавершенные транзакции в конце резервной копии остаются незавершенными; предполагается, что вы сможете применить к восстанавливаемой базе данных дополнительные резервные копии. Если вы захотите привести базу данных в состояние восстановления, RECOVERY, она отменит эти незавершенные транзакции. Если же вы выберете вариант без восстановления, NO_RECOVERY, она оставит их в неизменном и незавершенном состоянии. Выберите вариант STANDBY, и будет выполнена отмена этих незавершенных транзакций, после чего они будут сохранены в файле UNDO. В резервных копиях журналов регистрации транзакций хранится вся информация, необходимая для применения транзакций в порядке времени выполнения и без конфликтов, как если бы вы воспроизводили запись проведенных внутри базы данных операций. В сущности, именно это вы и делаете, когда применяете резервную копию журнала регистрации транзакций.

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

В этом разделе, а также в следующем, с помощью языка Transact-SQL используются резервные копии моей базы данных SQL_Cruise, пребывающей в режиме полного протоколирования, в том смысле, что все транзакции регистрируются и что я при желании могу воспроизвести состояние на тот или иной момент времени, если у меня имеются файлы резервных копий, полученные на указанный момент (см. экран 1).

 

Экран 1. История резервирования тестовой базы данных

Кроме того, мы узнаем, что кто-то удалил некую весьма важную таблицу в 20:26 30 августа 2016 года с помощью следующей команды:

DROP TABLE Very_Important_Table;

Процесс восстановления: процесс ГИП — T-SQL

Во время теста я буду пользоваться последней версией SQL Server Management Studio. Интерфейсы более ранних версий будут выглядеть похоже, но могут отличаться в каких-то деталях.

Формы восстановления размещаются по щелчку правой кнопкой мыши на базе данных, которую вы хотите восстановить. Далее перемещайтесь по последующим всплывающим меню, пока не дойдете до разных типов восстановления, которые можете использовать (см. экран 2).

 

Экран 2. Выбор типа восстановления

В данном примере нам нужно использовать восстановление базы данных. Когда на экране отобразится форма Restore Database, в ней будут заполнены необходимые значения, позволяющие вам дойти до позднейшей точки восстановления, на которую могут вывести ваши резервные копии на базе сохраненной истории резервирования. В данном случае мы начинаем с последней полной резервной копии, последней разностной копии, сформированной до момента, который SQL Server считает вашей точкой восстановления — последним моментом времени, до которого возможно восстановление; далее следуют журналы регистрации транзакций, которые помогут вам добраться до этого момента (см. экран 3).

 

Экран 3. Восстановление базы данных

Если мы хотим вернуть базу данных к состоянию на последний из возможных моментов времени, нам нужно перейти на страницы Files и Options в этой форме. Если вам нужно сделать это сейчас, тогда, конечно, переходите к следующему параграфу, где рассматриваются упомянутые страницы. Тех же из вас, кого по-прежнему интересует восстановление состояния на определенный момент, прошу следить за тем, как я нажимаю кнопку Timeline…

На экране появляется форма, подобная той, что вы видите на экране 4, для SQL Server Management Studio 2016. Единственное различие состоит в том, что в вашей форме будет выбрана настройка Last backup taken. Я же сделал еще один шаг: указал момент времени, по состоянию на который необходимо восстановить базу данных.

 

Экран 4. Указание периода времени для восстановления базы данных

Если теперь я нажму кнопку ОК, мы воссоединимся с остальными читателями на странице Files (см. экран 5). Эта страница используется для того, чтобы дать вам возможность осуществить восстановление в другом месте. Данная возможность пригодится в тех случаях, когда возникает необходимость восстановить копию базы данных на другом сервере и, возможно, с другой файловой структурой. Когда дело дойдет до синтаксиса, к базовому коду нужно будет добавить оператор MOVE, как мы увидим позже. Поскольку в данный момент происходит восстановление существующей базы данных, здесь мы не будем вносить изменения.

 

Экран 5. Указание места для восстановления базы данных

Наконец, мы переходим на страницу Options. В нашем примере я внес соответствующие изменения, но каждое из них разъясняется ниже (см. экран 6).

 

Экран 6. Дополнительные параметры для восстановления

Варианты восстановления

Эти варианты определяют, что произойдет с нашей базой данных по завершении процесса восстановления.

  • Overwrite the existing database («Записать поверх существующей базы данных»). Мы делаем такой выбор, так как нам нужно заменить испорченный кем-то текст. В этом случае мы потеряем все транзакции, совершенные с того момента, к которому вы должны привести базу данных в восстановленном состоянии.
  • Если бы мы имели дело с базой данных в процессе репликации, нам следовало бы рассмотреть вариант Preserve the replication settings («Сохранить настройки репликации») с целью сохранения возможности возвращения базы данных в нашу схему репликации.
  • Настройка Restrict access to the restricted database («Ограничить доступ к защищенной базе данных») заблокирует возможность общего доступа к базе данных. Ее можно использовать для восстановления и последующей диагностики базы данных без предоставления пользователям права работать с ней.

Состояние восстановления

Эта настройка была подробно описана выше. Вариант RESTORE WITH RECOVERY выбирается для того, чтобы вновь подключить базу данных к сети. Как видите, мы можем указать файл UNDO, если хотим перевести эту базу данных в состояние STANDBY в конце процесса восстановления.

Резервирование остатка журнала

Резервное копирование остатка, или «хвоста», журнала — это особый процесс, состоящий из двух фаз: выполняется резервная копия последнего журнала регистрации транзакций, а затем база данных переводится в состояние, блокирующее все попытки соединения с ней, за исключением соединения с целью получения резервной копии остатка журнала (записей в журнале транзакций, появившихся там с момента последней операции резервного копирования этого журнала) и выполнения последующего процесса восстановления. Восстановление базы данных невозможно, если к ней кто-то подключен. При этом достигаются две цели: мы получаем «сквозную» резервную копию вплоть до того момента, когда база данных в последний раз была в рабочем состоянии (это на тот случай, если кто-нибудь передумает и не захочет восстанавливать базу данных на указанный нами момент), а также гарантию того, что мы можем выполнить восстановление, разорвав все существующие соединения. При восстановлении с протоколированием редко возникают основания для того, чтобы не выполнять эту проверку в качестве меры предосторожности.

Последние настройки дают возможность форсированно переводить базу данных в режим работы с одним пользователем (в случае, если вы не выполняете резервную копию остатка журнала и не работаете в режиме простого восстановления Simple), а также получать уведомления перед восстановлением каждой резервной копии.

Теперь мы готовы привести базу данных к состоянию на тот момент восстановления, который был указан. Но перед тем как мы это сделаем, я хочу показать вам код, который будет выполнять данный процесс. Почти во всех диалоговых окнах SQL Server Management Studio предусмотрена возможность в фоновом режиме записывать в виде сценария выполняемые действия в новое окно запроса, в буфер обмена или в задание агента SQL Server, с тем чтобы выполнить их позднее. Если я переведу запись в новое окно запроса и подчищу сценарий, чтобы окно выглядело более презентабельно, вы сможете увидеть все шаги, отраженные в коде. Они представлены в листинге 1 (примечания мои: понятно, что SQL — язык хороший, но не настолько).

Обратите внимание на то, что в сгенерированном коде имеется два параметра, применяемых только при использовании ленты: NOUNLOAD и NOREWIND. При выполнении операций, не предусматривающих использование ленты, они игнорируются, но тем не менее генерируются по умолчанию. Их можно игнорировать. NOSKIP, как вы увидите в резервной копии остатка журнала, определяет, следует ли в ходе операции резервирования перед началом записи поверх другой информации осуществлять проверку истечения срока действия резервных копий на различных носителях записи. Здесь этот параметр тоже добавляется по умолчанию и может быть проигнорирован.

Как мы видим из кода, база данных будет переведена в однопользовательский режим, при этом все сеансы, предусматривающие подключение к базе данных, будут разорваны, а незавершенные транзакции будут подвергнуты процедуре отмены. Это даст возможность сформировать финальную резервную копию журнала регистрации транзакций, сохраняющую состояние базы данных непосредственно перед ее перезаписью как часть процесса восстановления. По завершении формирования резервной копии остатка журнала мы проходим через процессы полного, разностного восстановления и восстановления журнала. После окончания каждого процесса база данных остается в состоянии без восстановления, norecovery; таким образом обеспечивается возможность выполнения следующей операции восстановления. Наконец, мы переходим к резервной копии журнала регистрации транзакций, где указывается момент времени, на который мы хотим восстановить данные. С помощью команды STOPAT мы можем задать условие, при котором процесс не будет воспроизводить транзакции, совершенные после указанного момента. Возможно, вы обратили внимание и на то, что здесь также происходит восстановление с параметром WITH RECOVERY, исключающее возможность выполнения новых операций по восстановлению. Наконец, мы выводим базу данных из однопользовательского режима, что позволяет всем пользователям вернуться к работе с ней.

Я уже упоминал о том, что если вы захотите перенести базу данных на новое место, то можете воспользоваться настройкой WITH MOVE. Для этого нужно изменить маршрут восстановления на странице Files диалогового окна восстановления. Такое изменение окажет влияние только на первый этап процесса — на процедуру восстановления полной резервной копии, так как все последующие процедуры восстановления будут обращаться непосредственно к базе данных; при этом не нужно будет ни устанавливать, ни создавать файловую структуру. Познакомьтесь с фрагментом кода в листинге 2. Я выделил его, чтобы вам было удобнее анализировать этот код.

Позвольте мне разъяснить смысл настройки STATS = 5. Эта величина показывает, какой процент восстановления фиксируется на консоли SQL Server Management Studio в ходе выполнения процесса восстановления. Присваиваемое по умолчанию значение 5 означает, что вы будете получать уведомление всякий раз по завершении очередных 5% процедуры восстановления. На мой взгляд, это слишком часто. В зависимости от размеров базы данных я обычно устанавливаю это значение в пределах от 10 до 50.

Параметр FILE

Возможно, вы обратили внимание, что при выполнении каждого этапа восстановления параметр FILE приравнивается к единице. Это значение встречается чаще прочих. Представьте, что номер файла — это позиция нескольких резервных копий, отправленных в резервный файл. Один и тот же файл можно зарезервировать более одного раза, при этом не переписывая его. Каждая резервная копия, направляемая в один и тот же файл, располагается после предыдущей, и ее номер файла увеличивается на единицу. Поскольку в данном случае имеется только одна резервная копия на файл, номер файла выражается единицей.

Простые команды резервного копирования на языке Transact-SQL

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

Резервные копии — важная составляющая процесса восстановления, но если у вас нет возможности восстановить эти копии, они попросту бесполезны. Вам часто придется слышать совет: тестируйте свои резервные копии. С помощью опубликованных в данной статье фрагментов кода вы можете с легкостью делать это. Наверняка у вас нет никакого желания попадать «на ковер» к начальству и оправдываться, что вот-де я смог получить резервные копии, но они не восстанавливались по неизвестной причине. Тестируйте резервные копии. На это уйдет какое-то время, но оно не будет потрачено зря.

Листинг 1. Выполнение процесса восстановления

USE [master]
--Переводим базу данных в режим Single_User:
ALTER DATABASE [SQL_Cruise] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--На всякий случай формируем резервную копию хвоста журнала
BACKUP LOG [SQL_Cruise]
TO  DISK = N'C:\Data\MSSQL12. MSSQLSERVER\MSSQL\Backup\SQL_Cruise_LogBackup_2016-08-30_20-37-27.bak'
WITH NOFORMAT, NOINIT,  NAME = N'SQL_Cruise_LogBackup_2016-08-30_20-37-27', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 5
--Восстанавливаем первую полную резервную копию
RESTORE DATABASE [SQL_Cruise]
FROM  DISK = N'C:\temp\SQL_Cruise_FULL.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
--Восстанавливаем позднейшую разностную резервную копию из возможных, соответствующую нашей цели:
RESTORE DATABASE [SQL_Cruise]
FROM  DISK = N'C:\temp\SQL_Cruise_DIFF_3.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
--Восстанавливаем полные резервные копии журнала регистрации транзакций в порядке:
RESTORE LOG [SQL_Cruise]
FROM  DISK = N'C:\temp\SQL_Cruise_log_03.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [SQL_Cruise]
FROM  DISK = N'C:\temp\SQL_Cruise_log_04.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
--Восстанавливаем последнюю резервную копию журнала регистрации транзакций, включающую момент времени и восстановление:
RESTORE LOG [SQL_Cruise]
FROM  DISK = N'C:\temp\SQL_Cruise_log_05. trn'
WITH  FILE = 1,  RECOVERY, NOUNLOAD,  STATS = 5,
        STOPAT = N’2016-08-30T20:26:00’
--Выводим базу данных из режима single_user:
ALTER DATABASE [SQL_Cruise] SET MULTI_USER
GO

Листинг 2. Перемещение базы данных

RESTORE DATABASE [SQL_Cruise] FROM  DISK = N'C:\temp\SQL_Cruise_FULL.bak'
WITH  FILE = 1,
        MOVE N'SQL_Cruise'
                TO N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL_Cruise_NEW.mdf',
        MOVE N'SQL_Cruise_log'
                TO N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL_Cruise_NEW_log.ldf',
        NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
STATS

Листинг 3. Примеры команд восстановления

Резервирование остатка журнала
--Переводим базу данных в режим Single_User:
ALTER DATABASE [] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--На всякий случай выполняем резервную копию хвоста журнала:
BACKUP LOG []
TO  DISK = N''
WITH NOFORMAT, NOINIT,  NAME = N'', STATS = 
Восстанавливаем полную или разностную резервную копию в том же месте
(синтаксис тот же)
RESTORE DATABASE []
FROM  DISK = N''
WITH  FILE = ,  ,  ,  STATS = 
Восстанавливаем полную резервную копию на новом месте
RESTORE DATABASE []
FROM  DISK = N''
WITH  FILE = ,
        MOVE N''
                TO N'',
        MOVE N''
                TO N'',
        ,  ,  STATS = 
Восстанавливаем журнал регистрации транзакций
RESTORE LOG []
FROM  DISK = N''
WITH  FILE = ,  ,  ,  STATS = 
Восстановление журнала регистрации транзакций на момент времени
RESTORE LOG []
FROM  DISK = N''
WITH  FILE = ,  ,  ,  STATS = 
        STOPAT = N''

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

Вы восстанавливаете базу данных в SQL Backup Master, запустив Database Backup Recovery Explorer и щелкнув ссылку действия «Восстановить из резервной копии», связанную с конкретным файлом резервной копии базы данных. Появится окно восстановления базы данных.

 

ВАЖНАЯ ИНФОРМАЦИЯ. При восстановлении резервной копии базы данных из SQL Backup Master следует запускать SQL Backup Master из учетной записи Windows, которая является членом роли системного администратора в SQL Server.

 

Сводка резервного копирования

 

На панели сводки резервного копирования отображается следующее:

 

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

•Тип резервной копии — тип восстанавливаемой резервной копии базы данных.

• Экземпляр SQL Server — экземпляр SQL Server, на который будет восстановлена ​​резервная копия базы данных.

• Целевая база данных — имя целевой базы данных, в которую будет восстановлен файл резервной копии.

 

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

 

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

 

Состояние восстановления

 

Во время процесса восстановления базы данных SQL Backup Master может либо оставить базу данных в состоянии «готов к использованию» (например, работающая, полностью восстановленная), либо оставить ее в нерабочем состоянии. .

 

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

 

Расположение файлов восстановления

 

SQL Backup Master позволяет изменять расположение файлов резервных копий во время операции восстановления.

 

Расположение файлов восстановления по умолчанию будет указано после успешной загрузки файла резервной копии. После отображения пути восстановления можно редактировать, нажав соответствующую кнопку обзора или дважды щелкнув путь восстановления.

 

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

 

ВАЖНО! SQL Server поддерживает указание расположения файлов только для полных резервных копий. При восстановлении дифференциальных резервных копий или резервных копий журнала транзакций список файлов будет доступен только для чтения.

 

Начало восстановления

 

Когда вы будете готовы восстановить резервную копию базы данных, нажмите кнопку «Начать восстановление».

 

SQL Backup Master затем выполнит следующие шаги:

 

1. Файл резервной копии базы данных будет загружен из текущего места назначения резервной копии. Этот файл будет сохранен во временной папке резервного копирования базы данных, назначенной заданию резервного копирования.

2. Файл резервной копии будет распакован (и, возможно, расшифрован) с использованием параметров сжатия/шифрования, заданных в настоящее время для соответствующего задания резервного копирования.

3. Все существующие соединения с целевой базой данных будут разорваны.

4.База данных (или файл журнала) будет восстановлена.

5.Временные файлы базы данных будут очищены и т. д.

 

ВАЖНО: любые существующие данные в базе данных восстанавливаются в процессе резервного копирования. Эту операцию нельзя отменить, если вы не создадите резервную копию непосредственно перед восстановлением.

 

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

Полное руководство по резервному копированию и восстановлению SQL Server с помощью командной строки

Создание резервных копий базы данных SQL Server — один из наиболее важных аспектов обслуживания системы. Существуют различные инструменты для создания резервных копий, такие как SQL Server Management Studio, SqlBak и SQLBackupAndFTP. Однако выполнение резервного копирования через интерфейс командной строки может обеспечить еще большую гибкость и возможности настройки.

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

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

SQL для резервного копирования базы данных

Чтобы выполнить резервное копирование SQL Server с помощью T-SQL, вы можете использовать оператор BACKUP DATABASE . Этот оператор создает полную резервную копию указанной базы данных, которую можно использовать для восстановления базы данных до ее состояния на момент создания резервной копии.

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

 BACKUP DATABASE [имя_базы_данных]
НА ДИСК = 'C:\Backup\backup_file.bak'
С ИНИТ
  • [имя_базы_данных] — Имя базы данных для резервного копирования.
  • TO DISK = 'C:\Backup\backup_file.bak' — Путь и имя создаваемого файла резервной копии. Это может быть любой допустимый путь к файлу и имя файла.
  • WITH INIT — этот параметр перезаписывает любой существующий файл резервной копии с тем же именем, что и создаваемый.

Вы можете изменить параметры в предложении WITH в соответствии со своими конкретными потребностями в резервном копировании. Например, вы можете включить сжатие, чтобы уменьшить размер файла резервной копии и отобразить информацию о ходе резервного копирования, используя параметры СЖАТИЕ и СТАТИСТИКА соответственно.

Обратите внимание, что для выполнения резервного копирования с помощью T-SQL пользователь должен иметь достаточные права для выполнения инструкции BACKUP DATABASE и доступа к указанному пути к файлу резервной копии.

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

Чтобы восстановить базу данных SQL Server из файла резервной копии, вы можете использовать оператор RESTORE DATABASE в T-SQL. Основной синтаксис для восстановления базы данных из файла резервной копии выглядит следующим образом:

 RESTORE DATABASE [имя_базы_данных]
С ДИСКА = 'C:\Backup\backup_file.bak'
С ВОССТАНОВЛЕНИЕМ
  • [database_name] — Имя восстанавливаемой базы данных.
  • С ДИСКА = 'C:\Backup\backup_file.bak' — Путь и имя файла резервной копии для восстановления.
  • WITH RECOVERY — этот параметр указывает, что базу данных следует оставить в восстановленном состоянии, чтобы сделать ее доступной для использования.

Обратите внимание, что для восстановления базы данных с помощью T-SQL пользователь должен иметь достаточные права для выполнения инструкции RESTORE DATABASE и доступа к указанному пути к файлу резервной копии.

Восстановление базы данных с другим именем

Восстановление базы данных с другим именем может быть полезно, когда вам нужно создать копию существующей базы данных или восстановить базу данных в другом экземпляре SQL Server. В этом разделе мы рассмотрим, как восстановить базу данных SQL Server с другим именем с помощью T-SQL.

Основной синтаксис для восстановления базы данных с другим именем выглядит следующим образом:

 ВОССТАНОВЛЕНИЕ БАЗЫ ДАННЫХ [имя_новой_базы_данных]
С ДИСКА = 'C:\Backup\backup_file. bak'
WITH MOVE '[original_logical_data_file_name]' TO '[new_physical_data_file_path]',
     MOVE '[original_logical_log_file_name]' TO '[new_physical_log_file_path]'
  • [new_database_name] — Новое имя восстанавливаемой базы данных.
  • С ДИСКА = 'C:\Backup\backup_file.bak' — Путь и имя файла резервной копии для восстановления.
  • С ПЕРЕМЕЩЕНИЕМ — этот параметр сопоставляет имена логических файлов в файле резервной копии с новыми именами физических файлов и путями для файлов базы данных.

При восстановлении базы данных с другим именем необходимо использовать параметр WITH MOVE , чтобы указать новые имена физических файлов и пути для файлов базы данных. Этот параметр сопоставляет имена логических файлов в файле резервной копии с новыми именами и путями физических файлов. MOVE  требуются два параметра: исходное логическое имя файла и новый физический путь к файлу.

Для определения логических имен файлов данных и файлов журнала в файле резервной копии можно использовать следующую инструкцию T-SQL:

 RESTORE FILELISTONLY FROM DISK = 'C:\Backup\backup_file.bak'

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

Например, чтобы восстановить базу данных с именем «AdventureWorks» как «AdventureWorksCopy» с файлом данных, перемещенным в «C:\Data\AdventureWorksCopy.mdf», а файл журнала — в «C:\Log\AdventureWorksCopy.ldf», вы должны использовать следующий оператор T-SQL:

 ВОССТАНОВЛЕНИЕ БАЗЫ ДАННЫХ AdventureWorksCopy
С ДИСКА = 'C:\Backup\AdventureWorks.bak'
С ПЕРЕМЕЩЕНИЕМ 'AdventureWorks_Data' В 'C:\Data\AdventureWorksCopy.mdf',
     ПЕРЕМЕСТИТЕ 'AdventureWorks_Log' В 'C:\Log\AdventureWorksCopy. ldf'

Командная строка резервного копирования SQL Server

В этом разделе описывается выполнение резервного копирования SQL Server с помощью интерфейса командной строки. Он включает примеры сценариев резервного копирования и утилит, которые могут помочь автоматизировать процесс резервного копирования.

В этой статье все подключения к базе данных выполняются с использованием проверки подлинности Windows. Если вы используете проверку подлинности SQL Server, вам потребуется указать параметры -U и -P при использовании sqlcmd. При использовании модуля PowerShell Backup-SqlDatabase для проверки подлинности SQL Server данные подключения передаются через параметр -SqlCredential. Обязательно обратите внимание, чтобы избежать ошибок при выполнении команд.

sqlcmd Резервное копирование базы данных на локальный диск

sqlcmd — это утилита командной строки, предоставляемая Microsoft для взаимодействия с SQL Server. Он позволяет выполнять операторы SQL, хранимые процедуры и файлы сценариев, а также выполнять различные административные задачи.

Чтобы выполнить резервное копирование SQL Server с помощью sqlcmd , можно использовать параметр -Q для указания выполняемой команды резервного копирования. Основной синтаксис для резервного копирования базы данных с использованием sqlcmd выглядит следующим образом:

 sqlcmd -S [имя_сервера] -Q "РЕЗЕРВНАЯ БАЗА ДАННЫХ [имя_базы_данных] НА ДИСК='C:\Backup\backup_file.bak' С INIT"
  • -S [имя_сервера] — имя экземпляра SQL Server для подключения.
  • -Q — указывает выполняемый запрос.
  • "BACKUP DATABASE [database_name] TO DISK='C:\Backup\backup_file. bak' WITH INIT" — Команда резервного копирования, которую необходимо выполнить.

sqlcmd восстановление базы данных с локального диска

Чтобы восстановить базу данных SQL Server с помощью sqlcmd , можно использовать параметр -Q , чтобы указать команду восстановления, которая должна быть выполнена. Основной синтаксис для восстановления базы данных с помощью sqlcmd выглядит следующим образом:

 sqlcmd -S [server_name] -Q "ВОССТАНОВЛЕНИЕ DATABASE [database_name] FROM DISK='C:\Backup\backup_file.bak' WITH RECOVERY
    90 117
    -S [имя_сервера] — имя экземпляра SQL Server для подключения.
  • -Q — указывает выполняемый запрос.
  • RESTORE DATABASE [имя_базы_данных] FROM DISK='C:\Backup\backup_file. bak' WITH RECOVERY — Команда восстановления, которую необходимо выполнить.

PowerShell Резервное копирование всех баз данных SQL

PowerShell — это оболочка командной строки и язык сценариев, разработанный Microsoft. Он включает в себя несколько модулей для взаимодействия с SQL Server, что делает его мощным инструментом для автоматизации задач SQL Server, включая резервное копирование. Одним из таких модулей является модуль SQLServer, предоставляющий командлеты для операций резервного копирования и восстановления.

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

Install-Module -Name SqlServer

После установки модуля вы можете использовать командлет Backup-SqlDatabase для выполнения резервного копирования. Командлет позволяет указать базу данных для резервного копирования, путь к файлу резервной копии и различные параметры резервного копирования.

Для резервного копирования всех баз данных на экземпляре SQL Server можно использовать следующую команду:

 Get-ChildItem "SQLSERVER:\SQL\[имя-сервера]\[имя-экземпляра]\базы данных" | Backup-SqlDatabase -BackupContainer "[backup-path]"

Замените [server-name] , [instance-name] и [backup-path] соответствующими значениями для вашей среды. Командлет Get-ChildItem извлекает все базы данных в указанном экземпляре SQL Server и передает их командлету Backup-SqlDatabase для резервного копирования.

Вот пример резервного копирования всех баз данных на экземпляре SQL Server:

 Get-ChildItem "SQLSERVER:\SQL\MSI\DEFAULT\Databases" | Backup-SqlDatabase -BackupContainer "c:\Backups2\"

Эта команда выполняет резервное копирование всех баз данных на экземпляре SQL Server «MSI» в папку «c:\Backups2».

Дифференциальное резервное копирование PowerShell SQL Server

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

Чтобы выполнить разностное резервное копирование с помощью PowerShell, используйте командлет Backup-SqlDatabase с параметром -Incremental . Вот пример:

 Backup-SqlDatabase -ServerInstance "." -База данных "AdventureWorks" -BackupFile "C:\Backups\AdventureWorks_diff.bak" -Incremental

Эта команда создает разностную резервную копию базы данных “AdventureWorks” и сохраняет ее в файл “C:\Backups\AdventureWorks_diff. bak”.

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

Помимо создания резервных копий, PowerShell также можно использовать для восстановления баз данных SQL Server. Модуль SQLServer предоставляет командлет Restore-SqlDatabase для восстановления баз данных из файлов резервных копий.

Основной синтаксис для восстановления базы данных с помощью PowerShell выглядит следующим образом:

 Restore-SqlDatabase -ServerInstance [экземпляр-сервера] -Database [имя-базы-данных] -BackupFile [файл-резервной копии] -ReplaceDatabase
  • -ServerInstance [сервер-экземпляр] — имя экземпляра SQL Server для подключения.
  • -Database [имя-базы-данных] — Имя восстанавливаемой базы данных.
  • -BackupFile [backup-file] — Путь и имя файла резервной копии для восстановления.
  • -ReplaceDatabase — этот параметр указывает, что существующая база данных должна быть заменена восстановленной базой данных.

Вот пример восстановления базы данных с помощью PowerShell:

 Restore-SqlDatabase-ServerInstance '.' -База данных «MyDatabase» -BackupFile «C:\Backup\MyDatabase.bak» -ReplaceDatabase

Эта команда восстанавливает базу данных «MyDatabase» из файла резервной копии, расположенного в «C:\Backup\MyDatabase.bak», на локальный сервер SQL Server. instance и заменяет существующую базу данных, если она существует.

PowerShell SQL Server Restore Differential Backup

Чтобы восстановить базу данных SQL Server из дифференциальной резервной копии с помощью PowerShell, сначала необходимо восстановить полную резервную копию с помощью -NoRecovery , затем восстановите разностную резервную копию.

Чтобы восстановить полную резервную копию, используйте командлет Restore-SqlDatabase с параметрами -NoRecovery и -ReplaceDatabase . Вот пример:

 Restore-SqlDatabase -ServerInstance '.' -Database 'AdventureWorks' -BackupFile 'C:\Backups\AdventureWorks_full.bak' -NoRecovery -ReplaceDatabase

Эта команда восстанавливает полную резервную копию базы данных “AdventureWorks” из файла резервной копии “C:\Backups\AdventureWorks_full.bak” в локальный экземпляр SQL Server и заменяет существующую базу данных, если она существует.

После восстановления полной резервной копии можно восстановить дифференциальную резервную копию с помощью того же командлета Restore-SqlDatabase без параметра -NoRecovery . Вот пример:

 Restore-SqlDatabase -ServerInstance '.' -Database 'AdventureWorks' -BackupFile 'C:\Backups\AdventureWorks_diff.bak'

Эта команда восстанавливает разностную резервную копию базы данных “AdventureWorks” из файла резервной копии “C:\Backups\AdventureWorks_diff.bak” на локальный сервер SQL Server. пример.

Сценарии резервного копирования

Хотя однострочные команды могут быть полезны, сценарии резервного копирования предоставляют больше возможностей и могут автоматизировать процесс резервного копирования. Сценарии резервного копирования могут быть написаны на разных языках, таких как пакетная обработка, PowerShell и Bash, в зависимости от ваших потребностей и среды.

Пакетный сценарий для резервного копирования базы данных SQL Server

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

 

 @эхо выключено

установить DB_NAME=MyDatabaseName
установить BACKUP_DIR=C:\Backups
установить DAYS_TO_KEEP=0

echo Запуск резервного копирования базы данных %DB_NAME%...

sqlcmd -E -S . -Q "РЕЗЕРВНАЯ БАЗА ДАННЫХ %DB_NAME% НА ДИСК='%BACKUP_DIR%\%DB_NAME%_%date:/=-%_%time::=-%.bak' С INIT, СЖАТИЕ"

если %ERRORLEVEL% neq 0 (
    echo Сбой резервного копирования с кодом ошибки %ERRORLEVEL%.
    eventcreate /T ERROR /L APPLICATION /ID 100 /D "Сбой резервного копирования SQL Server с кодом ошибки %ERRORLEVEL%."
    перейти к концу
)

echo Удаление старых файлов резервных копий старше %DAYS_TO_KEEP% дней в каталоге %BACKUP_DIR% ...

forfiles /P "%BACKUP_DIR%" /M "%DB_NAME%*.bak" /D -%DAYS_TO_KEEP% /C 2>nul "cmd /c if @ISDIR==FALSE del @PATH"

echo Старые файлы резервных копий успешно удалены.

:конец
эхо Сценарий завершен.

Чтобы использовать этот сценарий, замените значение переменной DB_NAME на имя базы данных SQL Server, для которой требуется создать резервную копию, и замените значение переменной BACKUP_DIR на соответствующий путь для хранения файлов резервных копий. Вы также можете изменить значение переменной DAYS_TO_KEEP , чтобы изменить количество дней хранения файлов резервных копий.

Сценарий PowerShell для резервного копирования всех баз данных SQL

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

  • Получает список всех баз данных SQL Server
  • Подключает сетевой диск
  • Создает резервную копию каждой базы данных
  • Сжимает файл резервной копии
  • Копирует файл резервной копии на сетевой диск
  • Удаляет просроченные резервные копии
  • Размонтирует сетевой диск
  • Отправляет уведомление об ошибке, если произошла ошибка
 вызов-sqlcmd

# Установить переменные
$backupFolderPath = "C:\temp" # Путь к папке резервного копирования
$compressionLevel = "2" # Уровень сжатия архива (1-9)
$expirationDays = 7 # Количество дней, по истечении которых резервные копии будут удалены

# Установить учетные данные для сетевого диска
$networkDrive = "Z" # Временный сетевой диск
$networkPath = "\\сервер\резервная копия\папка"
$networkUser = "сетевой пользователь"
$networkPassword = "сетевой пароль"

$eventLogName = "Приложение"
$eventSource = "MSSQLSERVER"

Write-Host «Запуск сценария резервного копирования базы данных SQL Server»

# Получить список всех баз данных SQL Server
$databases = Get-ChildItem "SQLSERVER:\SQL\MSI\DEFAULT\Databases" | Где-Объект {$_. Name -ne "tempdb"}

Write-Host "Найдены базы данных $($databases.Count)"

# Создать резервную папку, если она не существует
если (!(Тест-Путь -Путь $backupFolderPath)) {
    New-Item -ItemType Directory -Path $backupFolderPath | Out-Null
    Write-Host "Создана папка резервного копирования: $backupFolderPath"
}

New-PSDrive -Name $networkDrive -PSProvider FileSystem -Root $networkPath -Credential (New-Object System.Management.Automation.PSCredential($networkUser,(ConvertTo-SecureString $networkPassword -AsPlainText -Force)))
# Подключить сетевой диск

# Резервное копирование баз данных
foreach (база данных $ в базе данных $) {
    Write-Host "Создание резервной копии для базы данных $($database.Name)"
    $backupFile = Join-Path $backupFolderPath "$($database.Name)_$(Get-Date -Format 'yyyyMMdd_HHmmss').bak"
    Backup-SqlDatabase -ServerInstance "." -База данных $database.Name -BackupFile $backupFile -Инициализировать

    если ($?) {
        Write-Host «Резервная копия базы данных $($database. Name) создана».
    } еще {
        $errorDetails = "Ошибка создания резервной копии базы данных $($database.Name). Подробности: " + $Error[0].Exception.Message
        Write-EventLog -LogName $eventLogName -Source $eventSource -EntryType Error -EventId 1 -Message $errorDetails
        продолжать
    }

    Write-Host "Создана резервная копия для базы данных $($database.Name)"

    # Сжать резервную копию
    $zipFile = "$backupFile.zip"
    Compress-Archive -Path $backupFile -CompressionLevel Optimal -DestinationPath $zipFile

    если ($?) {
        Write-Host «Резервная копия для базы данных $($database.Name) была заархивирована».
    } еще {
        $errorDetails = "Ошибка создания резервной копии базы данных $($database.Name). Подробности: " + $Error[0].Exception.Message
        Write-EventLog -LogName $eventLogName -Source $eventSource -EntryType Error -EventId 1 -Message $errorDetails
        продолжать
    }

    Write-Host "Сжатая резервная копия $($backupFile)"

    # # Скопировать файл на сетевой диск
    cp $zipFile "$($networkDrive):\$(Split-Path $zipFile -leaf)"

    если ($?) {
        Write-Host «Резервная копия базы данных $($database. Name) создана и успешно отправлена ​​в хранилище».
    } еще {
        $errorDetails = "Ошибка создания резервной копии базы данных $($database.Name). Подробности: " + $Error[0].Exception.Message
        Write-EventLog -LogName $eventLogName -Source $eventSource -EntryType Error -EventId 1 -Message $errorDetails
        продолжать
    }

    # Удалить просроченные резервные копии
    Get-ChildItem $backupFolderPath -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-$expirationDays)) -and ($_.Extension -eq ".zip")} | Убрать предмет

    # Удалить просроченные резервные копии из хранилища резервных копий
    Get-ChildItem "$($networkDrive):\" -Recurse | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-$expirationDays)) -and ($_.Extension -eq ".zip")} | Убрать предмет

    Write-Host "Удаленные резервные копии с истекшим сроком действия"

    # Отправить уведомление об ошибке, если произошла ошибка
}

Write-Host «Сценарий резервного копирования базы данных SQL Server завершен»

# # Размонтировать сетевой диск
Remove-PSDrive-Name $networkDrive

Инструмент резервного копирования командной строки SQL Server

Вместо самостоятельного создания сложного сценария PowerShell или Bash можно использовать утилиту sqlbak-cli .

SqlBak-CLI — это простой инструмент командной строки для создания резервных копий базы данных и отправки их в хранилище. Эта утилита также может восстанавливать резервные копии, которые она создает. Он может отправлять резервные копии в сетевые папки, FTP, SFTP и Backblaze. Документацию и инструкции по загрузке можно найти по этой ссылке.

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

Резервное копирование всех баз данных на другой сервер через SSH с использованием SqlBak-CLI

Создайте файл с именем backup-job-settings.json с подробностями подключения для базы данных и сетевой папки.

 {
    "источник":
        {
            "тип": "mssql",
            "имя_источника":"имя-источника",
            "источник данных":".",
            «trust_server_certificate»: правда,
            «is_integrated_security»: ложь,
            "имя_пользователя":"sa",
            "пользовательский пароль":"*******",
            "базы данных": [ "mssql_custom" ]
        },
       "хранилища": [
          {
             "тип": "sftp",
             "server_name":"backup-storage. com",
             "user_name":"ftp-пользователь",
             "пользовательский пароль":"*************",
             "путь":"резервные копии"
          }
       ]
}

Чтобы запустить резервное копирование, выполните следующую команду:

 sqlbak-cli run-backup --job-settings=backup-settings.json

Восстановите базу данных на другом сервере через SSH с помощью SqlBak-CLI

Чтобы восстановить backup создайте файл с именем restore-job-settings.json.

 {
    "тип": "восстановить",
    "цель": {
    "тип": "mssql",
    "источник данных":".",
    «trust_server_certificate»: правда,
    "имя_пользователя":"sa",
    "пользовательский пароль":"********",
    "базы данных": [
            {
                "имя_источника": "Вордпресс",
                "target_name": "Вордпресс"
            }
        ],
    },
    "хранилище": {
        «тип_назначения»: «sftp»,
        "server_name":"backup-storage. com",
        "user_name":"ftp-пользователь",
        "пользовательский пароль":"*************",
        "путь":"резервные копии"
    }
}

Чтобы запустить восстановление, используйте следующую команду:

 sqlbak-cli run-restore --job-settings=restore-settings.json

Автоматизация резервного копирования с помощью SqlBak-CLI, PowerShell и пакетных сценариев

После того, как вы ваши сценарии резервного копирования настроены с помощью SqlBak-CLI, PowerShell или пакетной службы, вы можете автоматизировать их выполнение с помощью планировщика заданий в Windows. Это особенно полезно, если вы хотите запускать резервное копирование по регулярному расписанию, не забывая делать это вручную.

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

  1. Откройте планировщик заданий, введя «Планировщик заданий» в меню «Пуск» и щелкнув соответствующий результат.
  2. Нажмите «Создать простую задачу» на панели «Действия» в правой части окна планировщика заданий.
  3. Назовите задачу и нажмите «Далее».
  4. Выберите периодичность резервного копирования и нажмите «Далее». Например, если вы хотите создавать резервную копию базы данных каждый день в полночь, выберите «Ежедневно» и установите время на 00:00.
  5. Выберите «Запустить программу» в качестве действия для задачи и нажмите «Далее».
  6. Введите путь к вашему скрипту резервного копирования в поле «Программа/скрипт». Для SqlBak-CLI это может быть «sqlbak-cli.exe». Для PowerShell это может быть «powershell.exe». Для пакетной обработки это может быть «cmd.exe».
  7. Для SqlBak-CLI:
    В поле «Программа/скрипт» введите путь к файлу sqlbak-cli.exe. Например: C:\Program Files\SQLBak-CLI\sqlbak-cli.exe .
    В поле «Добавить аргументы (необязательно)» введите run-backup и путь к JSON-файлу задания резервного копирования: run-backup --job-settings=C:\backup\my-backup-job. json .
  8. Для PowerShell:
    В поле «Программа/скрипт» введите путь к файлу powershell.exe. Например: powershell.exe .
    В поле «Добавить аргументы (необязательно)» введите путь к файлу сценария PowerShell, например: -File C:\Users\UserName\Documents\backup.ps1 .
  9. Для пакетной обработки:
    В поле «Программа/скрипт» введите путь к файлу cmd.exe. Например: cmd.exe .
    В поле «Добавить аргументы (необязательно)» введите путь к вашему командному файлу, например: C:\Users\UserName\Documents\backup.bat .
  10. Нажмите «Далее», просмотрите сведения о задаче и нажмите «Готово».

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

Заключение

Резервное копирование базы данных — не единственная задача, которую должен выполнять администратор БД.