(2.1) Conceptual operation of backing up and restoring--sql server files

Overview: SQL Server stores data and logs as files

1. Data files

SQL Server data files fall into 2 categories

(1) Main database file

The primary database file contains startup information for the database, system objects, and other files that point to the database (from the data file), and the master data file can also store user data and objects. Each database must have only one master data file, followed by an. mdf suffix.

(2) from the data file

User-defined, user-Stored User data and objects, followed by the name. ndf.

(3) Concept of filegroups

Divided into 2 categories

"1" PRIMARY filegroup

The name of the primary filegroup is primary, which contains the primary file, and any minor files that are not placed in the user-defined filegroup, and all system tables are assigned to the primary filegroup, and the default is placed in the primary filegroup if no filegroup is specified.

The default filegroup can be set to a filegroup other than primary by the ALTER DATABASE statement, but at any time a database can have only one default filegroup.

"2" user-defined file group

User-defined filegroups are created by the user when the database is first created, or after the database is created, by modifying the database.

When data is read and stored in a SQL Server database, it is not in the data file unit, but in the filegroup. Each object in the database is stored on a filegroup (not on a database file), and each filegroup contains at least one data file.

The advantage of using filegroups instead of data files is that you can simplify management, increase the number of manageable data files, logically manage the division and use of database files, and, on the other hand, break down some of the system's limitations, such as: two disks that can be used to store data, but they are not large enough to store the entire database. You can create one data file on each of the two disks and add it to the same filegroup, so that you can use 2 disk space at a time.

Benefits of using multiple files

In most cases, a small database does not need to create multiple files to hold the data. But as the data grows, the drawbacks of individual files arise.

• First, the use of multiple files to distribute to different disk partitions (multiple hard disks) can improve IO performance in several major ways.
• Second, multiple files are convenient for backup and recovery for databases with more data.
• However, multiple files require more disk space, because each file has its own set of B-tree organization and its own growth space. Of course there are fragments of their own.
• Overall, the advantages of multiple files are far greater than the drawbacks.

Example Demo

How do I create multiple files and filegroups?

--1. Creating a file and filegroup in the same way you create a database
--Create A new databasecreate DATABASE multiplefilegroups on PRIMARY (--PRIMARY File Group NAME = ' Multiplefilegro UPS ', FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroups.mdf ', SIZE = 5MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB),--secondary Fi Le Groupfilegroup FileGroup1 (--1st file in the first secondary file Group NAME = ' MultipleFileGroups1 ', Filenam E = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroups1.ndf ', SIZE = 1MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB), (--2nd fi Le in the first secondary File Group NAME = ' MultipleFileGroups2 ', FILENAME = ' C:\Program Files\Microsoft SQL Server \mssql11. Sql2012\mssql\data\multiplefilegroups2.ndf ', SIZE = 1MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB) LOG on (--L og File NAME = ' Multiplefilegroups_log ', FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroupS.ldf ', SIZE = 5MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB) GO 

--2. Creating a filegroup ALTER DATABASE [TEST] add FILEGROUP [fg_test_id_01]  ALTER DATABASE [test] add FILEGROUP [fg_test_id_02]

--3. create file ALTER DATABASE [Test] ADD file (NAME = n ' fg_testunique_id_01_data ', FILENAME = N ' E:\FG_TestUnique_Id_01_data.ndf ', SIZE = 1MB, filegrowth = 1MB) to FILEGROUP [fg_test_id_01];  ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_id_02_data ', FILENAME = N ' E:\FG_TestUnique_Id_02_data.ndf ', SIZE = 1 MB, filegrowth = 1MB) to FILEGROUP [fg_test_id_02];

How do i put a table or index into a specific filegroup? How to set up filegroups

CREATE TABLE Customers (   FirstName char () NOT NULL,   LastName char (a) not NULL,   Address CHAR (+) not null,< C7/>zipcode CHAR (5) Not NULL,   Rating INT not NULL,   ModifiedDate DATETIME not NULL,) on [Filegroup1]go

How do I mark a specific filegroup as the default filegroup?

--FileGroup1 Gets the default filegroup, where new database objects--'ll be createdalter database multiplefilegroups MO Dify FILEGROUP FileGroup1 Defaultgo

This is the method I usually recommend because you don't need to think about it after you've created your database object. So now let's create a new table, which is automatically stored in the FILEGROUP1 filegroup.

--the table would be created in the file group "FileGroup1" CREATE table Test (    Filler CHAR (8000)) GO

Now let's do a simple test: We insert 40,000 records into the table. 8K size per record. So we inserted 320MB data into the table. This is the polling scheduling assignment algorithm that I just mentioned, and it will do the following: SQL Server distributes data between 2 files: The first file has 160M of data, and the second file has 160M of data.

--Insert 40.000 Records, results in about 312MB data (40.000 x 8kb/1024 = 312,5MB)--they is distributed in a Round-ro Bin fashion between the files in the file group "FileGroup1" – each file would get about 160MBDECLARE @i INT = 1WHILE (@i & lt;= 40000) BEGIN    INSERT into Test VALUES    (        REPLICATE (' x ', 8000)    )        SET @i + = 1ENDGO

Next you can look at the hard drive and you will see 2 files in the same size.

When you put these files on a different physical hard disk, you can access them at the same time. That's the power of having multiple files in a filegroup.

You can also use the following script to get information about a database file.

--Retrieve file statistics information about the created database Filesdeclare @dbId intselect @dbId = database_id from S ys.databases WHERE name = ' multiplefilegroups ' SELECT     sys.database_files.type_desc,     sys.database_ Files.physical_name,    sys.dm_io_virtual_file_stats.* from Sys.dm_io_virtual_file_stats (    @dbId,    NULL) INNER JOIN sys.database_files on sys.database_files.file_id = Sys.dm_io_virtual_file_stats.file_idGO

Reference article:

https://www.sqlpassion.at/archive/2016/08/29/files-and-file-groups-in-sql-server/

SQL Server moves the table data to the new file/filegroup:(1) 72472437

(2) https://www.cnblogs.com/lyhabc/p/3480917.html (fine recommended)

(2.1) Conceptual operation of backing up and restoring--sql server files