Use master sql что это

Transact-SQL — создание базы данных

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

Объектом базы данных, который требуется создать в первую очередь, является сама база данных. Компонент Database Engine управляет как системными, так и пользовательскими базами данных. Пользовательские базы данных могут создаваться авторизованными пользователями, тогда как системные базы данных создаются при установке СУБД.

Для создания базы данных используется два основных метода. В первом методе задействуется обозреватель объектов среды SQL Server Management Studio, как было показано ранее, а во втором применяется инструкция языка Transact-SQL CREATE DATABASE. Далее приводится общая форма этой инструкции, а затем подробно рассматриваются ее составляющие:

Параметр db_name — это имя базы данных. Имя базы данных может содержать максимум 128 символов. Одна система может управлять до 32 767 базами данных. Все базы данных хранятся в файлах, которые могут быть указаны явно администратором или предоставлены неявно системой. Если инструкция CREATE DATABASE содержит параметр ON, все файлы базы данных указываются явно.

Компонент Database Engine хранит файлы данных на диске. Каждый файл содержит данные одной базы данных. Эти файлы можно организовать в файловые группы. Файловые группы предоставляют возможность распределять данные по разным приводам дисков и выполнять резервное копирование и восстановление частей базы данных. Это полезная функциональность для очень больших баз данных.

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

Учетная запись компонента Database Engine, применяемая для создания базы данных, называется владельцем базы данных. База данных может иметь только одного владельца, который всегда соответствует учетной записи. Учетная запись, принадлежащая владельцу базы данных, имеет специальное имя dbo. Это имя всегда используется в отношении базы данных, которой владеет пользователь.

Опция LOG ON параметра dbo определяет один или более файлов в качестве физического хранилища журнала транзакций базы данных. Если опция LOG ON отсутствует, то журнал транзакций базы данных все равно будет создан, поскольку каждая база данных должна иметь, по крайней мере, один журнал транзакций. (Компонент Database Engine ведет учет всем изменениям, которые он выполняет с базой данных. Система сохраняет все эти записи, в особенности значения до и после транзакции, в одном или более файлов, которые называются журналами транзакций. Для каждой базы данных системы ведется ее собственный журнал транзакций.)

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

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

Компонент Database Engine создает новую базу данных по шаблону образцовой базы данных model. Свойства базы данных model можно настраивать для удовлетворения персональных концепций системного администратора. Если определенный объект базы данных должен присутствовать в каждой пользовательской базе данных, то этот объект следует сначала создать в базе данных model.

В примере ниже показан код для создания простой базы данных, без указания дополнительных подробностей. Чтобы исполнить этот код, введите его в редактор запросов среды Management Studio и нажмите клавишу <F5> .

Код, приведенный в примере, создает базу данных, которая называется SampleDb. Такая сокращенная форма инструкции CREATE DATABASE возможна благодаря тому, что почти все ее параметры имеют значения по умолчанию. По умолчанию система создает два файла. Файл данных имеет логическое имя SampleDb и исходный размер 2 Мбайта. А файл журнала транзакций имеет логическое имя SampleDb_log и исходный размер 1 Мбайт. (Значения размеров обоих файлов, а также другие свойства новой базы данных зависят от соответствующих спецификаций базы данных model.)

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

Созданная в примере база данных называется Projects. Поскольку опция PRIMARY не указана, то первичным файлом предполагается первый файл. Этот файл имеет логическое имя projects_dat и он сохраняется в дисковом файле projects.mdf. Исходный размер этого файла 10 Мбайт. При необходимости, система выделяет этому файлу дополнительное дисковое пространство в приращениях по 5 Мбайт. Если не указать опцию MAXSIZE или если этой опции присвоено значение UNLIMITED, то максимальный размер файла может увеличиваться и будет ограничиваться только размером всего дискового пространства. (Единицу размера файла можно указывать с помощью суффиксов KB, TB и MB, означающих килобайты, терабайты и мегабайты соответственно. По умолчанию используется единица размера MB, т.е. мегабайты.)

Кроме файла данных создается файл журнала транзакций, который имеет логическое имя projects_log и физическое имя projects.ldf. Все опции спецификации файла журнала транзакций имеют такие же имена и значения, как и соответствующие опции для спецификации файла данных.

В языке Transact-SQL можно указать конкретный контекст базы данных (т.е. какую базу данных использовать в качестве текущей) с помощью инструкции USE. (Альтернативный способ — выбрать имя требуемой базы данных в раскрывающемся списке Database (Базы данных) в панели инструментов среды SQL Server Management Studio.)

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

Создание моментального снимка базы данных

Кроме создания новой базы данных, инструкцию CREATE DATABASE можно применить для получения моментального снимка существующей базы данных (база данных-источник). Моментальный снимок базы данных является согласованной с точки зрения завершенных транзакций копией исходной базы данных на момент создания моментального снимка. Далее показан синтаксис инструкции для создания моментального снимка базы данных:

Таким образом, чтобы создать моментальный снимок базы данных, в инструкцию CREATE DATABASE нужно вставить предложение AS SNAPSHOT OF. В примере ниже иллюстрируется создание моментального снимка базы данных SampleDb и сохранения его в папке D:\temp. (Прежде чем выполнять этот пример, нужно создать данный каталог.)

Моментальный снимок существующей базы данных — это доступная только для чтения копия базы данных-источника, которая отражает состояние этой базы данных на момент копирования. (Таким образом, можно создавать множественные моментальные снимки существующей базы данных.) Файл моментального снимка (в примере выше это файл D:\temp\snapshot_DB.mdf) содержит только измененные данные базы данных-источника. Поэтому в коде для создания моментального снимка необходимо указывать логическое имя каждого файла данных базы данных-источника, а также соответствующие физические имена.

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

Моментальные снимки баз данных можно создавать только на дисках с файловой системой NTFS (New Technology File System — файловая система новой технологии), т.к. только эта файловая система поддерживает технологию разреженных файлов, применяемую для хранения моментальных снимков.

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

Присоединение и отсоединение баз данных

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

Для отсоединения базы данных от сервера баз используется системная процедура sp_detach_db. (Отсоединяемая база данных должна находиться в однопользовательском режиме.)

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

Why use master to create a database?

It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you’re creating a database called Sales , and you arelady have a database called Sales , you’ll need to change your database context before you:

Restore with replace; or,
Drop the current database and then:
- Create from scratch; or,
- Create for attach.
There are plenty of other scenarios outside of database creation that also require either (a) not being in the context of the current database, or (b) being in the context of master specifically (or at least not a specific database), and many of these things you may be doing during or around creating databases:
- Setting a database to a different state, like single_user
- Preventing errors when a script has a USE command but that user database may be offline or otherwise inaccessible
- Granting server-level permissions like CREATE DATABASE
- Granting server-level role membership
- Marking a module as a system object ( sp_MS_marksystemobject ) or as a startup procedure
- Certain types of certificate, server audit, and Availability Group operations
Probably a slew of other things. USE master; isn’t always necessary, but sometimes it is, and it doesn’t hurt to always execute server-level commands from that database.

Create Database in SQL Server 2019

In SQL Server, a database is made up of a collection of objects like tables, functions, stored procedures, views etc. Each instance of SQL Server can have one or more databases. SQL Server databases are stored in the file system as files. A login is used to gain access to a SQL Server instance and a database user is used to access a database. SQL Server Management Studio is widely used to work with a SQL Server database.

Type of Database in SQL Server

There are two types of databases in SQL Server: System Database and User Database.

System databases are created automatically when SQL Server is installed. They are used by SSMS and other SQL Server APIs and tools, so it is not recommended to modify the system databases manually. The followings are the system databases:
- master: master database stores all system level information for an instance of SQL Server. It includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
- model: model database is used as a template for all databases created on the instance of SQL Server
- msdb: msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
- tempdb: tempdb database is used to hold temporary objects, intermediate result sets, and internal objects that the database engine creates.
User-defined Databases are created by the database user using T-SQL or SSMS for your application data. A maximum of 32767 databases can be created in an SQL Server instance.

There are two ways to create a new user database in SQL Server:
1. Create Database Using T-SQL
2. Create Database using SQL Server Management Studio
Create Database using T-SQL Script

You can execute the SQL script in the query editor using Master database.

The following creates ‘HR’ database.

The Following create ‘HR’ database with data and log files.

Make sure that the data and log file path exist before executing the above SQL script.

Now, open SSMS and refresh the databases folder and you will see ‘HR’ database is listed.

Create Database in SQL Server

Create Database using SQL Server Management Studio

Open SSMS and in Object Explorer, connect to the SQL Server instance. Expand the database server instance where you want to create a database.

Right-click on Databases folder and click on New Database.. menu option.

Create Database

In New Database window, enter a name for the new database, as shown below. Let us enter the database name ‘HR’.

Create Database

The Owner of the database can be left at default or to change the owner, click on […] button.

Under the Database files grid, you can change the default values for the database and the log files. Every SQL Server database has at-least a minimum of two operating system files: Data file and Log file.
- Data Files contain data and objects like tables, views, stored procedures, indexes etc.
- Log files contain the information required to recover all transactions in a database. There must be at-lease one log file for each database. Learn more about Database Files and Filegroups
Make it as large as possible based on the maximum amount of data you expect.

To change database options, select the Options page. You can change the Collation, Recovery model under this tab, as shown below.

Database Options

Collation specifies the bit patterns that represent each character in a dataset. SQL Server supports storing objects having different collations in a single database.

Recovery model is a database property that controls how transactions are logged. There are three options under Recovery models: Simple, Full & Bulk-logged. Typically a database uses a Full recovery model.

Compatibility Level lists SQL Server 2008, 2012, 2014, 2016, 2017 & 2019. The latest version installed i.e., SQL Server 2019 is selected by default.

Containment type has two options: None and Partial. None is selected by default.

Now, select Filegroups tab. Filegroups are the physical files on your disc, where SQL server data is stored. By default a Primary data file is created while creating a new database. Learn more about Files and Filegroups.

Filegroups

Click Ok to create a new ‘HR’ database. This will be listed in the database folder, as shown below.

Create Database in SQL Server

In the above figure, the new ‘HR’ database is created with the following folders:

Database Diagrams: It graphically shows the structure of the database. You can create a new database diagrams by right-clicking on the folder and selecting Create New Diagram

Tables: All the system and user defined tables associated with the database are available under this folder. Tables contain all the data in a database.

Views: All the System and used defined views are available under this folder. System views are views that contain internal information about the database.

External Resources: Any Service, computer, fileshare, etc that are not a part of the SQL Server installation are stored here. Contains 2 folders 1) External Data Sources 2) External File Formats

Programmability: The Programmability folder lists all the Stored Procedures, Functions, Database Triggers, Assemblies, Rules, Types, Defaults, Sequences of the database

Service Broker: All database Services are stored in this folder

Storage: Stores information on Partition Schemes, Partition Functions, Full Text Catalogs,

Security: Database Users, Roles, Schemas, Asymmetric Keys, Certificates, Symmetric Keys, Security policies are created & available in the Security folder of every database.

Thus, you can create a new database in SQL Server using T-SQL script or SSMS.

SQL Server system databases – the master database

There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:
- master
- model
- msdb
- tempdb
This is my second article about SQL Server system databases.

The first one was about the tempdb database. In this article I will focus on the master database.

Master database usage in SQL Server

SQL Server uses the master database to record all information about the SQL Server instance system, like login accounts, endpoints, linked servers and configuration settings.

The information that a SQL Server instance needs is stored in the master database, like the information about all existing databases and the location of their data and transaction log files. If the master database does not exist or cannot be read then the SQL Server instance cannot start.

Even it is possible to create user objects in master database, it is not recommended to do so. The master database should stay as static as possible. For example, in the case that master database being rebuilt, all user objects will be lost.

Operations

Permissions

By default all users that have access to the SQL Server instance are granted to perform SELECT operations in the master database in the behalf of the public database role. The SELECT permission can be denied for any user as it is for a regular database or even, the public database role can be revoked to control which users may query the metadata from the master database.

Backups

Usually changes in the master database only occurs when there are changes in system objects like add/changing/deleting logins, endpoints or linked servers. A change in the master database can also be caused by changes in the SQL Server instance configuration or a SQL Server patch has been applied. When any of these changes occurs, it is also recommended to perform a backup of the master database.

It is recommended to have regular backups of the master database since it will be very useful when the master database becomes unusable.

Moving the master database file locations

As any regular database, master data and log files can be moved to another location if needed. But as opposed to the regular databases, to move master database file locations you will need to use the SQL Server Configuration Manager (SSCM).

To do this, open SSCM, go to SQL Server Services in the left panel. The existence services will appear listed in the right panel. Right click in the SQL Server service from the instance that you want to move the files and choose Properties. The correspondent Properties window will pop-up, then go to Startup Parameters tab where you will see the existing parameters for the actual path for the master database files.
- -d is the parameter for the data file location
- -l is the parameter for the log file location
Select the one that you want to change and type the new file location and click in the Update button to save the changes made. Repeat the process for all files that you want to move. When done click on the OK button to return to the SQL Server Services pane.

Stop the respective SQL Server instance service and copy the master database files to the new location. Now when starting the SQL Server instance, the master database files will run from the new location.

Assure that the SQL Server service account has full control permissions in the new location path, otherwise the following error will occur when attempting to start the SQL Server service:

It can be confirmed by checking in the SQL Server log for the reason. A similar access denied error should been logged:

2017-06-27 08:31:33.64 spid8s Starting up database ‘master’.
2017-06-27 08:31:33.64 spid8s Error: 17204, Severity: 16, State: 1.
2017-06-27 08:31:33.64 spid8s FCB::Open failed: Could not open file C:\temp\master\master.mdf for file number 1. OS error: 5(Access is denied.).
2017-06-27 08:31:33.64 spid8s Error: 5120, Severity: 16, State: 101.
2017-06-27 08:31:33.64 spid8s Unable to open the physical file “C:\temp\master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2017-06-27 08:31:33.64 spid8s Error: 17204, Severity: 16, State: 1.
2017-06-27 08:31:33.64 spid8s FCB::Open failed: Could not open file C:\temp\master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2017-06-27 08:31:33.64 spid8s Error: 5120, Severity: 16, State: 101.
2017-06-27 08:31:33.64 spid8s Unable to open the physical file “C:\temp\master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2017-06-27 08:31:33.64 spid8s SQL Server shutdown has been initiated

After confirming that SQL Server instance is up and running properly you may delete the old master data and log files from the old location.

Stored procedures

As a regular database, the master database has its own system stored procedures and allows user stored procedures to be created and stored as well.

It has the particular ability feature of a special stored procedure that can execute any other stored procedure whenever the SQL Server instance starts. This automation can be configured with the SP_PROCOPTION, available only for members of the sysadmin server role.

Another particularity is that Extended Stored Procedures can only be defined in the master database.

Restrictions

The master database has many restrictions and some are explained below.

Drop database

The master database cannot be deleted. If you try to delete it you will receive an error

saying that a system database cannot be dropped:

Set offline

The master database cannot be set to offline. If you try to do it you will receive the respective error:

Database rename

It is not possible to rename the master database. If you try to do it you will receive the respective error:

Change database owner

It is not possible to change the owner of the master database. If you try to do it you will receive the respective error:

Change Data Capture (CDC)

It is not possible to enable the Change Data Capture feature on the master database. If you try to do it you will receive the error stating that system databases do not support CDC:

Other restrictions

There are some more restrictions that are good to be known:
- The master database and its primary filegroup cannot be set to READ_ONLY status;
- The master database does not allow for adding more filegroups for the database nor rename the primary filegroup;
- The primary filegroup, primary data file, primary log file and the guest user of master cannot be deleted;
- The default collation for the master database is the SQL Server instance collation and cannot be changed without rebuilding the SQL Server instance itself;
- The master database cannot be part of a database mirroring solution;
- Triggers cannot be created on the master database system tables;
- A full-text catalog and full-text index cannot be created on the master database.
Other articles in this series:

References

Vitor Montalvão is a senior SQL Server Engineer with more than 20 years of experience working with SQL Server.

He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.

Читать:
Как скачать репозиторий с гитхаба