SQL Server Secondary Data file & SQL recovery

SQL server secondary data file is an optional data file that stores users data, we can also say that it is a user defined data file. A SQL server database can has any number of secondary data files or even no secondary data file. It totally depends upon the database administrator requirement. As we see that some database has several numbers of secondary data files but some have no any secondary data file. The recommended file extension for secondary data file is .NDF.  Now the point comes to how to create, backup, restore and recover SQL server secondary data file so I have discussed these topics below....

• Create a secondary data file
• Take a backup of secondary data file
• Restore a secondary data file

Create a secondary data file: A database administrator can create a secondary data file any time when he wishes to create. To create execute the below query:

Create a new file group

ALTER DATABASE yourtest_filegroup
   ADD FILEGROUP NonClustIndexes
Go

-- Add a file to the file group; we can now use the file group to store data

ALTER DATABASE yourtest_filegroup
   ADD FILE (
      NAME = NonClustIndexes,
      FILENAME = 'E:\MSSQL\DEV\NonClustIndexes.ndf',
      SIZE = 25MB,
      MAXSIZE = 200MB,
      FILEGROWTH = 25MB
      )
   TO FILEGROUP NonClustIndexes
Go

Take a backup of secondary data file: To take a backup of secondary data file, you will have to execute this query:

BACKUP DATABASE YourDatabaseName
   FILEGROUP = 'Yourdatabasegroup1',
   FILEGROUP = 'Youtdatabasegroup2'
   TO DISK = 'C:\MySQLServer\Backups\Databasename\YourdbFiles.bck'
GO

Restore a secondary data file: SQL server secondary data file got corrupt or damaged due to many reasons like virus attack, power failure, human error, hardware problem and many more. You can restore corrupt/damaged NDF file from the backup. To restore from backup execute this query:

RESTORE DATABASE YourDatabase
   FILE = 'YourDatabase_data_1',
   FILE = 'YourDatabase_data_2',
   FILEGROUP = 'new_customers'
   FROM YourDatabaseBackups
   WITH
      FILE = 9,
      NORECOVERY;

Code snippet from sqlservercentralforum

Summary: NDF file is a user defined data file without it you can't retrieve your database so always keep the backup of secondary data file.

I welcome your comments. If you have any question regarding SQL server secondary data files then share it with me.