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

backup of transaction log

Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server database each midnight on tape and moving it off-site. I then take differential backups and also want to backup the transaction log. Assuming that my production database server is
on site and the site is burned down, how do I recover my transaction logs from? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recovery? Thanks in advance for the help!
- Bill
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill
|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential backup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file server store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You must create SP-s).
BOL: BACKUP and RESTORE
JBandi
|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill

backup of transaction log

Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server d
atabase each midnight on tape and moving it off-site. I then take differenti
al backups and also want to backup the transaction log. Assuming that my pro
duction database server is
on site and the site is burned down, how do I recover my transaction logs fr
om? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recove
ry? Thanks in advance for the help!
- BillHi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe
.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential b
ackup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file serv
er store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You mus
t create SP-s).
BOL: BACKUP and RESTORE
JBandi|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill