Sql server, как восстановить базу из бекапа или из другой базы

Инструкция по созданию/восстановлению резервных копий баз данных

2015-08-26 · Posted in SQL

Создание резервной копии базы данных (backup)

1)Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).

2)Подключиться к серверу под учетной записью администратора или владельца БД (можно использовать встроенную учетную запись «sa», пароль для которой задавался при установке SQL Server, либо выбрать вариант «Проверка подлинности Windows» в случае если текущий пользователь сеанса Windows обладает правами администратора в SQL Server, а также использовать любую другую учетную запись SQL Server или Windows, которая включена в роль «db_owner» копируемой базы):

3)Нажать правой кнопкой мыши на имени копируемой БД (в разделе «Базы данных») и выбрать меню «Задачи/Создать резервную копию»:

4)В разделе «Назначение» указать путь и имя файла (путь всегда задается для компьютера, где установлен сам SQL Server!), в который будет выгружена база данных, для чего использовать кнопки «Удалить» и «Добавить» до тех пор, пока в поле «Создать резервную копию на» не будет отображен ровно один желаемый путь:

5)На странице «Параметры» установить переключатель «Создать резервную копию в новом наборе носителей…» и галочку «Проверить резервную копию после завершения»:

6)Нажать кнопку «ОК».

Восстановление базы данных из резервной копии (restore)

1)Запустить утилиту SQL Server Management Studio (из состава MS SQL Server).

3)Нажать правой кнопкой мыши на разделе «Базы данных» и выбрать меню «Восстановить базу данных»:

4)На странице «Общие» выполнить следующие действия:

В поле «В базу данных» ввести имя для восстанавливаемой базы (если будет указано имя существующей базы, то это эквивалентно тому, что сначала полностью удалить существующую базу и затем восстановить из резервной копии новую базу, т.е. все данные существующей базы будут утеряны!);

Установить переключатель «С устройства» и указать путь к файлу резервной копии, нажав кнопку «…»;

Установить галочку «Восстановить» в нужной строке (которых может быть несколько, если один файл *.bak содержит несколько резервных копий базы):

5)На странице «Параметры» установить галочку «Перезаписать существующую базу данных» и проверить пути в списке «Восстановить файлы базы данных как» (должны указывать на существующую папку на SQL-сервере, к которой предоставлены права на запись – пути по умолчанию обычно должны заканчиваться папкой DATA, а не просто MSSQL):

6)Нажать кнопку «ОК».

Кроме того, после переноса базы на любой новый SQL-сервер придется заново настроить авторизацию и регулярное резервное копирование в соответствии с инструкцией по установке сетевой версии соответствующей программы.

Для переноса большого количества «имен входа» (логинов) с их паролями на новый сервер можно воспользоваться способом, описанным в статье базы знаний Microsoft. Однако, следует иметь в виду, что данный способ не переносит серверные права логинов (принадлежность «серверным ролям», право на «просмотр состояния сервера» и т.п.), а также не подходит для переноса с SQL 2012 на более ранние версии SQL Server.

Восстановление базы данных из архивных копий

В Enterprise Manager установите курсор на название поврежденной базы и из контекстного меню вызовите последовательно пункты «Все задачи» — «Restore Database …»:

В открывшемся окне на закладке «General» в поле «Restore as database:» по умолчанию будет выбрана поврежденная база данных (на ней стоял курсор во время вызова пункта меню «Restore Database…»). Вы можете указать другое имя для восстанавливаемой базы данных. В нашем примере мы этого делать не будем.

В группе переключателей «Restore:» выберите «From device». Это, наверное, самый длительный путь восстановления, но таким образом Вы лучше поймете, что и как происходит при восстановлении.

В поле «Devices:» выберите файл полной архивной копии базы данных. Это делается через кнопку «Select Devices…». Процесс выбора файла для восстановления я описывать не буду — он достаточно прост и понятен.

Установите переключатели «Restore backup set» и «Database — complete». Этим мы указываем SQL серверу, что файл, выбранный в поле «Devices:», — это полная копия базы данных.

Перейдите на закладку «Options». В таблице «Restore database files as:» поля будут заполнены информацией из файла полной копии базы данных. Оставим их без изменений. Тем самым мы говорим, что база данных будет восстанавливаться на место поврежденной.

Обратите внимание на группу переключателей «Recovery completion state». Нас будут интересовать первые два значения:. 1

Leave database operational. No additional transaction logs can be restored. (Оставить базу данных работающей. Архивы файла транзакций не могут быть восстановлены)

1. Leave database operational. No additional transaction logs can be restored. (Оставить базу данных работающей. Архивы файла транзакций не могут быть восстановлены).

Этот режим выбирается в 2х случаях:

Мы восстанавливаем базу данных из полной архивной копии, но архивы файла транзакций восстанавливать не будем;
Мы восстанавливаем последний архив файла транзакций.

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

2. Leave database nonoperational but able to restore additional transaction logs. (Оставить базу данных в нерабочем состоянии, но с возможностью восстанавливать архивы файла транзакций).

Этот режим надо использовать, когда мы хотим восстановить базу данных из полной архивной копии и набора архивов файла транзакций (за исключением последнего). По условиям нашей задачи у нас есть файл полной копии базы данных и архивы файла транзакций. Поэтому при восстановлении базы данных из полной копии выбираем режим 2 — после восстановления оставить базу в нерабочем состоянии для возможности восстановления данных из архивов файла транзакций. Теперь можно нажать кнопку «ОК». После окончания процедуры восстановления статус базы данных поменяется на «Loading»:

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

Восстановление архива файла транзакций выполняется аналогично восстановлению из полной копии базы данных. Выберите из контекстного меню пункты «Все задачи» — «Restore Database …».

На этот раз в окне «Devices:» выберите архив файла транзакций, созданный первым после полной копии базы данных. Далее, при последующих операциях восстановления, выбирайте архивы файла транзакций в хронологическом порядке.

Укажите SQL серверу, что будет восстанавливаться файл транзакций. Для этого установите переключатель в положение «Transaction log». На закладке «Options» выберите восстановление в режиме 2.

Нажмите кнопку «ОК». Подобным образом восстановите данные из всех архивов файла транзакций, за исключением последнего. Для него выберите восстановление в режиме 1.

Все! Восстановление базы данных из архивных копий завершено. Запускайте программу 1С и проверяйте ее работоспособность.

Conclusion

This demonstration is a seamless setup where you are not facing any corruption, access, lost database files, and virus affected databases. In this case, you can easily restore a database by writing a simple T-SQL script as shown above to achieve a full database restore.

Did you find yourself in the worst scenario where you cannot complete a restore due to the possible corruption in the database or even the backups? Good news, I would like to introduce to you a robust SQL Database Repair Tool that will not only fix your database or backups with the least minimal data loss but also potentially help you restore deleted records from the database.

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» и сделать настройки, если требуется (не обязательно):

Нажимаем ОК и дожидаемся сообщения об успешном восстановлении базы:

Если база данных содержала информацию о пользователях, то необходимо проверить их связь с логинами. Для этого в дереве находим вновь воссозданную БД, раскрываем пункт «Security» — «Users» и выбираем какого-либо существующего пользователя и в контекстном меню переходим на пункт «Properties«:

В открывшемся окне свойств пользователя мы видим, что пользователь существует только в контексте это базы (т.е. не связан ни с каким логином «SQL user without login»):

Это не позволит Вам подключиться к базе данных под данным пользователем. Поэтому создадим логин для этого пользователя (если логин уже существует, пропустите этот шаг). В дереве объектов СУБД перейдем на уровень выше (выше, чем текущая БД) в пункт «Security» и в контекстном меню пункта «Logins» выберем «New Login. «:

В открывшемся окне заполняем: — login name — имя логина (я всегда делаю его одноименным с пользователем базы) — выбираем параметр «SQL Server authentication» и задаем пароль — снимаем «галку» «Enforce password policy» — default database — прописываем нужную базу данных:

Далее можно перейти в левом меню в раздел «User Mapping«, где увидим, что можно закрепить за созданным логином некого пользователя в базе. Если у Вас база данных не содержит никаких пользователей, то задайте эти параметры, как показано на рисунке (в противном случае пропустите этот шаг) и будет автоматически создан пользователь БД связанный с данным логином:

При этом логин будет создан, но не связан с пользователем.

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

Теперь, если снова перейти в свойства пользователя БД, то мы видим, что пользователь связан с только что созданным логином.

Типы резервного копирования SQL Server

Полное (Full Backup)

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

Полную резервную копию вы можете восстановить за 1 шаг, так как она не требует других дифференциальных/инкрементальных копий.

Если модель восстановления базы SQL данных установлена как “Полная”, то при восстановлении бекапа вы можете указать параметр “STOPAT”, где указывается время (до секунды) на котором нужно остановить восстановление данных. Например, сотрудник внёс некорректные данные в 14:46:07, с помощью параметра STOPAT вы можете восстановить данные на момент 14:46:06

Дифференциальное

Дифференциальное или разностное резервное копирование — это копирование только тех данных, которые появились с момента последней полной резервной копии.

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

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

Например, если полная резервная копия весит 300 GB, а дифференциальная спустя час работы 5 GB, то спустя сутки это будет 120 GB, что делает использование данного типа копий нерациональным.

Журнал транзакций

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

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

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

Tail-Log

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

Tail-Log бекап рекомендуется делать перед восстановлением копий журнала транзакций, чтобы не потерять транзакции между последним бекапом и текущим моментом времени.

Copy-only

Этот вид бекапа не может служить “базой” для дифференциальных резервных копий и для копий журнала транзакций. Copy-only бекап не нарушает текущую цепочку резервных копий (полный-> дифференциальный или полный -> копии журналов транзакций) и используется только в том случае, если вам нужно снять полную резервную копию, не задевая текущую цепочку бекапов.

За исключением этих нюансов – ничем не отличается от обычной полной копии.

Частичная резервная копия

Partial backup этот тип резервной копии используется для того, чтобы снять копии с read-only файловых групп. На практике используется редко.

Контрольные суммы резервных копий

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

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

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

Инструкция BACKUP никогда не изменяет исходную страницу на диске и ее содержимое.

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

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

Если во время проверки операция резервного копирования обнаруживает ошибку страницы, резервное копирование прерывается с ошибкой.

Примечание

Дополнительные сведения о контрольной сумме страниц и обнаружении разрывов страниц см. в описании параметра PAGE_VERIFY инструкции ALTER DATABASE. Дополнительные сведения см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).
Независимо от того, присутствует ли контрольная сумма страницы или нет, инструкция BACKUP создает отдельные контрольные суммы резервных копий для потока резервных файлов. Дополнительно операции восстановления могут использовать контрольные суммы резервных копий для проверки наличия повреждений в резервных файлах. Контрольная сумма резервной копии хранится на носителе резервных файлов, а не на страницах базы данных. Контрольную сумму резервной копии также можно использовать во время восстановления.
Резервный набор данных помечен как содержащий контрольные суммы резервных копий (в столбце has_backup_checksums таблицы msdb..backupset). Дополнительные сведения см. в разделе backupset (Transact-SQL).

Во время операции восстановления, если на резервном носителе имеются контрольные суммы, по умолчанию и инструкция RESTORE, и инструкция RESTORE VERIFYONLY проверяют контрольные суммы резервных копий и страниц. Если у резервной копии нет контрольной суммы, все операции восстановления продолжаются без проверок. Данное поведение объясняется тем, что без контрольной суммы резервной копии операция восстановления не может достоверно проверять контрольные суммы страниц.

ограничения

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

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

Без сертификата или асимметричного ключа вы не сможете восстановить базу данных. Сохраняйте сертификат, который использовали для шифрования ключа шифрования базы данных, в течение всего периода хранения резервной копии. Дополнительные сведения см. в статье SQL Server Certificates and Asymmetric Keys

Чтобы восстановить зашифрованную базу данных, потребуется доступ к сертификату или асимметричному ключу, использовавшемуся для шифрования этой базы данных. Без сертификата или асимметричного ключа вы не сможете восстановить базу данных. Сохраняйте сертификат, который использовали для шифрования ключа шифрования базы данных, в течение всего периода хранения резервной копии. Дополнительные сведения см. в статье SQL Server Certificates and Asymmetric Keys.

