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?