sql.ru

По материалам статьи Microsofr: «INF: Effect of Database File Additions or Deletions on Database Recovery»

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

Server: Msg 3155, Level 16, State 1, Line 1 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

Поэтому, Вы должны создать полную резервную копию базы данных после добавления или удаления файла к базе данных, чтобы начать новую последовательность резервных копий журнала.
Также, аналогичную описанной проблему можно наблюдать при установке Log Shipping. Когда Вы устанавливаете передачу записей журнала между двумя SQL Server 7.0, резервные копии записей transaction log будут непрерывно передаваться и восстанавливаться на резервном сервере. Если по какой - любой причине, к промышленной базе данных будет добавлен журнал, резервные копии transaction log не смогут быть восстановлены на резервном сервере без повторной синхронизации журнала, передающегося на резервный сервер.
Например, Вы можете добавить файл базы данных из-за недостатка дискового пространства. После того, как Вы добавите журнал, передача записей журнала не может быть продолжена и записи не будут восстановлены в transaction log второго сервера. Попытки восстановления на резервном сервере transaction log приведут к сбою, с уже показанным здесь сообщением об ошибке.
В SQL Server 2000 можно использовать команду WITH MOVE при восстановлении резервной копии transaction log, которая была создана после добавления файла базы данных. Операция восстановления в SQL Server 2000 может учитывать дополненные файлы базы данных. Например, Вы можете восстанавливать первую резервную копию transaction log, которая была создана после добавления файла базы данных, с использованием нижеследующего запроса:

RESTORE LOG NORTHWIND FROM DISK =path for the transaction log backup file
WITH MOVE Logical name of the new data file TO physical name of the new data file
(where you want the file to be created on the destination server),
NORECOVERY

Результат исполнения запроса, это база данных вместе с добавленным файлом. Операция восстановления создаст и новый файл, что позволит начать в него запись данных. Вы можете использовать команду RESTORE без опции MOVE. Если файл базы данных удален из базы данных SQL Server 2000, операция восстановления продолжится без любs[ изменений, и SQL Server самостоятельно узнает об удаление файлов и соответственно откорректирует базу данных.
Ниже, в двух разделах, рассматривается последовательность шагов, которые описывают поведение в SQL Server 7.0 и его более совершенного продолжения SQL Server 2000.

SQL Server 7.0

Рассмотрим случай, когда используется промышленная база данных SQL Server 7.0, работающая в режиме 24х7 (круглосуточно всю неделю). При попытке восстановить её из backup, Вы должны вначале восстановить последнюю, полную резервную копию (используя WITH NORECOVERY), а затем, все n-1 резервные копии transaction log (используя WITH NORECOVERY) и последнюю резервную копию transaction log (используя WITH RECOVERY).
Следующие шаги описывают последовательность, подразумевающую добавление файла базы данных между резервированием записей transaction log на промышленной базе данных и заканчивающуюся ошибкой, которая происходит при попытке последующего восстановления из резервной копии, описанное выше.
ОБРАТИТЕ ВНИМАНИЕ: приводящиеся ниже шаги были применены к активно используемой базе данных.

1. Выполните резервное копирование базы данных SQL Server 7.0.
2. Создайте резервную копию transaction log в файл tran_log1.trn.
3. Добавьте файл к базе данных, используя представленный ниже скрипт:

ALTER DATABASE northwind
ADD FILE
( NAME = northwind_data1,
   FILENAME =E:\MSSQL7\DATA\northwnd_data1.ndf,
   SIZE = 5MB,
   MAXSIZE = 15,
   FILEGROWTH = 10%
)
GO

4. Выполните резервное копирование transaction log в файл tran_log2.trn.

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

1. Восстановите базу данных SQL Server 7.0, используя:

WITH NORECOVERY
Restore Successful

Processed 360 pages for database northwind, file Northwind on file 1. Processed 1 pages for database northwind, file Northwind_log on file 1. Backup or restore operation successfully processed 361 pages in 0.810 seconds (3.642 MB/sec)

2. Восстановите резервную копию transaction log, используя WITH NORECOVERY из tran_log1.trn:

Restore Successful

Processed 1 pages for database northwind, file Northwind_log on file 1. Backup or restore operation successfully processed 1 pages in 0.004 seconds (1.664 MB/sec).

3. Восстановите transaction log, используя WITH NORECOVERY из tran_log2.trn:

Операция восстановления потерпит неудачу с сообщением об ошибке:

Server: Msg 3155, Level 16, State 1, Line 1 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

Поскольку сообщение об ошибке указывает, что восстановление transaction log не может быть продолжено из заданной резервной копии transaction log (tran_log2.trn). Такое поведение соответствует дизайну SQL Server 7.0 и не позволяет добавлять или удалять файлы между резервными копиями журнала.
Чтобы предотвратить эту ошибку, создайте полную резервную копию после того, как Вы добавите или удалите один или более файлов базы данных, после чего начнётся новая последовательность резервирования записей журнала. Если последовательность резервирования, созданная предварительно, является единственно доступной для восстановления, восстановите tran_log1.trn, используя опцию WITH RECOVERY, которая позволит восстановить базу данных к не противоречивому состоянию непосредственно перед тем, как файлы базы данных были добавлены или удалены.

SQL Server 2000

Для SQL Server 2000, добавление или удаление файлов обрабатывается без необходимости создания новой последовательности резервирования. После добавления файла к базе данных в середину последовательности резервного копирования записей transaction log, следующее резервное копирование transaction log должно быть запущено вручную, с использованием опции WITH MOVE.

Рассмотрим сценарий, подобный используемому для SQL Server 7.0. После добавления файла к промышленной базе данных, выполните:

ALTER DATABASE Northwind
ADD FILE
(
   NAME = northwind_data1,
   FILENAME =E:\MSSQL7\DATA\northwnd_data1.ndf,
   SIZE = 5MB,
   MAXSIZE = 15,
   FILEGROWTH = 10%
)
GO

Если резервное копирование transaction log базы данных выполнено после добавления файла, попробуйте восстановить первую, после добавления файла, резервную копию transaction log (tran_log2.trn), что приведёт к следующему сообщению об ошибке:

Processed 1 pages for database northwind, file Northwind_log on file 1.
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The system cannot find
the path specified.) while attempting to open or create the physical file
E:\Program Files\Microsoft SQL Server\MSSQL\DATA\nwnd_d2.ndf.
Server: Msg 5183, Level 16, State 1, Line 1
File northwind_data2 cannot be created. Use WITH MOVE to specify a usable physical file name.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Сообщение об ошибке выдаётся потому, что команда RESTORE не запускалась с опцией, создающей новый файл (соответствующий файлу, добавляемому к промышленной базе данных) и позволяющей записать данные в файл.
Чтобы избегать появления сообщения об ошибке 5123, воспользуйтесь следующим сценарием поведения:

1. Вручную восстановите первую резервную копию transaction log, созданную после добавления файла (для SQL Server Query Analyzer или SQL Server Enterprise Manager) используйте следующий код:

RESTORE LOG NORTHWIND FROM
DISK=e:\filemgt\nwind_log2.trn
WITH MOVE northwind_data1
TO c:\Program Files\Microsoft SQL server\MSSQL$SQL2K1\Data\northwnd_data1.ndf,
NORECOVERY

Теперь Вы создали новый файл базы данных, такой же, как существует в промышленной базе данных сервера, на котором создавалась резервная копия transaction log.
После того, как Вы выполните команды из пункта 1, можно продолжить восстановление transaction log, причём, без использования опции MOVE.

SQL Server 2000 Log Shipping Сценарий

Если Ваш SQL Server 2000 использует Log Shipping, и если добавляется файл базы данных к промышленной базе и заданию на загрузку в резервный сервер (непрерывно выполняющемуся в заданных интервалах времени), выполнение загрузки на резервный сервер будет прервано сбоем с сообщением об ошибке 5105.
Для таблице log_shipping_plan_history на резервном сервере появится следующее сообщение об ошибке, добавляющееся всякий раз, когда будет выполняться задание на загрузку:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5105: [Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\data\northwnd_data2.NDF may be incorrect. [Microsoft][ODBC SQL Server Driver][SQL Server]File northwnd_data2 cannot be created. Use WITH MOVE to specify a usable physical file name. [Microsoft][ODBC SQL Server

Это сообщение об ошибках появляется потому, что не определено новое расположение файла в опции восстановления, а SQLMAINT не запрограммирован на определение нового расположения файла. Чтобы избежать появления этой ошибки на резервном сервере, для первого резервного копирования transaction log, созданного после добавления файла к базе данных SQL Server, в Query Analyzer, используйте опцию WITH MOVE и определите расположение, в котором добавленный файл должен быть создан на резервном сервере.
Например, если northwind _tlog_200101270220.trn - это первая резервная копия transaction log, созданного после добавления в базу данных northwind файла northwnd_data2, выполните следующую команду, которая позволит избежать ошибок при исполнении задания на загрузку:

restore log pubs2 from disk =C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\BACKUP\northwind_tlog_200101270219.TRN
with move northwnd_data2 to C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\data\northwnd_data2.ndf,
standby =c:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\Backup\Northwind.TUF

После того, как Вы выполните эту команду, дальнейшее восстановление transaction log будет проходить без сообщения об ошибке 5105. Вам, вероятно, придется сделать некоторые исправления в зависимости от установки load_all для Log Shipping пары.
Если столбец load_all для Log Shipping пары в таблице log_shipping_plan_databases установлен в 0, проверьте столбец last_loaded_file в той же самой таблице, и установите его на резервное копирование transaction log, которое было выполнено вручную. В предыдущем примере файл - northwind _tlog_200101270219.trn.
Если опция столбца load_all установлена в 1, Вы можете безопасно выполнять задание на загрузку в запланированные интервалы времени.

Дополнительную информацию можно получить в статье Microsoft Knowledge Base:
Q281122 INF: Restore File and Filegroup Backups in SQL Server