Post reply

backup and restore of filegroups

  • March 7, 2013 at 8:36 am

    #275003

    I have a database with 10 filegroups including primary. Each file group holds a data file. This database is in simple recovery mode. We take full backup of this database which takes around 7 hours to finish. The size of the db is 1.5 TB. I would like to take the filegroup backups instead of full backup. What's the process of backing up each and every filegroup and if need to restore how we need to perform ?

  • March 7, 2013 at 9:40 am

    #1594530

    From Books Online:

    Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups.

    You can take filegroup backups of a database in simple recovery, but they'll be useless for restore unless all but one are read only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 7, 2013 at 9:52 am

    #1594542

    Thanks Gila.

    Let's assume i made one of the filegroup as read only. For the first time i need to restore the primary as with partial, recovery to make the db online and for the second file group restore do i need to use partial and recovery again?

  • March 7, 2013 at 9:58 am

    #1594549

    http://msdn.microsoft.com/en-us/library/ms189906.aspx

    http://msdn.microsoft.com/en-us/library/ms190388.aspx

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

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply