locked
Backup/Restore .ndf files RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Thursday, July 15, 2010 6:40 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 15, 2010 7:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    bass_player http://bassplayerdoc.blogspot.com/
    • Proposed as answer by Tom Li - MSFT Monday, July 19, 2010 10:50 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:39 AM
    Saturday, July 17, 2010 7:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

     

    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Monday, July 19, 2010 10:50 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:39 AM
    Sunday, July 18, 2010 3:50 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 15, 2010 7:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    bass_player http://bassplayerdoc.blogspot.com/
    • Proposed as answer by Tom Li - MSFT Monday, July 19, 2010 10:50 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:39 AM
    Saturday, July 17, 2010 7:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

     

    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Monday, July 19, 2010 10:50 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:39 AM
    Sunday, July 18, 2010 3:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Friday, July 23, 2010 3:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Useful code,but i think u miss tail log backup and restore,so the ndf file is still in "restoring" state and will not works

    Thursday, July 18, 2013 9:04 AM