Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup and restore of filegroups Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 8:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 414, Visits: 2,081
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 ?
Post #1428030
Posted Thursday, March 7, 2013 9:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 40,418, Visits: 36,869
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 2008, MVP
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

Post #1428070
Posted Thursday, March 7, 2013 9:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 414, Visits: 2,081
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?
Post #1428083
Posted Thursday, March 7, 2013 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 40,418, Visits: 36,869
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 2008, MVP
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

Post #1428090
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse