Как восстановить таблицу в ms sql

Обзор восстановления и восстановления (SQL Server)

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

База данных ( полное восстановление базы данных) Вся база данных восстанавливается и восстанавливается, а база данных находится в автономном режиме во время операций восстановления и восстановления.
Файл данных ( восстановление файла) Файл данных или набор файлов данных возвращается в исходное состояние и восстанавливается. Во время восстановления файла файловые группы, содержащие файлы, автоматически находятся в автономном режиме во время восстановления. Любые попытки подключения и работы с файловой группой вне сети приведут к ошибке.
Страница данных ( восстановление страницы) При использовании модели полного восстановления или модели восстановления с неполным протоколированием можно восстановить отдельные станицы. Восстановление страниц может применяться для любой базы данных вне зависимости от числа файловых групп.

Резервное копирование и восстановление SQL Server работают во всех поддерживаемых операционных системах. Дополнительные сведения о поддерживаемых операционных системах см. в разделе Требования к оборудованию и программному обеспечению для установки SQL Server 2016. Сведения о поддержке резервных копий более ранних версий SQL Server см. в подразделе «Поддержка совместимости» раздела RESTORE (Transact-SQL).

Обзор сценариев восстановления

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

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

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

1 Восстановление в Сети поддерживается только в выпуске Enterprise.

Шаги по восстановлению базы данных

Для восстановления файла ядро СУБД выполняет два шага:

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

Для восстановления базы данных ядро СУБД выполняет три шага.

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

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

Преимущества восстановления файла или страницы

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

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

Восстановление и журнал транзакций

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

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

Этап 1 — это этап анализа, который анализирует журнал транзакций, чтобы определить, что является последней контрольной точкой, и создает таблицу грязной страницы (DPT) и активную таблицу транзакций (ATT). ТГС содержит записи о страницах, которые были «грязными» на момент завершения работы базы данных. ATT содержит записи транзакций, которые были активными во время завершения работы базы данных.
Этап 2 — это этап повтора, который выполняет перекат всех изменений, записанных в журнале, которые, возможно, не были записаны в файлы данных во время завершения работы базы данных. Минимальный регистрационный номер транзакции в журнале (minLSN), необходимый для успешного восстановления всей базы данных, находится в ТГС и отмечает начало операций восстановления, необходимых для всех «грязных» страниц. На этом этапе ядро СУБД SQL Server записывает на диск все грязные страницы, принадлежащие зафиксированным транзакциям.
Этап 3 — это этап отмены, который откатывает неполные транзакции, найденные в ATT, чтобы убедиться, что целостность базы данных сохраняется. После отката база данных включается в режим «в сети», и больше никакие резервные копии журнала транзакций не могут быть применены.

Сведения о ходе каждого этапа восстановления базы данных регистрируются в журнале ошибок SQL Server. Ход восстановления базы данных также можно отслеживать с помощью расширенных событий. Дополнительные сведения см. в записи блога New extended events for database recovery progress (Новые расширенные события для процесса восстановления базы данных).

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

Чтобы максимально повысить доступность баз данных в корпоративной среде после запуска службы SQL Server, например после отработки отказа экземпляра отказоустойчивого кластера AlwaysOn или перезапуска на месте, SQL Server Enterprise Edition может перевести базу данных в сеть после этапа повторного выполнения, а этап отмены по-прежнему выполняется. Это также называется быстрым восстановлением.
Однако быстрое восстановление недоступно при переходе базы данных в интерактивное состояние, но служба SQL Server не была перезапущена. Например, выполнение ALTER DATABASE AdventureWorks SET ONLINE; не позволит базе данных находиться в состоянии чтения и записи до завершения всех трех этапов восстановления.

Модели восстановления и поддерживаемые операции восстановления

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

Операция восстановления	Модель полного восстановления	Модель восстановления с неполным протоколированием	Простая модель восстановления
Восстановление данных	Полное восстановление (при наличии журнала).	С некоторыми потерями данных.	Будут потеряны все данные с момента создания последней полной или разностной резервной копии.
Восстановление на момент времени	На любое время, сохранившееся в резервных копиях журналов.	Запрещено, если резервная копия журналов содержит какие-либо изменения с неполным протоколированием.	Не поддерживается.
Восстановление файла 1	Поддерживается полностью.	Иногда. 2	Только для вторичных файлов, доступных только для чтения.
Восстановление страницы 1	Поддерживается полностью.	Иногда. 2	Нет.
Восстановление на уровне файловой группы 1	Поддерживается полностью.	Иногда. 2	Только для вторичных файлов, доступных только для чтения.

1 Доступно только в выпуске Enterprise SQL Server

2 Сведения о необходимых условиях см. в разделе «Ограничения восстановления» в рамках простой модели восстановления далее в этой статье.

Независимо от модели восстановления базы данных резервное копирование SQL Server не может быть восстановлено до версии ядра СУБД SQL Server, которая старше версии, созданной резервной копией.

Сценарии восстановления в простой модели восстановления

В простой модели восстановления предусмотрены следующие ограничения для операции восстановления.

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

Если приведенные выше ограничения противоречат задачам восстановления, рекомендуется использовать модель полного восстановления. Дополнительные сведения см. в разделе Общие сведения о резервном копировании (SQL Server).

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

Восстановление в модели восстановления с массовым журналом

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

Основные сведения о модели восстановления с неполным протоколированием см. в разделе Журнал транзакций (SQL Server).

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

Ограничения для восстановления на определенный момент времени

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

Ограничения для восстановления в сети

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

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

Если эти условия не выполнены, последовательность восстановления в сети завершается ошибкой.

Перед тем, как начать восстановление в сети, рекомендуется переключиться к модели полного восстановления. Дополнительные сведения см. в разделе Модели восстановления (SQL Server).

Дополнительные сведения о выполнении восстановления в сети см. в разделе Восстановление в сети (SQL Server).

Помощник по восстановлению базы данных (среда SQL Server Management Studio)

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

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

Читать:

Где находится microsoft visual c на windows 7

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

Помощник по восстановлению: введение
Помощник по восстановлению: использование среды SSMS для создания и восстановления раздельных резервных копий

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

Ускоренное восстановление базы данных доступно, начиная с SQL Server 2019 (15.x), и в Базе данных SQL Azure. Ускорение восстановления базы данных значительно повышает доступность базы данных, особенно в присутствии длительных транзакций, перепроектируя процесс восстановления ядра СУБД SQL Server. База данных, для которой было включено ускоренное восстановление, значительно быстрее восстанавливается после отработки отказа или другого неясного завершения работы. Если включено, ускоренное восстановление базы данных также значительно быстрее завершает откат отмененных продолжительных транзакций.

Вы можете включить ускоренное восстановление базы данных для каждой базы данных в SQL Server 2019 (15.x) с помощью следующего синтаксиса:

ALTER DATABASE [] SET ACCELERATED_DATABASE_RECOVERY = ON;

Ускорение восстановления базы данных по умолчанию в базе данных SQL Azure.

Общие сведения о резервном копировании (SQL Server)
Журнал транзакций (SQL Server)
Руководство по архитектуре журнала транзакций SQL Server и управлению им
Резервное копирование и восстановление баз данных SQL Server
Применение резервных копий журналов транзакций (SQL Server)

Восстановление удаленных данных в SQL Server

13 Aпреля 2014 Программирование
6 0

У нас на работе парень (извинюсь, но он из Индии) запустил update без указания секции where прямо на боевой базе данных. Результат — записи всей таблицы обновились, хотя ему нужно были изменить только одну. Ну со всеми бывает, надеюсь, что будет редко и меня обойдет.

Самое обидное, что он делал все по правилам — открыл транзакцию, запустил обновление, увидел, что изменено 11 тысяч строк вместо одной и почему-то проигнорировал и закомитил данные. Только потом увидел свой косяк.

Всего 11 тысяч строк, но в достаточно важной таблице. Восстанавливать базу из резервной копии — слишком долго. Нужно за просить последнюю копию у админов, получить разрешение на копирование 300 гигабайт PCI ограниченных данных, восстановить где-то на сервере и складировать данные. Нереально. Проще обновить все затертые данные случайным мусором и сказать, что во всем виноват Билл Гейтс.

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

Потом решили копать в сторону репликации. Как раз три дня назад мы сделали полный снимок и инициализировали заново один из серверов, потому что репликация глюкнула. Я точно не знаю причин, почему сделали инициализацию заново, потому что меня не было на работе, да мне и все равно, главное, что нам повезло. Залезли в папку, где находятся файлы с последнего снапшота и скопировали файлы .bcp (по идее это означает Bulk Copy, но тут я могу ошибаться). Скопировали файлы на локальный диск одного из серверов баз и используя утилиту командной строки bcp скормили это все серверу. SQL Sever загрузил данные в новую таблицу и мы из этой таблицы скопировали затертые данные в боевую базу данных. Из 11 тысяч записей в репликационных данных мы нашли почти все записи, кроме новых строк, которые создали с момента полной копии.

Восстановить новые записи, которые были созданы уже после последней полной копии можно через просмотр записей в журнале, который храниться на дистрибуторе в базе данных distribution. Просто выполняем команду sp_browsereplcmds и она показывает все, что нужно. Только отфильтруй записи по таблице, указав параметр article_id.

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

Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым

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

Использование операции UPDATE или DELETE без условия WHERE часто приводит к потере данных из таблиц. Удаленные строки могут быть восстановлены, если известно время их удаления. Это можно сделать с помощью порядковых номеров регистрации (LSN). Это уникальный идентификатор, который присваивается каждой записи в журнале транзакций SQL Server. В следующем разделе статьи мы рассмотрим восстановления удаленных данных с помощью журнала транзакций и номеров LSN.

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

Чтобы восстанавливать строки, удаленные из таблицы, база данных SQL Server должна иметь BULK-LOGGED или FULL модель восстановления в момент первого удаления. Чтобы журналы были доступны для восстановления данных, необходимо выполнить следующие действия (применимо в SQL Server 2005, 2008, 2012, 2014 и 2016):

Шаг 1

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

SELECT * FROM Table_name

Шаг 2

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

USE Databasename GO BACKUP LOG [Databasename] TO DISK = N'D:DatabasenameRDDTrLog.trn' WITH NOFORMAT, NOINIT, NAME = N'Databasename-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Шаг 3

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

USE Databasename GO Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

В результате работы запроса мы получим идентификатор транзакции (например, 000: 000001f3) для удаленных строк.

Шаг 4

Определим конкретное время, когда строки были удалены. Для этого мы используем идентификатор транзакции 000: 000001f3 . Выполните следующий запрос:

USE Databasename GO SELECT [Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = ‘000:000001f3' AND [Operation] = 'LOP_BEGIN_XACT'

В результате выполнения этого запроса мы получим значение текущего порядкового номера журнала (LSN). Например, 00000020: 000001d0: 0001.

Шаг 5

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

USE Databasename GO RESTORE DATABASE Databasename_COPY FROM DISK = 'D:DatabasenameRDDFull.bak' WITH MOVE 'Databasename' TO 'D:RecoverDBDatabasename.mdf', MOVE 'Databasename_log' TO 'D:RecoverDBDatabasename_log.ldf', REPLACE, NORECOVERY; GO

Шаг 6

Теперь применим журнал транзакций для восстановления удаленных строк, используя LSN 00000020: 000001d0: 0001:

USE Databasename GO RESTORE LOG Databasename_COPY FROM DISK = N'D:DatabasenameRDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001' Note: Since LSN values are in Hexadecimal form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above.

Шаг 7

Процесс восстановления записей успешно завершен. Проверьте, восстановлены ли удаленные записи в базе данных с именем Databasename_Copy.

USE Databasename_Copy GO Select * from Table_name

Недостатки подхода с использованием журнала транзакций

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

Заключение

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

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

Существует ли способ восстановления одной таблицы, без рестора всей БД? Модель восстановления полная, бэкапы лога есть. Бэкапы не сжатые. Версия MS SQL 2016.

Отслеживать
Nick Proskuryakov
задан 11 сен 2019 в 15:56
Nick Proskuryakov Nick Proskuryakov
3,732 2 2 золотых знака 16 16 серебряных знаков 40 40 бронзовых знаков
Нет, нету. Поднимите бэкап рядом на том же сервере, и перекиньте данные между базами.
– user177221
11 сен 2019 в 19:01

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

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

Litespeed. Стоит в районе 1000$. Есть пробная версия на 15 дней. Два ограничения при работе с нативными бэкапами — не умеет делать восстановление таблиц если бэкап сжат (compressed) или зашифрован (encrypted).
RedGate SQL Backup. Стоит 1070$. Есть пробная версия на 30 дней. Также не умеет работать с нативными бэкапами если они сжаты (compressed) или зашифрованы (encrypted).

Есть еще Поэтапное восстановление (SQL Server) — встроенный механизм, но он требует заранее вынесенной в отдельную файловую группу таблицу и специального типа бэкапа.

Отслеживать
ответ дан 18 июн 2020 в 20:20
Konstantin Taranov Konstantin Taranov
496 4 4 серебряных знака 10 10 бронзовых знаков

sql
sql-server
резервное-копирование
таблицы

Важное на Мете

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.