При восстановлении базы данных более старой версии до более новой версии SQL Server эта база данных будет автоматически обновлена до новой версии. Это исключает возможность использования базы данных с более старой версией Компонент Database Engine. Но это относится к обновлению метаданных и не влияет на уровень совместимости базы данных. Если уровень совместимости пользовательской базы данных до обновления был 100 или выше, после обновления он останется таким же. Если уровень совместимости до обновления равен 90, в обновленной базе данных устанавливается уровень совместимости 100, что является самым низким поддерживаемым уровнем совместимости в SQL Server 2016 (13.x) и выше. Дополнительные сведения см. в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).

Как правило, база данных сразу становится доступной. Однако если база данных SQL Server 2005 (9.x) содержит полнотекстовые индексы, то в процессе обновления будет произведен их импорт, сброс или перестроение в зависимости от установленного значения свойства сервера Режим обновления полнотекстового каталога . Если выбран режим обновления Импортировать или Перестроить, то полнотекстовые индексы во время обновления будут недоступны. В зависимости от объема индексируемых данных процесс импорта может занять несколько часов, а повторная сборка — до десяти раз дольше.

Если выбран режим обновления Импорт, а полнотекстовый каталог недоступен, то связанные с ним полнотекстовые индексы будут перестроены. Сведения о просмотре и изменении параметра Режим обновления полнотекстового поиска см. в статье Наблюдение за полнотекстовым поиском для экземпляра сервера и управление им.

Сведения о SQL Server восстановлении из Хранилище BLOB-объектов Azure см. в статье SQL Server Резервное копирование и восстановление с помощью Microsoft Хранилище BLOB-объектов Azure.

осстановить SQL базу из .bak – Это абсолютно бесплатно

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

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

Способ 1 – использование SSMS

Шаг 1. Откройте SSMS и подключитесь к экземпляру SQL Server 2014.

Шаг 2: Затем перейдите в Обозреватель объектов и щелкните Имя сервера, чтобы развернуть дерево серверов.

Шаг 3: Перейдите в Базы данных и откройте базу данных, которую вы хотите восстановить в SQL Server 2014.

Шаг 4: Щелкните правой кнопкой мыши на Базе данных, выберите Задачи> Восстановить> База данных.

Шаг 5: Когда появится окно Восстановить базу данных, сделайте следующее:

1. В разделе «Источник для восстановления» выберите параметр «С устройства».
2. Нажмите кнопку обзора рядом с «С устройства», чтобы открыть окно Выбор устройств резервного копирования.
3. В появившемся окне нажмите Добавить.
4. В появившемся диалоговом окне найдите и выберите файл .BAK, который вы хотите восстановить, и нажмите кнопку «ОК».
5. Нажмите кнопку ОК, чтобы вернуться в окно восстановления базы данных.

Шаг 6: Файл .BAK появится в окне восстановления базы данных.

Шаг 7: На левой боковой панели выберите «Опции» в разделе «Выбор страницы» и выполните следующие действия:

а. В разделе «Параметры восстановления» выберите «Перезаписать существующую базу данных (С ЗАМЕНЕЙ)».

Шаг 8: Чтобы продолжить процесс восстановления, нажмите кнопку ОК.

Шаг 9: Отображается окно с прогрессом восстановления.

Шаг 10: Нажмите OK, когда появится сообщение «Восстановление базы данных завершено успешно».

Способ 2 – восстановить базу данных с помощью запроса

Если вам интересно узнать, «Как восстановить базу данных в SQL Server из bak-файла с помощью запроса», выполните следующие действия:

Шаг 1. Запустите SSMS, а затем подключитесь к экземпляру SQL Server 2014.

Шаг 2: Выберите опцию New Query.

Шаг 3: В окне редактора запросов выполните любой из следующих операторов RESTORE:

ПРИМЕЧАНИЕ. Для восстановления всей базы данных из файла .bak используйте следующую команду:

ПРИМЕЧАНИЕ. Замените FileName на имя файла, который вы хотите восстановить.

ПРИМЕЧАНИЕ. Замените «DBName» именем базы данных, которую вы хотите восстановить. Также замените «BackupFilePath» на путь вашего файла резервной копии базы данных, а «BackupFileName» на имя вашего .bak-файла.

Способ 3 – Восстановление базы данных SQL с помощью Replace PowerShell

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

Внимание!!

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

Последние мысли

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

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