Sql-server – Piecemeal restore, filegroup and file extension

restoresql server

As per my understanding (so far) any given user database contains three types of files – mdf, ndf and ldf.

MDF stands for meta data file and stores data definition as well as data (in case of small db and no other file exists for data).

NDF is basically for table data and stores data, we can dictate which table can be allocated here based on file groups.

LDF stands for log data file. This stores VLFs (Virtual Log Files)

I was under the impression that there can be only one .mdf file belonging to primary file group and n number of .ndf files belonging to primary or user defined file group.

I was creating a database and created multiple file group and extension left .mdf (by mistake) for all of them and to my surprise, now my database has three .mdf files belonging to different file group as shown below:

CREATE DATABASE [TEST_DBASE]
ON  PRIMARY 
( NAME = N'TEST_DBASE', FILENAME = N'E:\DB\TEST_DBASE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [ACCNTS_FILEGROUP] 
( NAME = N'ACCNTS_FILE', FILENAME = N'E:\DB\ACCNTS_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [LOAN_FILEGROUP] 
( NAME = N'LOAN_FILE', FILENAME = N'E:\DB\LOAN_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

In this case, how shall we do piecemeal restore if we need to bring only Accounts table online?

Earlier we were following below steps:

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'PRIMARY' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY, PARTIAL

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'ACCNTS_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with RECOVERY

Above steps were making Accounts table online and accessible.

To bring Loans tables online at a later point of time (non-critical table), we used to perform below steps:

BACKUP LOG [BANK_DATABASE] TO DISK = 'E:\BACKUPS\Backup_tail.trn' with FORMAT, NORECOVERY

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'LOAN_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with NORECOVERY
RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\Backup_tail.trn' with RECOVERY

My concern in the above scenario is – will there be any change for piecemeal restore considering the fact that meta data file is now spread in all the file groups and does not belongs to primary file group only.

For any partial restoration, restoration of primary file group was mandatory since it contains all metadata however if all files are now .mdf, how will it work?

Best Answer

File extensions are meaningless to the application. For example, create a text file and name it textfile.foo and open it in Notepad. Notepad doesn't care what the filename is--it just opens it because you told it to open it.

SQL Server works the same way. You can name the files with any extension you want and it doesn't change how SQL Server uses them. The first data file, which is typically the one with the .mdf file, will always hold the metadata and can never be removed. The metadata will not be spread out to other files just because you gave them an .mdf file extension.

File extensions are almost always for the humans--so that we can more easily keep track of things. Accordingly, we use the .mdf extension on the first data file and .ndf on additional data files so that humans will be able to easily tell which file contains the metadata and can never be removed from the database.

I'm sure there are some applications out there that require file extensions to meet certain requirements, but SQL Server isn't one of them, at least not in regard to the file extension on database files.

Related Question