Sql-server – Backup and Restore SQL server files and filegroups

backupdatafilefilegroupssql server

I have a database called Admin. It has only one default filegroup called primary. 

File group: primary
files in the file group: Admin_data.mdf

Now I have taken a full filegroup backup.

Backup file name: fg_full.bak

Then created a new ndf in the same file group called sec_file.ndf and created few tables, inserted 1000 rows on each table.

After an hour took a file backup of my MDF and NDF files

MDF backup: Admin_Data.bak
NDF backup: sec_file_ndf.bak

File Backup using SSMS
enter image description here

Here somehow my sec_file get deleted so I want to restore latest NDF backup, how can I restore it?

Output of restore headeronly

enter image description here

Restore filelistonly from disk = 'D:\sec_file_ndf.bak'

enter image description here

Best Answer

Query the backup file with 

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

and 

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak'

...to verify the contents of your D:\sec_file_ndf.bak file.

You might have multiple backups in one *.BAK file. If so add the option FILE = 2 to your RESTORE command to restore your most recent backup of the *.NDF file.

Related Question