none
Restoring a file group database in SQL 2005 Server

    Question

  • I have an SQL Server 2005 database file group database that I'm trying to restore.   When I do a syntax check the from/source statement of the restore continues to have errors.  I have the following code:

    Use FileGroupDB
    go
    RESTORE DATABASE FileGroupDB

    FILEGROUP = 'JJJJJJJJ',
    FILE = 'r:\JJJJJJData2_1.NDF',
    FILE = 'r:\JJJJJJData2_2.NDF',
    FILE = 'r:\JJJJJJData2_3.NDF',
    FILE = 'r:\JJJJJJData2_4.NDF',

    FILEGROUP = 'KKKKKKKKK',
    FILE = 'x:\KKKKKKIndx2_1.NDF',
    FILE = 'x:\KKKKKKIndx2_2.NDF',
    FILE = 'x:\KKKKKKIndx2_3.NDF',
    FILE = 'x:\KKKKKKIndx2_4.NDF',

    FILEGROUP = 'LLLLLLLL',
    FILE = 'x:\LLLLLIndx2_1.NDF',
    FILE = 'x:\LLLLLIndx2_2.NDF',
    FILE = 'x:\LLLLLIndx2_3.NDF',
    FILE = 'x:\LLLLLIndx2_4.NDF',

    FILEGROUP = 'MMMMMMMM',
    FILE = 'r:\MMMMMMData3_1.NDF',
    FILE = 'r:\MMMMMMData3_2.NDF',
    FILE = 'r:\MMMMMMData3_3.NDF',
    FILE = 'r:\MMMMMMData3_4.NDF',

    FILEGROUP = 'NNNNNNNN',
    FILE = 'x:\NNNNNNIndx3_1.NDF',
    FILE = 'x:\NNNNNNIndx3_2.NDF',
    FILE = 'x:\NNNNNNIndx3_3.NDF',
    FILE = 'x:\NNNNNNIndx3_4.NDF',

    FILEGROUP = 'AAAAAAAAA',
    FILE = 'x:\AAAAAAAIndx3_1.NDF',
    FILE = 'x:\AAAAAAAIndx3_2.NDF',
    FILE = 'x:\AAAAAAAIndx3_3.NDF',
    FILE = 'x:\AAAAAAAIndx3_4.NDF',

    FILEGROUP = 'BBBBBBBB',
    FILE = 'r:\BBBBBBBData4_1.NDF',
    FILE = 'r:\BBBBBBBData4_2.NDF',
    FILE = 'r:\BBBBBBBData4_3.NDF',
    FILE = 'r:\BBBBBBBData4_4.NDF',

    FILEGROUP = 'CCCCCCCCCCC',
    FILE = 'x:\CCCCCIndx4_1.NDF',
    FILE = 'x:\CCCCCIndx4_2.NDF',
    FILE = 'x:\CCCCCIndx4_3.NDF',
    FILE = 'x:\CCCCCIndx4_4.NDF',

    FILEGROUP = 'DDDDDDD',
    FILE = 'x:\DDDDDDIndx4_1.NDF',
    FILE = 'x:\DDDDDDIndx4_2.NDF',
    FILE = 'x:\DDDDDDIndx4_3.NDF',
    FILE = 'x:\DDDDDDIndx4_4.NDF',

    FILEGROUP = 'PRIMARY',
    FILE = 'D:\EDDDD\Data\DDDD.mdf',

    FILEGROUP = 'EEEEEEEEE',
    FILE = 'D:\EEEE_RA_0.ndf',
    FILE = 'D:\EEEE_RA_0.ndf',

    FILEGROUP = 'FFFFFF',
    FILE = 'D:\FFFFFF_RA_1.ndf',

    FILEGROUP = 'GGGGGG',
    FILE = 'D:\GGGGGG_RA_2.ndf',

    FILEGROUP = 'HHHHHH',
    FILE = 'D:\HHHH_RA_3.ndf',

    FILEGROUP = 'IIIII',
    FILE = 'D:\IIIII_RA_4.ndf'

    FROM 'H:\Location Database Backups\Sub Location Database FILE GROUP\Database\SourceRestore_backup_201307180430.bak'
    WITH RECOVERY
    go

    What must be in the from statement?

    Thursday, July 25, 2013 9:35 PM

Answers

All replies

  • First, this is not a security question.

    Second, what exactly are you trying to do.  Do you want to restore the full database?

    Have you looked at the syntax for the RESTORE statement?

    A simple full restore would look like:

    RESTORE DATABASE FileGroupDB from disk='H:\wherever.bak' with recovery

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 10:00 PM
  • Yes I'm trying to restore the entire database.  Since a file group database where the groups are spread across multiple drives shouldn't I be doing the above?

    Thanks

    Thursday, July 25, 2013 10:40 PM
  • Yes I'm trying to restore the entire database.  Since a file group database where the groups are spread across multiple drives shouldn't I be doing the above?

    Nope. Just RESTORE the database. Add WITH MOVE clauses for any files that you want to put in a different folder than they were in where and when the backup was taken.

    The SSMS Restore Database Wizard will walk you through it.  But instead of running the restore through the wizard, hit the "Script" button and it will generate a TSQL script for you to examine and run in a query window.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, July 25, 2013 10:46 PM
  • I think I understand so far, however, I have file groups that span multiples files in the same directory with just a different file name.  Example:

        FileGroup = 'Group A' has File 1.ndf, file 2.ndf, file 3.ndf

    what happens in this case?

    Thanks

     
    Thursday, July 25, 2013 10:59 PM
  • I think I understand so far, however, I have file groups that span multiples files in the same directory with just a different file name.  Example:

        FileGroup = 'Group A' has File 1.ndf, file 2.ndf, file 3.ndf

    what happens in this case?

     

    The RESTORE command will create the files in the original locations or in new locations if you use WITH MOVE.   You can look at the files that it will create using:

    RESTORE filelistonly from disk='H:\wherever.bak'

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 11:05 PM
  • So what to I do to over write the existing files?  I want to restore thing in there original location.

    Thanks

    Thursday, July 25, 2013 11:54 PM
  • To restore over an existing database just use RESTORE with REPLACE.

    EG

    backup database test to disk='c:\temp\test.bak'
    
    restore database test from disk='c:\temp\test.bak' 
    with replace, recovery, stats=5
    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 26, 2013 12:00 AM
  • I tried the restore database option, however, it continues to tell me that files are in use by the database.

    Thanks

    Friday, July 26, 2013 12:14 AM
  • That will be the case if any users are connected to the database.

    You can issue

    ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    to terminate all the existing connections to the database before performing the restore

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, July 26, 2013 12:15 AM
  • I have the DB in single user mode and I'm attempting to execute the restore.  I continue to get the same message about the files being use by the DB.  What might be the problem?

    Thanks

    Friday, July 26, 2013 12:38 AM
  • Are you connected to the database?  You should be in master.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 26, 2013 12:50 AM
  • I was not in master, so I change to master it appears to be working, my problem now is that I ran out of space.  I will address that. 

    Thanks for you help.

    thanks

    Friday, July 26, 2013 1:00 AM
  • Hello,

    Can you try to change the database to single user mode before restore the database with REPLACE option? For example:

    ALTER DATABASE Your_DB  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE 

    Reference:http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Friday, July 26, 2013 1:05 PM
  • Hello,

    I’m writing to follow up with you on this post. If you have any other question, you can open a new thread in the forum. I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have. Thanks.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Wednesday, July 31, 2013 12:11 PM