СОВЕТ

Добавление и удаление файла базы данных между резервированиями журнала
По материалам статьи Microsofr: «INF: Effect of Database File Additions or Deletions on Database Recovery»
Информация в этой статье относится к Microsoft SQL Server Версий 7.0/2000 (все издания)

Добавление файлов базы данных 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

ГОТОВИМСЯ К ТЕСТУ ПО 70-028

ШПАРГАЛКА #9 Продолжение (обзор официального курса Microsoft)
Архив шпаргалок Вы найдёте на следующих сайтах:
http://www.sql.ru/subscribe/
http://subscribe.ru/archive/comp.soft.winsoft.sqlhelpyouself

Информация из чёрного ящика MS SQL Server

Как в самолёте, у SQL сервера есть "чёрный ящик" (BlackBox.trc). В этом BlackBox динамически хранится и обновляется информация о ста последних событиях сервера баз данных, которые записываются и хранятся в т.н. буфере истории запросов. Если ваш сервер сбойнёт (произойдёт исключительная ситуаций не ниже 17-го уровня), будет предпринята попытка записи содержимого буфера запросов в специальный файл трассировки BlackBox.trc, который, как и все аналогичные файлы, можно будет просмотреть в Profiler, даже если сервер на запущен или когда работоспособность сервера будет восстановлена. Хотя сбор данных для истории запросов практически не вносит дополнительной нагрузки в работу сервера, эта опция, по умолчанию, отключена. Включение/отключение её можно осуществить с помощью расширенной хранимой процедуры xp_trace_setqueryhistory, которая имеет следующий синтаксис:

EXECUTE xp_trace_setqueryhistory (0|1)

Другая расширенная процедура поможет Вам вручную записать последние 100 запросов в указанный файл. Вот её синтаксис:

EXECUTE xp_trace_flushqueryhistory ИМЯ_ФАЙЛА

Есть ещё одна специальная программа sqldiag, запуск которой при включённой истории запросов записывает буфер в файл \MSSQL7\LOG\Sqldiag.trc
Чёрный ящик будет содержать имя сервера, события (включая их классы) и сообщения об ошибках ими вызванных, текст запросов T-SQL или приложений, пользователей, SQL:BatchStarting, RPC:Starting, Exception и все поля данных программы SQL Server Profiler.
Воспользовавшись содержимым чёрного ящика, можно последовательно отследить всё, что непосредственно предшествовало сбою.

Продолжение следует.

ПОЛЕЗНОСТИ

Сравнение Borland InterBase 4.x, Sybase SQL Server и Microsoft SQL Server
Автор перевода: Кузьменко Дмитрий.
Motorola, Nokia, MCI, Northern Telecom, Philadelphia Stock Exchange, Bear Stearns, First National Bank of Chicago, the Money Store, the US Army, NASA, Boeing. Все эти компании, независимо от направления бизнеса, имеют одно общее: они выбрали InterBase в качестве ключевого компонента их информационных систем. Borland InterBase одинаково хорошо применяется и для "домашнего" управления ракетными системами, сбора данных для аэрокосмических исследований или хранения и обработки данных биржи. Приложен...
[подробнее] [Обсудить статью в форуме]
Динамический SQL в программах на PL/SQL
Автор: Стив Бобровски.
Администраторы и разработчики часто пренебрегают теми преимуществами, которые можно получить от применения некоторых выдающихся, но скрытых драгоценных возможностей, которыми обладает сервер Oracle7. Я прежде всего имею в виду пакеты утилит DBMS, которые позволяют разработчикам приложений применять расширенные средства сервера базы данных, такие как сигналы (alerts), коммуникационные каналы (communication pipes) и управляемые сервером блокировки ресурсов (server-managed resource locking). В Orac...
[подробнее] [Обсудить статью в форуме]
Поиск в базе данных
Автор: www.sources.ru.
Самая распространённая задача, которую решают приложения работающие с базами данных - это поиск необходимых записей по заданному критерию. В Delphi, компоненты ADOExpress включают в себя методы поиска записей, аналогичные тем, которые используются в BDE...
[подробнее] [Обсудить статью в форуме]

НОВОСТИ ПРОЕКТА SQL.RU

Скромно извещаем наших подписчиков, что с 4 июня 2001г. настоящая рассылка перешла в категорию ЗОЛОТЫХ. Выражаем огромную признательность за то, что это стало возможно всему замечательному коллективу проекта SUBSCRIBE.RU

Новые технические статьи Microsoft

Q300258 - FIX: INSERT EXEC of Extended Stored Procedure Incorrectly Inserts Empty String Instead of NULL>
Microsoft SQL Server 7.0 Conversion Guide
Q110139 - INF: Causes of SQL Transaction Log Filling Up
Q114760 - INF: How to Obtain SQL Server 4.21a Service Pack 4
Q154548 - How to Obtain SQL Server v6.0 Service Pack 3
Q159671 - INF: How to Obtain French SQL Server 6.5 Service Pack 2
Q173907 - INF: How to Set the Day/Month/Year Date Format in SQL Server
Q240872 - INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers
Q279489 - INF: How to Connect to Analysis Server 2000 By Using HTTP Connection
Q282749 - BUG: Deadlock Information Reported with SQL Server 2000 Profiler Is Incorrect
Q294753 - BUG: DOC: SQL Server 7.0 Readme Files Do Not Document Installation Warning
Q295732 - INF: Creating Databases or Changing Disk File Locations on a Shared Cluster Drive on Which SQL Server 2000 was not Originally Installed
Q295939 - BUG: sp_OAMethod May Cause Access Violation When @@servername Is NULL
Q296197 - BUG: Documentation of Ownership Chain in BOL May Be Misleading
Q298093 - BUG: BOL Incorrectly Demonstrates Usage of sp_change_primary_role with @job_id Parameter
Q296769 - BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing Linked Server Objects
Q299423 - FIX: XML for SQL Server 2000 Web Release 1 Hotfix Rollup
Q299926 - INF: Writebacks That Use Multi-Hierarchies Must Specify All Hierarchies
Q300516 - INF: Readme.txt File for SQL Server 2000 Service Pack 1
Q290212 - INF: List of Bugs Fixed in SQL Server 2000 Service Pack 1 (1 of 2)
Q290211 - INF: How to Obtain the Latest SQL Server 2000 Service Pack
Q298643 - FIX: Poor Cardinality Estimate for Tables with Multiple Qualifying Histogram Values
Q300538 - INF: List of Bugs Fixed in SQL Server 2000 Service Pack 1 (2 of 2)
Q278443 - FIX: DTS Import Wizard Closes When You Browse for SQL Server Servers
Q160725 - How to Obtain Embedded SQL for C Version 6.5 Patch
Q178444 - SQL Server Utility Files Available
Q221465 - INF: Using the WITH MOVE Option with the RESTORE Statement
Q224071 - INF: Moving SQL Server 7.0 Databases to a New Location
Q237980 - INF: How to Convert an Access Database to SQL Server
Q256052 - FIX: Sqlservr.exe Non-Trusted Connection Through OPENROWSET Allows Access to Service Account
Q263968 - FIX: Service Pack Installation May Save Standard Security Password in File
Q264880 - FIX: Passwords May Be Retrieved from Enterprise Manager and from a DTS Package with No Owner Password
Q266766 - FIX: Temporary Stored Procedures in SA Owned Databases May Bypass Permission Checks When You Run Stored Procedures
Q274463 - INF: Understanding and Troubleshooting the Copy Database Wizard in SQL Server 2000
Q274797 - INF: SQL Server 7.0 Service Pack 3 Fixlist
Q284228 - INF: New Trace Flag to Enhance Stored Procedure Replication
Q293164 - PRB: Errors May Occur When You Use SQL Server 2000 to Save a DTS Package as a Visual Basic File
Q294350 - BUG: User Databases Do Not Contain INFORMATION_SCHEMA Views in SQL Server 2000
Q297105 - FIX: Potential English Query Security Issue
Q299950 - FIX: Bad Cardinality Estimate Results in Sub Optimal Join Strategy
Q300447 - FIX: Snapshot Delivery Failure During BCP IN not Recognized by Merge Agent

ФОРУМ SQL.RU: ДЮЖИНА САМЫХ ПОПУЛЯРНЫХ ТОПИКОВ НЕДЕЛИ

BEGIN TRANSACTION .... ROLLBACK TRANSACTION ?
Какая разница ?
Sleeping
Дилемма: разные базы данных
English language exceptions
Надо вот такой вопрос
Оптимизированный доступ в MS SQL?
Репликация. Update.
Вопрос о "сетке"
Как в триггере работать с временными таблицами
Задачка
EXEC (TSQL-script) и переменные

ФОРУМ SQL.RU: ВОПРОСЫ ОСТАЛИСЬ БЕЗ ОТВЕТА

Не могу удалить процесс
Журнал транзакций в SQL 6.5
Интеграция между MS SQL и Interbase
Проблема с восстановлением ADODB.Recordset из формата ADTG
Хранение документов WORD (EXCEL) в поле IMAGE в MS SQL Server 2000.
Merge-репликации. Извещения о конфликтах в MS SQL Server 2000.
Репликация с Access
Distributed transaction теряется...
UPDATE table INSERTED!!!!! Ну, очень нужно. Народ помогите.

#050<<  #051