locked
Restore of filegroup to development database RRS feed

  • Question

  • Hello,

    We have a large database with two filegroups, PRIMARY and one for the indexes. I have taken a backup of the PRIMARY filegroup, is it possible to restore this backup onto a separate server (creating a new development database)?

    Any advice would be most appreciated.

    Thank you,

    Andrew

    Tuesday, June 25, 2013 2:39 PM

Answers

  • Hello,

    Just as an FYI - all filegroups are contained in a full database backup. You don't have to take filegroup backups in order to restore filegroups. If you do need to take a filegroup backup, please use COPY_ONLY so that you don't interrupt your current restore strategy.

    Also, you'll need enterprise edition in order to have partial database availability which is what you're referring to.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, June 25, 2013 3:13 PM
    Answerer

All replies

  • BACKUP DATABASE DB_NAME FILEGROUP = 'FILEGROUPNAME' TO DISK = 'YOURPATHYOURFILENAME.BAK' WITH INIT

    RESTORE DATABASE DB_NAME FILEGROUP = 'FILEGROUPNAME' FROM BACKUP WITH PARTIAL, RECOVERY

    This script u can use for backup and restore of Primary file group,please go through below link first study it and then use the script.If u have multiple filegroups restore each with norecovery and last with recovery

    http://support.microsoft.com/kb/281122

    you can see this also

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


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Tuesday, June 25, 2013 2:57 PM added line
    • Proposed as answer by Fanny Liu Wednesday, June 26, 2013 8:17 AM
    Tuesday, June 25, 2013 2:54 PM
  • Hello,

    Just as an FYI - all filegroups are contained in a full database backup. You don't have to take filegroup backups in order to restore filegroups. If you do need to take a filegroup backup, please use COPY_ONLY so that you don't interrupt your current restore strategy.

    Also, you'll need enterprise edition in order to have partial database availability which is what you're referring to.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, June 25, 2013 3:13 PM
    Answerer
  • Thanks very much for your help!
    Thursday, June 27, 2013 11:08 AM