SQL-Ex blog

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

Факт 1 - начальный размер файлов новой базы данных наследуется из базы данных model

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

В SQL Server Management Studio (SSMS) щелкнем правой кнопкой на базе данных model и выберем "свойства".



На странице "Файлы" показана информация, относящаяся к файлам базы данных model.



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



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



Итак, файлы новой базы данных будут иметь размеры и авторост по умолчанию из базы данных model.

Факт 2 - модель восстановления новой базы данных является той же, что и модель восстановления базы данных model

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



Мы изменим модель восстановления с Full на Bulk-logged (с неполным протоколированием). Также изменим уровень совместимости и опцию Auto Shrink (автоматическое сжатие). Сохраним изменения, нажав кнопку ОК.



Теперь, если открыть окно создания новой базы данных, мы увидим, что все эти изменения применяются к новой базе данных.



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

Факт 3 - объекты базы данных, созданные в базе данных model, будут существовать во вновь созданной базе данных

Если мы создаем объекты в базе данных model, эти объекты появятся в любой новой базе данных этого экземпляра. Давайте проверим это. В SSMS выполним следующий код для создания таблицы в базе данных model.

USE model

GO

CREATE TABLE TestTable(ID INT, Val INT)

GO

INSERT INTO TestTable(ID, Val) 

VALUES(1,100)

GO

В коде ниже мы создаем хранимую процедуру в базе model.

USE model

GO

CREATE PROCEDURE getTestTableData

AS

BEGIN

   SELECT * FROM TestTable

END

Если мы раскроем ветку "Таблицы" и "Хранимые процедуры" в SSMS, то увидим созданные объекты.



Теперь, если мы создадим новую базу данных (NewDB2), то увидим, что эти объекты также созданы в этой новой базе данных.



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

USE [NewDB2]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[getTestTableData]

SELECT 'Return Value' = @return_value

GO

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

Факт 4 - объекты, созданные в базе данных model, также появляются в TempDB

Не только новые базы данных наследуют объекты, созданные в базе данных model, но они также существуют в TempDB при её создании.

Сначала мы видим, что в нашем экземпляре нет пользовательских таблиц и хранимых процедур в TempDB.



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



Это объясняется тем фактом, что при перезапуске экземпляра SQL Server, база данных TempDB создается заново. Следовательно, т.к. база создается заново, она наследует объекты, созданные в базе данных model.

Факт 5 - база данных model всегда должна существовать в SQL Server

Невозможно запустить экземпляр SQL Server, если база данных model недоступна. Это происходит потому, что при старте экземпляра воссоздается база данных TempDB, а для этого необходима база данных model.

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



Если посмотреть в файл ERRORLOG, мы сможем увидеть причину, по которой произошел сбой запуска SQL Server - недоступность базы данных model.



Следовательно, наличие базы данных model является обязательным для каждого экземпляра SQL Server.

Заключение

Суммируя сказанное, база данных model используется как шаблон для создания новых баз данных в экземпляре. Это означает, что вновь созданные базы данных в экземпляре наследуют опции база данных model, модель восстановления, начальный размер файлов данных и т.д. Возможно также создать объекты базы данных, например, таблицы в базе данных model, и эти объекты появятся в любой новой базе данных, которая создается в экземпляре. База данных TempDB также наследует некоторые установки базы данных model и, следовательно, база данных model должна существовать при запуске SQL Server.