backup strategy question (sql 2005)

Hi,
im thinking about the backup strategy for our new sql 2005 database. I would
like to know how you would do it. The scenario is the following:
- the db consists of 2 file groups (primary + another one)
- the second file group is pretty large (250 GB) and contains a lot of blob
data that changes rarely
- the primary group is rather small (3 GB), but changes frequently
Since the first file group is small, I plan to backup it every day (full
backup). The secound group should be fully backuped every 2 weeks. In the
meantime I would backup the daily changes of the second group using
differncial backups. Any better ideas?
What I dont understand is how the transaction log behaves in this case. The
log contains the changes for all file groups. So what happens if I backup
only one file group? Are the changes of that group removed from the log and
the changes of the other group stay logged? I wonder how this works.
thanks in advance,
BenjaminThe only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
diff, file, filegroup, filegrup with diff etc) does not empty the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case. The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin|||Hi,
ok, interesting. But if this is the case, why should I create full database
backups at all? I mean, I dont want to store the logs forever. If I backup
the entire database or a part of it I don't want to keep the old log files.
Can you tell me how to achieve this?
"Tibor Karaszi" wrote:
> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
> diff, file, filegroup, filegrup with diff etc) does not empty the log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> > Hi,
> >
> > im thinking about the backup strategy for our new sql 2005 database. I would
> > like to know how you would do it. The scenario is the following:
> >
> > - the db consists of 2 file groups (primary + another one)
> > - the second file group is pretty large (250 GB) and contains a lot of blob
> > data that changes rarely
> > - the primary group is rather small (3 GB), but changes frequently
> >
> > Since the first file group is small, I plan to backup it every day (full
> > backup). The secound group should be fully backuped every 2 weeks. In the
> > meantime I would backup the daily changes of the second group using
> > differncial backups. Any better ideas?
> >
> > What I dont understand is how the transaction log behaves in this case. The
> > log contains the changes for all file groups. So what happens if I backup
> > only one file group? Are the changes of that group removed from the log and
> > the changes of the other group stay logged? I wonder how this works.
> >
> > thanks in advance,
> > Benjamin
>|||Hmm, I'm afraid that I don't get the question...
Are you saying that you don't want to perform transaction log backups? Find, just set the recovery
model for the database to simple.
Or are you saying something else?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:EF2882DD-C711-4F3B-A092-BD34C4DDD723@.microsoft.com...
> Hi,
> ok, interesting. But if this is the case, why should I create full database
> backups at all? I mean, I dont want to store the logs forever. If I backup
> the entire database or a part of it I don't want to keep the old log files.
> Can you tell me how to achieve this?
>
> "Tibor Karaszi" wrote:
>> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
>> diff, file, filegroup, filegrup with diff etc) does not empty the log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
>> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
>> > Hi,
>> >
>> > im thinking about the backup strategy for our new sql 2005 database. I would
>> > like to know how you would do it. The scenario is the following:
>> >
>> > - the db consists of 2 file groups (primary + another one)
>> > - the second file group is pretty large (250 GB) and contains a lot of blob
>> > data that changes rarely
>> > - the primary group is rather small (3 GB), but changes frequently
>> >
>> > Since the first file group is small, I plan to backup it every day (full
>> > backup). The secound group should be fully backuped every 2 weeks. In the
>> > meantime I would backup the daily changes of the second group using
>> > differncial backups. Any better ideas?
>> >
>> > What I dont understand is how the transaction log behaves in this case. The
>> > log contains the changes for all file groups. So what happens if I backup
>> > only one file group? Are the changes of that group removed from the log and
>> > the changes of the other group stay logged? I wonder how this works.
>> >
>> > thanks in advance,
>> > Benjamin
>>|||I have no idea if this would work or not. What are your business
requirements for availability? How much data loss is acceptable? How long
can the system be down for a recovery operation? What type of hardware are
you using?
Sure, you can simply backup the databases using virtually any method that
you choose. But, that doesn't mean the backups are going to accomplish
something. If your business rules state that you can only be offline for 5
minutes and you setup backups that are going to take 1 hour to restore, then
your backups are essentially worthless to the business, because they do not
meet business needs.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I
> would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of
> blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case.
> The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log
> and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin