How should I backup a readonly Filegroup?

MartinHMartinH Posts: 82 Bronze 2
edited February 8, 2012 10:22AM in SQL Backup Previous Versions
Hi:

I have a database that contains dynamic and static data. The static data is historical data that never changes, and I wish to move these tables and their indices to a ReadOnly Filegroup. The rest of the data is dynamic and changes frequently.

I would like to be able to do a one-time full backup (all filegroups) and after that I just want to backup the dynamic portion. Is this possible?

I case of catastrophic failure, what would the restore process entail?

Thanks,
Martin.
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Thank you for your post into the forum.

    With regards to filegroup backups and the restore process, is exactly the same as using native SQL Server backups. So to answer your questions:
    I would like to be able to do a one-time full backup (all filegroups) and after that I just want to backup the dynamic portion. Is this possible?

    The answer is yes, if this the course of action you wish to take.
    I case of catastrophic failure, what would the restore process entail?

    Restore the Full backup, next the latest filegroup backup and followed by the unbroken chain of transaction log backups from the original Full Backup.

    Whilst you wish to take a one time only Full Backup, you may want to reconsider this action and perform a regular full backup to reduce the number transaction log backups for restore. Also taking regular Full backups, if the one time only full backup file becomes corrupt as the file maybe moved around storage locations. You wil be unable to recovery the database if a catastrophic failure occurs.

    Also perform and test your recovery strategy to ensure that it meets your needs and that of your organisation.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • MartinHMartinH Posts: 82 Bronze 2
    Eddie:

    Good questions! I'm not sure I know the answers, please allow me to elaborate.

    The whole purpose of the exercise is to reduce the size of the daily backups. The database is just over 2Gb and the compressed size is 250Mb. Of this, over half is historic data that does not change (ever).

    I thought that if I could move the historic data to a new filegroup, I could do much smaller daily backups.

    Currently we do 2 Full backups a day, and one Log backup per day. Now you know the situation and the goal we wish to achieve, what would you suggest?

    I have no problem doing a 'complete' backup once a week (including the historic data), and partial backups (no historical data) throughout the week. Is this possible?

    Your help is appreciated.

    Regards,
    Martin.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If your objective is only to minimise the size of your full database backups, then backing up only the read-write filegroups would work. Even then, there are 2 options - explicitly backing up the filegroups, or taking a partial backup using the READ_WRITE_FILEGROUPS option in the backup command.

    The former option makes recovery harder - as Eddie mentioned, you would need to restore every trx log backup since the last full backup to get your database into a consistent state.

    The latter option doesn't require you to restore your trx logs, but is available only on SQL Server 2005 and newer.

    Consider also differential backups, and partial differential backups, which should yield even smaller backup files.

    Lastly, you should really consider if taking a single trx log backup for the day is enough. You stand to lose quite a bit of work between the time the full backup is performed and the time the trx log backup is performed. Also, full database/differential/partial backups do not allow you to stop the restore process at a specific point in time.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Thank you for your reply.

    Peter has highlighted other options for you to consider.

    When designing a backup strategy, you need to consider the recovery process and test out the recovery procedure to see if it meets your needs.

    The advice below is purely personal and the actions I would initially take if I were in your shoes:
      1. Full Backup once a week, on a weekend. 2. Daily or twice daily filegroup backup. 3. Transaction log backups every 30 minutes or every 15 minutes. This allows for a point in time recovery.

    Please consider Petey's recommendations of taking a partial backup using the READ_WRITE_FILEGROUPS option in the backup command. Or performing Differential backups. This will help you reduce the number of files you need to restore in the event of a problem occurring.

    Sorry to keep harping on about it, test the recovery solution at regular intervals and modify the backup and recovery strategy to overcome any problems you encounter.

    I hope the above helps.

    If any other readers of this forum post, have advice or experiences to share, please add a post.

    Many Thanks
    Eddie Davis
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Martin,

    if this is truely read-only data could you not export it to another read-only database? if you access it then you would need to change your code but this would save the backup problem and possibly save the existing database from future expansion as you put more data into it.

    HTH

    Chris
    English DBA living in CANADA
    · Share on Twitter
Sign In or Register to comment.