Answered by:
Sqlserver 2012: Filegroup Backup VS Full Database Backup

Question
-
Hi Friends,
Please as I know filegroup backup mostly used for partition table, but I want to inquire the pros and con of
database Filegroup Backup VS Full Database Backup and which is best way.
thank you.
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
asad
Answers
-
some failure happened on 13 July and we want to restore database, as
above case is it possible we can restore live data from 12 Jul and read only
data from 30 June.That should work. I am assuming here that the database is in full recovery
and that you also apply the transaction log.However, no matter what backup/restore scheme you set up, asking here is not
sufficient. You must always test your backups to ensure that the backups are
good (preferrably run DBCC CHECKDB on them as well). And with a more complex
and sophisticated scheme it is also important that you test that the scheme
actually works and you are familiar with the procedures. You don't want to
be in the situation that where there is a disaster that you don't know how
to bring the pieces together./Erland
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, July 20, 2015 8:26 AM
All replies
-
Which is the best depends on what you want to achieve. Certainly a full database backup is the simplest way to go, since ultimately you need to restore the full database.
However, one scenario where filegroup backups could come in handy is when you have lots of archive data on a read-only filegroup. You back up that filegroup once. Then you take filegroup backups of your filegroup(s) with the live data, thereby considerably reudcing your backup times.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
thank you for reply, we can perform incremental backup as well differential backup on filegroup , Please what are the possible limitation of filegroup level backup. <iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
asad
-
-
It would help if you told us what you had in mind to use a filegroup backup for.
Note that thing you cannot do is to restore a single filegroup in order to achieve some table-only restore. You can restore a single filegroup, but before you can use the database, you need to restore the transaction log, to get the database transactionally consistent.
Another answer would be: if you don't know much about filegroup backups, don't use them, but stick to full database backup.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Actually we have very large tables and I want to apply table partitions, same time I want to reduce backup time but sure I cannot ignore recovery , therefore I raise the thread . please help me in this regard.<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>
asad
-
Actually we have very large tables and I want to apply table partitions, same time I want to reduce backup time but sure I cannot ignore recovery
asad
Hi Asad,
There are many ways to help the performance of backups. Filegroup backups are a great strategy and partitioning will certainly help some of the queries achieve better performance and reduce your overall I/O. One thing you may want to test to reduce your backup and recovery times is tweaking your backup and restore commands. We cut our backup times by over 70% by doing the following:
https://www.mssqltips.com/sqlservertip/2539/options-to-improve-sql-server-backup-performance/
Our command looks like:
BACKUP DATABASE DW TO DISK='B:\DW.bak' WITH FORMAT, COMPRESSION ,BLOCKSIZE = 65536 ,BUFFERCOUNT = 1000 ,MAXTRANSFERSIZE=2097152
Doing this can be a drain on the CPU and memory so be warned! We had a nightly maintenance window so it wasn't a problem for us. The CPUs run at around 95-100% during the backup.
You can also improve performance by using multiple backup devices.
I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)
-
Actually we have very large tables and I want to apply table partitions, same time I want to reduce backup time but sure I cannot ignore recovery
Then you could work from the ideas in my first post. That is, assuming that your paritions are time-based, and updates are only to the active partitions, you could take backup of the filegroup with the active partitions only.
Then again, if you have a couple of tables, you may prefer to have active partitions on different filegroups to distribite the I/O load. And in that case you will have to backup the full database.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Tuesday, July 14, 2015 6:36 AM
- Unproposed as answer by malikasad Tuesday, July 14, 2015 10:47 AM
-
thank you for reply, Please I need some help regarding backup by filegroup the backup scenario as followed.
Filegroup1 readonly backup taken on 30june
filegroup2 readonly backup taken on 30June
filegroup3 live data backup taken on every day (from 1 to 12 Jul) after business hours
filegroup 4 live data backup taken on every day (from 1 to 12 Jul) after business hours
some failure happened on 13 July and we want to restore database, as above case is it possible we can restore live data from 12 Jul and read only data from 30 June.
thank you.
asad
-
some failure happened on 13 July and we want to restore database, as
above case is it possible we can restore live data from 12 Jul and read only
data from 30 June.That should work. I am assuming here that the database is in full recovery
and that you also apply the transaction log.However, no matter what backup/restore scheme you set up, asking here is not
sufficient. You must always test your backups to ensure that the backups are
good (preferrably run DBCC CHECKDB on them as well). And with a more complex
and sophisticated scheme it is also important that you test that the scheme
actually works and you are familiar with the procedures. You don't want to
be in the situation that where there is a disaster that you don't know how
to bring the pieces together./Erland
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, July 20, 2015 8:26 AM