Как открыть бэкап базы sql
PROИТ
Office 365, AD, Active Directory, Sharepoint, C#, Powershell. Технические статьи и заметки.
SQL Server Как восстановить базу данных из bak файла бэкапа (restore DB from backup)
Задача: восстановить базу данных из файла бэкапа.
Шаг за шагом с иллюстрациями рассмотрим как это сделать на примере СУБД Microsoft SQL Server 2014. Кроме восстановления посмотрим также как сделать линковку (связь) пользователя базы данных и логина СУБД (будет использовать SQL скрипт sp_change_users_login).
Итак, залогиньтесь в СУБД SQL Server и в дереве в контекстном меню пункта Databases выберите пункт «Restore Database. «:
В параметрах выберем «Device» и перейдем по кнопке «Обзор» (кнопка с многоточием):
В открывшемся окне нажимаем «Add» и находим файл бэкапа базы данных:
Перед началом рекомендуется проверить корректность файла, нажав кнопку «Verify Backup Media«:
Далее перейдем в левом меню на раздел «Files» и проверим пути для сохранения восстановленной базы данных. При необходимости меняем их (не обязательно):
Также можно перейти в раздел «Options» и сделать настройки, если требуется (не обязательно):
Нажимаем ОК и дожидаемся сообщения об успешном восстановлении базы:
В открывшемся окне свойств пользователя мы видим, что пользователь существует только в контексте это базы (т.е. не связан ни с каким логином «SQL user without login»):
Это не позволит Вам подключиться к базе данных под данным пользователем. Поэтому создадим логин для этого пользователя (если логин уже существует, пропустите этот шаг). В дереве объектов СУБД перейдем на уровень выше (выше, чем текущая БД) в пункт «Security» и в контекстном меню пункта «Logins» выберем «New Login. «:
Далее можно перейти в левом меню в раздел «User Mapping«, где увидим, что можно закрепить за созданным логином некого пользователя в базе. Если у Вас база данных не содержит никаких пользователей, то задайте эти параметры, как показано на рисунке (в противном случае пропустите этот шаг) и будет автоматически создан пользователь БД связанный с данным логином:
При этом логин будет создан, но не связан с пользователем.
Чтобы связать логин СУБД с пользователем конкретной базы данных выполним следующий SQL-скрипт:
Теперь, если снова перейти в свойства пользователя БД, то мы видим, что пользователь связан с только что созданным логином.
Краткое руководство. Локальное резервное копирование и восстановление баз данных SQL Server
В этом кратком руководстве нам предстоит создать новую базу данных, выполнить простое резервное копирование и затем восстановить ее.
Предварительные требования
Для работы с этим кратким руководством вам потребуется следующее:
Создание тестовой базы данных
Создание резервной копии
Чтобы создать резервную копию базы данных, сделайте следующее.
Кроме того, можно выполнить следующую команду Transact-SQL для резервного копирования базы данных:
Восстановление резервной копии
Для восстановления базы данных выполните следующие действия.
Запустите среду SQL Server Management Studio (SSMS) и подключитесь к своему экземпляру SQL Server.
Выберите Устройство: и нажмите кнопку с многоточием (. ), чтобы найти файл резервной копии.
Снова нажмите ОК в диалоговом окне Выбор устройств резервного копирования, чтобы закрыть его.
Чтобы восстановить резервную копию базы данных, нажмите ОК.
Кроме того, можно выполнить следующий скрипт Transact-SQL для восстановления базы данных:
Очистка ресурсов
Выполните следующую команду Transact-SQL, чтобы удалить базу данных, которую вы создали, а также свой журнал резервного копирования в базе данных MSDB:
Просмотр содержимого ленты или файла резервной копии (SQL Server)
В этом разделе описывается просмотр содержимого ленты или файла резервной копии в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.
Поддержка резервного копирования на ленточные устройства будет исключена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
В этом разделе
Перед началом работы
Просмотр содержимого ленты или файла резервной копии с помощью следующих средств
Перед началом
безопасность
Сведения о безопасности см. в статье RESTORE HEADERONLY (Transact-SQL).
Permissions
В SQL Server 2008 и более поздних версиях для получения сведений о резервном наборе данных или устройстве резервного копирования необходимо разрешение CREATE DATABASE. Дополнительные сведения см. в разделе GRANT, предоставление разрешений для базы данных (Transact-SQL).
Использование среды SQL Server Management Studio
Просмотр содержимого ленты или файла резервной копии
После подключения к соответствующему экземпляру Microsoft Компонент SQL Server Database Engine в обозревателе объектов разверните дерево сервера, щелкнув его имя.
Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
В области Назначение страницы Общие выберите Диск или Лента. В списке Создать резервную копию на выберите нужный файл на диске или ленту.
На правой панели выводятся сведения о наборе носителей и резервных наборах данных на выбранной ленте или в файле.
Использование Transact-SQL
Просмотр содержимого ленты или файла резервной копии
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Резервное копирование и восстановление баз данных SQL Server
В этой статье описаны преимущества резервного копирования баз данных SQL Server, основные условия резервного копирования и восстановления, а также приведены стратегии резервного копирования и восстановления для SQL Server и рассмотрены вопросы безопасности, связанные с резервным копированием и восстановлением в SQL Server.
В этой статье приводятся общие сведения о резервном копировании SQL Server. Конкретные действия по резервному копированию баз данных SQL Server см. в разделе Создание резервных копий.
В дополнение к локальному хранилищу для хранения резервных копий SQL Server поддерживает резервное копирование и восстановление из службы хранилища BLOB-объектов Azure. Дополнительные сведения см. в разделе Резервное копирование и восстановление SQL Server с помощью службы хранилища BLOB-объектов Microsoft Azure. Для файлов базы данных, сохраненных в службе хранилища больших двоичных объектов Microsoft Azure, SQL Server 2016 (13.x); предоставляет возможность использовать моментальные снимки Azure для практически мгновенного создания резервных копий и ускорения восстановления. Дополнительные сведения см. в разделе Резервные копии моментальных снимков файлов для файлов базы данных в Azure. Azure также предоставляет возможности резервного копирования корпоративного класса для SQL Server на виртуальных машинах Azure. Это полностью управляемое решение резервного копирования поддерживает группы доступности Always On, долгосрочное хранение, восстановление на определенный момент времени, а также централизованное управление и мониторинг. Дополнительную информацию см. в статье Azure Backup для SQL Server на виртуальных машинах Azure.
Зачем выполнять резервное копирование
При правильном создании резервных копий баз данных можно будет восстановить данные после многих видов сбоев, включая следующие:
Кроме того, резервные копии баз данных полезны и при выполнении повседневных административных задач, например для копирования баз данных с одного сервера на другой, настройки Группы доступности AlwaysOn или зеркального отображения баз данных и архивирования.
Глоссарий терминов, связанных с резервным копированием
создание резервных копий
Процесс создания резервной копии путем копирования записей данных из базы данных SQL Server или записей журнала из ее журнала транзакций.
резервная копия
Копия данных, которая может использоваться для восстановления данных в случае возникновения ошибки. Резервные копии баз данных также могут использоваться для восстановления копии базы данных в новом расположении.
устройство резервного копирования
Диск или ленточное устройство, на которые записываются резервные копии SQL Server для последующего восстановления. Резервные копии SQL Server можно также записать в службу хранилища BLOB-объектов Azure, а формат URL-адреса используется, чтобы указать назначение и имя файла резервной копии. Дополнительные сведения см. в разделе Резервное копирование и восстановление SQL Server с помощью службы хранилища BLOB-объектов Microsoft Azure.
носитель данных резервной копии
Одна магнитная лента или несколько или один файл на диске или несколько, в которые были записаны одна резервная копия или несколько.
резервное копирование данных
Резервная копия данных всей базы данных (резервная копия базы данных), части базы данных (частичная резервная копия) или набора файлов данных или файловых групп (резервная копия файлов).
резервное копирование базы данных
Резервная копия базы данных. Полные резервные копии базы данных отображают состояние всей базы данных на момент завершения резервного копирования. Разностные резервные копии базы данных содержат только изменения базы данных с момента последнего полного резервного копирования.
разностная резервная копия
Резервная копия данных, основанная на последней полной или частичной резервной копии базы данных или набора файлов данных или файловых групп (базовой копии для разностного копирования), которая содержит только данные, измененные по сравнению с базовой копией для разностного копирования.
полная резервная копия
Резервная копия, которая содержит все данные заданной базы данных или наборов файлов или файловых групп, а также журналов для обеспечения возможности последующего восстановления этих данных.
резервная копия журналов
Резервная копия журналов транзакций, включающая все записи журнала, не входившие в предыдущую резервную копию журналов. (модель полного восстановления)
recover
Для возврата базы данных в стабильное и согласованное состояние.
recovery
Фаза запуска или восстановления базы данных, которая приводит базу данных в состояние согласованности транзакций.
модель восстановления
Свойство базы данных, с помощью которого выполняется управление обслуживанием журналов транзакций в базе данных. Существует три модели восстановления: простая модель восстановления, модель полного восстановления и модель восстановления с неполным протоколированием. Модель восстановления базы данных определяет требования к резервному копированию и восстановлению.
восстановление
Многоэтапный процесс, в ходе которого все данные и страницы журнала копируются из указанной резервной копии SQL Server в определенную базу данных, а затем выполняется накат всех фиксированных транзакций, записанных в резервной копии журнала, путем внесения новых данных на основе зарегистрированных изменений.
Стратегии резервного копирования и восстановления
Операции резервирования и восстановления данных следует адаптировать под конкретную среду с учетом доступных ресурсов. Таким образом, для надежного использования резервного копирования и восстановления требуется стратегия резервного копирования и восстановления. Хорошо спроектированная стратегия резервного копирования и восстановления позволяет сбалансировать бизнес-требования для обеспечения максимальной доступности данных и минимальной потери данных, учитывая стоимость обслуживания и хранения резервных копий.
Стратегия резервирования и восстановления состоит из части, относящейся к резервированию, и части, относящейся к восстановлению. Часть, относящаяся к резервированию, определяет тип и частоту создания резервных копий, тип и скоростные характеристики оборудования, необходимого для их создания, способ проверки резервных копий, а также местонахождение и тип носителя резервных копий (включая и вопросы безопасности). Часть, относящаяся к восстановлению, определяет ответственного за проведение операций восстановления, методы их проведения, позволяющие удовлетворить требования пользователей по доступности базы данных и минимизации потерь данных, а также методы проверки восстановления.
Разработка эффективной стратегии резервирования и восстановления требует тщательного планирования, реализации и тестирования. Проверка необходима — у вас нет стратегии резервного копирования до тех пор, пока не были успешно восстановлены все резервные копии во всех сочетаниях, включенных в стратегию восстановления, и не была проверена физическая согласованность восстановленной базы данных. Необходимо оценить ряд факторов. К ним относятся следующие объекты.
Задачи организации, относящиеся к рабочей базе данных, особенно требования к доступности данных и их защите от потери или повреждения.
Свойства каждой базы данных: размер, типичное использование, характер содержимого, требования к данным и т. д.
Ограничения на ресурсы, например: оборудование, персонал, пространство для хранения носителей резервных копий, физическая безопасность этих носителей и так далее.
Рекомендации
Использование отдельного хранилища
Убедитесь, что резервные копии базы данных размещаются в отдельном от файлов базы данных физическом хранилище или устройстве. Если физический диск, на котором хранятся базы данных, неисправен или завершается сбоем, восстановление зависит от возможности доступа к отдельному диску или удаленному устройству, в котором хранятся резервные копии. Помните, что можно создать несколько логических томов или разделов одного и того же физического диска. Прежде чем выбирать место хранения резервных копий, внимательно изучите разделы диска и структуры логических томов.
Выбор подходящей модели восстановления
Операции резервного копирования и восстановления выполняются в контексте модели восстановления. Модель восстановления является свойством базы данных, которое задает, как выполняется управление журналом транзакций. Таким образом, модель восстановления базы данных определяет, какие типы резервных копий и сценарии восстановления поддерживаются для базы данных и каким будет размер резервных копий журнала транзакций. Обычно база данных использует простую модель восстановления или модель полного восстановления. Модель полного восстановления может быть дополнена путем переключения на модель с неполным протоколированием перед выполнением массовых операций. Основные сведения о моделях восстановления и их влиянии на управление журналом транзакций см. в разделе Журнал транзакций (SQL Server).
Лучший выбор модели восстановления базы данных зависит от бизнес-требований. Чтобы избежать управления журналом транзакций и упростить резервное копирование и восстановление, используйте простую модель восстановления. Чтобы снизить вероятность потери результатов работы ценой увеличения административных издержек, используйте модель полного восстановления. Чтобы уменьшить влияние на размер журнала во время операций с неполным протоколированием и при этом обеспечить возможность восстановления этих операций, используйте модель восстановления с неполным протоколированием. Дополнительные сведения о влиянии моделей восстановления на создание резервных копий и восстановление см. в разделе Общие сведения о резервном копировании (SQL Server).
Создание стратегии резервного копирования
После того как выбрана модель восстановления, соответствующая бизнес-требованиям определенной базы данных, необходимо спланировать и выполнить соответствующую стратегию резервного копирования. Оптимальная стратегия зависит от многих факторов, среди которых наиболее важны следующие.
Сколько часов в день приложения имеют доступ к базе данных?
Если существует прогнозируемый внепиковый период, рекомендуется запланировать полное резервное копирование базы данных именно на этот период.
Насколько часты и вероятны изменения и обновления?
Если изменения часты, учтите следующее.
В рамках простой модели восстановления рассмотрите возможность запланировать разностное резервное копирование между полными резервными копированиями базы данных. Разностная резервная копия сохраняет только те изменения, которые были внесены с момента последнего полного резервного копирования.
В рамках модели полного восстановления следует запланировать частное резервное копирование журналов. Планирование разностного резервного копирования между полными резервными копиями сокращает время восстановления путем сокращения количества резервных копий журналов, которые необходимо восстанавливать после восстановления данных.
Касаются ли обычно изменения небольшой или же значительной части базы данных?
В большой базе данных, в которой изменения концентрируются в части файлов или файловых групп, полезно частичное резервное копирование или резервное копирование файлов. Дополнительные сведения см. в разделах Частичные резервные копии (SQL Server) и Полные резервные копии файлов (SQL Server).
Сколько места на диске требуется для полного резервного копирования базы данных?
За какой прошлый период компании нужны резервные копии?
Убедитесь, что установлен правильный график резервного копирования в соответствии с потребностями приложения и бизнес-требованиями. По мере устаревания резервных копий риск потери данных выше, если нет способа повторного создания всех данных до точки сбоя. Прежде чем удалять старые резервные копии из-за ограничений ресурсов хранилища, следует учесть, требуется ли возможность восстановления за этот период.
Оценка размера полной резервной копии базы данных
Создание расписания резервного копирования
Влияние, оказываемое осуществлением резервного копирования на выполняемые транзакции, минимально, поэтому операции резервного копирования могут выполняться одновременно с выполнением обычных операций. Резервное копирование в SQL Server можно выполнять с минимальным влиянием на рабочие нагрузки.
Сведения об ограничениях параллелизма во время резервного копирования см. в разделе Общие сведения о резервном копировании (SQL Server).
После принятия решения о том, какой тип резервного копирования необходим и как часто его выполнять, рекомендуется запланировать регулярное резервное копирование как часть плана обслуживания базы данных. Дополнительные сведения о планах обслуживания и об их создании для резервных копий баз данных и журналов см. в разделе Use the Maintenance Plan Wizard.
Тестирование резервных копий
Можно сказать, что стратегия восстановления отсутствует, пока резервные копии не протестированы. Очень важно полностью протестировать стратегию резервного копирования для каждой базы данных, восстанавливая копию базы данных в систему тестирования. Необходимо протестировать восстановление каждого типа резервной копии, которую планируется использовать. Также рекомендуется после восстановления резервной копии выполнить проверку согласованности базы данных с помощью инструкции DBCC CHECKDB базы данных, чтобы убедиться, что носитель резервной копии не поврежден.
Проверка стабильности и согласованности носителя
Используйте параметры проверки, предоставляемые служебными программами резервного копирования (команда T-SQL BACKUP, планы обслуживания SQL Server, программное обеспечение или решение для резервного копирования и т. д.). Пример см. в разделе [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Используйте дополнительные функции, например BACKUP CHECKSUM, чтобы выявить проблемы с самим носителем резервного копирования. Дополнительные сведения см. в статье Возможные ошибки носителей во время резервного копирования и восстановления (SQL Server)
Стратегия резервного копирования и восстановления документов
Рекомендуется документировать процедуры резервирования и восстановления и хранить копию этой документации в документации по задаче. Также рекомендуется вести руководство по эксплуатации для каждой базы данных. Такое руководство по эксплуатации должно указывать расположение резервных копий, имена устройств резервного копирования (если есть), время, требуемое для восстановления тестовой резервной копии.
Отслеживание хода выполнения с помощью xEvent
Операции резервного копирования и восстановления могут выполняться в течение длительного времени из-за размера базы данных и сложности выполняемых операций. При возникновении проблемы с любой из операций можно использовать расширенное событие backup_restore_progress_trace для отслеживания хода выполнения в реальном времени. Дополнительные сведения о расширенных событиях см. в разделе Расширенные события.
С помощью расширенных событий backup_restore_progress_trace можно повысить производительность и существенно сэкономить дисковое пространство. Используйте эти события в течение короткого периода времени, проявляйте внимание и тщательно проверяйте свои изменения перед их реализацией в рабочей среде.
Копирование баз данных путем создания и восстановления резервных копий
SQL Server 2016 использует путь по умолчанию, отличный от пути, использованного в предыдущих версиях. Поэтому для восстановления резервной копии базы данных, созданной в месте расположения по умолчанию для ранних версий, необходимо использовать параметр MOVE. Сведения о новом пути по умолчанию см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server. Дополнительные сведения о перемещении файлов баз данных см. в статье «Перемещение файлов баз данных» далее в этом подразделе.
Основные этапы копирования базы данных, используя функции резервного копирования и восстановления
При использовании резервного копирования и восстановления для копирования базы данных на другой экземпляр SQL Server компьютер-источник и целевой компьютер могут быть любой платформой, на которой запускается SQL Server.
Восстановите резервную копию базы данных- источника на целевом компьютере. При восстановлении базы данных автоматически создаются все ее файлы.
Рассматриваются дополнительные вопросы, которые могут повлиять на процесс.
Перед восстановлением файлов базы данных
При восстановлении базы данных необходимые файлы базы данных создаются автоматически. По умолчанию у файлов, созданных SQL Server в процессе восстановления, те же имена и пути, что и у файлов резервной копии исходной базы данных на компьютере-источнике.
Также при восстановлении базы данных, если это необходимо, можно указать сопоставление дисков, имена файлов или путь для восстановления.
Это может быть необходимо в следующих ситуациях.
Нужная структура каталогов или сопоставление дисков, используемые на исходном компьютере, могут отсутствовать на другом компьютере. Например, возможно, резервная копия содержит файл, который нужно восстановить на диск E, но на целевом компьютере диска E — нет.
на целевом диске может быть недостаточно свободного места;
Если используется имя базы данных, которое уже существует на целевом сервере восстановления, а имя каждого из ее файлов совпадает с именем файла базы данных в резервном наборе данных, происходит одно из следующих действий.
Если существующий файл базы данных может быть перезаписан, он будет перезаписан (это не затронет файл, относящийся к базе данных с другим именем).
Если существующий файл не может быть перезаписан, возникнет ошибка восстановления.
Перемещение файлов баз данных
Если файлы резервной копии базы данных невозможно восстановить на целевом компьютере, необходимо переместить файлы в новое место назначения, где они могут быть восстановлены. Пример:
Нужно восстановить базу данных из резервных копий, созданных в месте расположения по умолчанию для предыдущей версии.
Может оказаться необходимым восстановить некоторые файлы базы данных из резервной копии на другой диск из-за нехватки места на диске по умолчанию. Такое случается довольно часто, потому что у большинства компьютеров в организациях разное число и параметры дисковых накопителей и различные конфигурации программного обеспечения.
Может оказаться необходимым создать копию существующей базы данных на том же компьютере для тестирования. В этом случае файлы базы данных для исходной базы данных уже существуют, и когда во время операции восстановления создается копия базы данных, должны быть указаны другие имена файлов.
Дополнительные сведения см. в подразделе «Восстановление файлов и файловых групп в новое место назначения» далее в этом разделе.
Изменение имени базы данных
Имя базы данных, явно задаваемое при ее восстановлении, автоматически используется в качестве нового имени базы данных. Так как такой базы данных еще не существует, новая база данных создается из файлов резервной копии.
Обновление базы данных с помощью восстановления
При восстановлении резервных копий из предыдущей версии желательно заранее знать, существует ли на целевом компьютере путь (диск и каталог) для каждого полнотекстового каталога резервной копии. Чтобы получить список всех логических и физических имен — пути и имени файла — всех файлов резервной копии, включая файлы каталогов, выполните инструкцию RESTORE FILELISTONLY FROM Дополнительные сведения см. в разделе Инструкция RESTORE FILELISTONLY (Transact-SQL).
Если нужный путь на целевом компьютере не существует, есть два варианта.
Создать нужное сопоставление дисков или структуру каталогов на целевом компьютере.
Переместить файлы каталогов на новое место назначения во время операции восстановления с помощью предложения WITH MOVE инструкции RESTORE DATABASE. Дополнительные сведения см. в разделе RESTORE (Transact-SQL)невозможно.
Дополнительные сведения об альтернативных параметров обновления полнотекстовых индексов см. в разделе Обновление полнотекстового поиска.
Владелец базы данных
При восстановлении базы данных на другом компьютере имя входа пользователя SQL Server или Microsoft Windows, начавшего процесс восстановления, автоматически становится именем владельца базы данных. При восстановлении базы данных системный администратор или владелец новой базы данных могут сменить ее владельца. Для предотвращения несанкционированного восстановления базы данных устанавливайте пароли на носители или сами резервные копии.
Управление метаданными при восстановлении базы данных на другой экземпляр сервера
Чтобы обеспечить целостность работы пользователей и приложений при восстановлении базы данных на другой экземпляр сервера, на новом экземпляре необходимо повторно создать некоторые или все метаданные, например имена входа и задания. Дополнительные сведения см. в статье Управление метаданными при обеспечении доступности базы данных на другом экземпляре сервера (SQL Server).
Просмотр файлов данных и журналов в резервном наборе данных
Восстановление файлов и файловых групп в новом расположении
Восстановление файлов и файловых групп поверх существующих файлов
Восстановление базы данных с новым именем
Перезапуск прерванной операции восстановления
Изменение владельца базы данных
Копирование базы данных с помощью управляющих объектов SQL Server (SMO)