Red Gate forums :: View topic - How should I backup a readonly Filegroup?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

How should I backup a readonly Filegroup?

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
MartinH



Joined: 15 Apr 2005
Posts: 61

PostPosted: Fri Feb 03, 2012 9:32 am    Post subject: How should I backup a readonly Filegroup? Reply with quote

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.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 889
Location: Red Gate Software

PostPosted: Mon Feb 06, 2012 1:24 pm    Post subject: Reply with quote

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:
Quote:

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.

Quote:

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
Back to top
View user's profile Send private message Send e-mail
MartinH



Joined: 15 Apr 2005
Posts: 61

PostPosted: Mon Feb 06, 2012 5:06 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Wed Feb 08, 2012 1:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
eddie davis



Joined: 14 Jun 2006
Posts: 889
Location: Red Gate Software

PostPosted: Wed Feb 08, 2012 2:13 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Wed Feb 08, 2012 4:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group