none
Sqlserver 2012: Filegroup Backup VS Full Database Backup RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Monday, July 13, 2015 8:37 AM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Tuesday, July 14, 2015 9:40 PM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Monday, July 13, 2015 9:21 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    Monday, July 13, 2015 10:27 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    https://msdn.microsoft.com/en-IN/library/ms189906.aspx?f=255&MSPPError=-2147217396

    Vote As Helpful if it helps to solve your issue

    Monday, July 13, 2015 11:37 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Monday, July 13, 2015 11:54 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    Monday, July 13, 2015 12:29 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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. :)

    Monday, July 13, 2015 1:06 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Monday, July 13, 2015 1:28 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Tuesday, July 14, 2015 11:04 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Tuesday, July 14, 2015 9:40 PM
    |