Home » SQL ServerRSS

Backup/Restore .ndf files

Due to large size (400 GB) if a database; planning to split database to multiple data files (.ndf files). To perform weekly backup; can see the option of taking backup of individual .df/.ndf file. But don't find option to restore them separately.

If .ndf files can't be restored separately; then what could be purpose of generating their individual back from SQL Server Management Studio.

Pls help.

- Deepak

 

4 Answers Found

 

Answer 1

You can put the file into a file group

CREATE DATABASE test
GO
ALTER DATABASE test SET  RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
  FILENAME = 'D:\wwdat1.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP ww_Group


create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group


insert test..test default values
insert test..test_GR default values

SELECT * FROM test..test_GR
SELECT * FROM test..test

GO
TRUNCATE TABLE test..test_GR

BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY

BACKUP DATABASE test
   FILE = 'ww',
   FILEGROUP = 'ww_Group'
   TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
   TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
   TO disk='D:\Test__log.ldf' WITH NOINIT


RESTORE DATABASE test
   FILE = 'ww',
   FILEGROUP = 'ww_Group'
   FROM DISK ='D:\CROUPFILES.bak'
   WITH FILE = 1,NORECOVERY
RESTORE LOG test
   FROM disk='D:\Test__log.ldf'
   WITH FILE = 1, NORECOVERY
RESTORE LOG test
   FROM disk='D:\Test__log.ldf'
   WITH FILE = 2, RECOVERY
GO
DROP DATABASE test

 

Answer 2

In addition to what Uri already mentioned, placing the ndf  files in a filegreoup and, then, perform backups on the filegroup. I would revisit my backup/restore strategy for any changes made on your physical database structure
 
Here's a reference article in dealing wih multiple filegroups as part of your backup strategy
"Deepak Mehrotra" <=?utf-8?B?RGVlcGFrIE1laHJvdHJh?=> wrote in message news:7f092000-2aad-4d71-bf76-c3c166aaf152...

Due to large  size (400 GB) if a database; planning to split database to multiple data files  (.ndf files). To perform weekly backup; can see the option of taking backup of individual .df/.ndf file. But don't find option to restore them separately.

If .ndf files can't be restored separately; then what could be purpose of generating their individual back from SQL Server Management Studio.

Pls help.

- Deepak

 

Answer 3

Deepak,

Flile level backup and restrore is possible. The files  in a database can be backed up and restored individually. Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

 

RESTORE DATABASE <database> FILE = <name_of_file_A> 
  FROM <file_backup_of_file_A> 
  WITH NORECOVERY
RESTORE DATABASE <database> FILE=<name_of_file_B>, <name_of_file_C> 
  FROM <file_backup_of_files_B_and_C> 
  WITH RECOVERY

 For more detail refer "Restore specific files or filegroups to a database (a file restore)." on the BooksOnLine.

Also note each file has logical name that only can be used on Backup / Restore. The logical file name in turn pointing to the physical file which may be of *.mdf or *.ndf.

 

 

Answer 4

Here's a related blog post, might aid in getting the big picture:

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter