Database Filegroup(s) and Piecemeal Restores in MS SQL Server

Introduction:

Since SQL Server preserves data in a version, those facts need to move somewhere, but where does it shift? In which Filegroups come into play in SQL Server. These are the substantial files on your disc volumes that hold data and can be used for backup and administrative control purposes. An indispensable thing that comes along the way of the database is its security and protection that includes the restoration of the database too. Piecemeal restore process comprises a set of restore step sequences, initiating with the primary and, at least, one secondary read-write filegroups followed by read-only filegroups.

Summary:

The first component to recognize is what types of files SQL Server makes use of:

The Primary Data File is the startup data file holding data in SQL Server and signals to other files present in your database. They have a .mdf extension on them.

Secondary Data Files hold all data that do not relate to the Primary Data File. They only hold user data, and so are user-defined Secondary Files are optional. Moreover, a secondary data file can be used to continue growing the size of the database if it rises above the limited capacity of a single windows file. The recommended extension for these files is a .ndf extension.

Transaction Log Files are the data files containing all logging actions available in the SQL server. Each database must contain at least one logging file. The recommended extension for these files is a .ldf extension.

All data files are preserved in the filegroups cataloged in the following table:

Every database possesses a primary filegroup. This filegroup has the primary data file and any secondary files that are not part of any other filegroups. User-defined filegroups can be created to categorize data files together for administrative, data allocation, and placement means. For instance, let there are three files named file1.ndf, file2.ndf, and file3.ndf, built on three disk drives, respectively, and appointed to the filegroup named fgroup1. A table can then be created explicitly on the filegroup i.e. fgroup1. The three disks will advance queries for data from the table; this will enhance performance. The same performance enhancement can be achieved by using only one file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups enable you to add new data to new discs easily.

Rules to be considered while designing Filegroups:

• A filegroup can be used by only one database. For example, file data.mdf and data.ndf, which has data and objects from the sales database, can be used by only one database.
• A file can be associated with only one filegroup.
• Transaction log files can never be a part of any filegroups. Transaction log files can never be a part of any filegroups.

Sometimes, a database restore from the backup is desired. As we know, we do have the choice to restore needed file groups, but not all of the filegroups are needed to make the database online at a specific time. It is always enforced to recover the primary filegroup, but any secondary user-defined file groups are optional, at that instance, while doing the restoration. We can obtain partial data as it is available online after restoring it. We have to wait for the rest of the data to recover other filegroups.

The command RESTORE DATABASE with PARTIAL clause initiates a new piecemeal restore operation. The keyword PARTIAL points that the restore process includes a partial recovery. The partial keyword indicates and starts the partial-restore sequence. This will be validated and authorized during the restoration stages. As the database recovery has been delayed or postponed, so the position of the database remains pending.

To test the concept of piecemeal process, do follow the following steps:

1. Add another filegroup to preserve the static data. Add a table to the filegroup and insert a few records.
2. Query the table to validate or authorize the data
3. To change the filegroup state to read-only, use the “alter” database command and backup the database.
4. The database has three filegroups, one is read-only, and the other two are in read-write mode. Verify the filegroup status by executing T-SQL and backup the read-only database.
5. Drop the database to mimic the piecemeal recovery process of the database.
6. Perform the database restore operation before you start changing the session context to the master database. Now, we are going to do the read-write filegroups restore using the READ_WRITE_FILEGROUPS clause.
7. Next, restore the read-only filegroups and verify the output by querying the read-only table data.

Conclusion:

Preserving data file groups and database-recovery and healing processes to the requirements of the organization is crucial. For instance, let our SQL Server crashes then we are unable to bring this server up, now we need to recover databases on a separate server and bring them online. The database is enormous, and so recovery might take a few hours to finish. Here the Piecemeal database works. In this technique, we can recover the database on a filegroup level and can minimize the downtime outstandingly.