How should I backup a readonly Filegroup?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

How should I backup a readonly Filegroup?

Postby MartinH » Fri Feb 03, 2012 9:32 am

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.
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am

Postby eddie davis » Mon Feb 06, 2012 1:24 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby MartinH » Mon Feb 06, 2012 5:06 pm

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.
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am

Postby petey » Wed Feb 08, 2012 1:48 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby eddie davis » Wed Feb 08, 2012 2:13 pm

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby ChrisAVWood » Wed Feb 08, 2012 4:22 pm

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
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests