In this article we take a close look at the structure of files and filegroups present in SQL Server
SQL Server makes use of binary files and not textual files and it consists of two types of files Log files and Data files; these can further be grouped into Filegroups for the purpose of allocation and administration. Data files understandably consist of data and objects like tables, indexes, views etc. Log files on the other hand keep a record of all transactions and also stores information that would be needed to restore transactions in databases. These were the operating system files; the SQL Server database further consists of three types of files:
- Primary data files: This includes the startup information for the given database and also denotes other files of the database. This can be used for storing data and objects, secondary data files can also be used for this purpose. There is only one primary data file in all databases, with file name extension- .mdf.
- Secondary data files: These are used for storing data and are user defined and optional. This also helps in spreading data on multiple disks by using a different disk for storing each file. It allows a database to grow even when it fills the maximum size allowed for a single Windows file. Its file name extension is- .ndf.
- Transaction Log: This stores the information required for database recovery and also gives notifications in-case of any modification. Every database must have one log file. Its file name extension is- .ldf.
For example a database called Sales can be created, including a primary data file containing data and objects, and a log file containing transaction log information. Similarly more databases can be created, which are more complex, with one primary and multiple secondary files and transaction log files.
Usually in single – disk systems, transaction log and data is stored on the same disk, but it is suggested to store them on different disk and opt for a multiple disk system.
There is a primary filegroup in every database, which consist of all primary and secondary data files which are not put in any other filegroup. Filegroups are nothing but groups of files put together by the user for the purpose of administration, placement and data collection. There are two types of file groups, which are:
- User defined: These are the ones which are mentioned above. It is created by the database user at the time of creating or modifying a database
- Primary file group: These are the file groups containing primary files and they are allocated all the system tables.
All the created objects, which have not been assigned any of the existing filegroups, will become a part of the default filegroup. At any time, there is one filegroup which will be designated the default filegroup. The default filegroup must be large enough to accommodate all those objects which are not a part of any filegroup. The Primary filegroup will remain default filegroup unless altered by using ‘Alter Database’ command.
Data stored in a SQL database needs to be protected during contingencies
In situations like a database crash it becomes acutely important to recover the data stored in the SQL Server database. While your standard recovery processes would be able to bring back a good amount of data, a certain set of data, especially the ones stored since the last backup can be at risk. To recover such data you can use a sql recovery tool like DataNumen SQL Recovery. This powerful software is designed to bring back every single piece of record stored in the database in perfect shape. Moreover the application can be used to restore accidentally deleted records too.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/