Backing up a 3-File Database 1

[JohnBates]

hi everyone,

This is a 2005 db in Full recovery mode.

Has Prod_data.mdf, Prod_index.ndf and of course Prod_log.ldf

I have 2 questions:

1. does a Full backup where the Backup Component is 'Database' backup the .ndf and .log files too or only the .mdf ?

2. If the database needs to be restored, I need to do a separate Restore Database step for the Prod_index.ndf file, correct?

Thanks! John

 

[mrdenny]

1. Yes, the BACKUP DATABASE command backs up all data within all the data files including the ndf and ldf files.

2. No, when you restore your full backup the ndf and ldf files are restored as well.

The only time that backups do not contain the data from all data files is when you are going filegroup backups.

An article I wrote should answer a lot of backup and recovery questions. You can download the PDF below.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog