Dear All,



I am using SQL server database consist of multiple .NDF files. I want to take a backup of single file group because one single file group contains approx 50 GB data and I have more 15 .NDF files.

Now problem is that when I take a backup of single file group and want to restore on a same existing database I filed to do so.



Anybody have an idea about this how to accomplish this whole work?


mobilink

Hi,

Friend, its a big topic to discuss practically. Many thiungs to keep in mind and to be varified specially in case of File groups.

First I like to say that you must restore all the T-Log files recent than the data files u r restoring.

If u r using Enterprise manager to restore then, u have to carefully check the check box saying "Select a subset of backup sets" and then read and cofigure other properties well. Click the Selecttion Criteria button just after the check box and there u can select the Files and filegroups in more filtered manner. Be sure to restote the Filegropu backup with NO RECOVERY. Cos u will have to apply T-Logs also. (Choose 2nd option of three displayed). Whe u r sure that no further Log are left, then Last Log file must be restored With RECOVERY (First option).

If u r using T-SQL then I hope u can find the Syntax in Books Online easily. Further considerations are same. Here is an example.


RESTORE DATABASE MyDb FILE = 'SECON_DBBACKFILE' FROM MyBackMedia
WITH NORECOVERY,STATUS =10
RESTORE LOG MyDb_LOG FROM MyDbLOGBackUp WITH FILE = 2

** 2 means the secon backup set of ur backup media which hold the Tlog backup.

Good luck.


B. Anant