Can Incremental Backup be done with Simple recovery Model?

For SQL2000 - Just wondering if the incremental backup requires transaction
logs.
We have a database upgrade process that performs a lot of updates. In order
to keep the transaction log from growing out of control - I would like to be
able to set the recovery model as "simple". I am hoping that I could do the
following:
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run an incremental backup
Does this sound reasonable? Should steps 4 and 5 be reversed?
Thanks in advance.No, Incremental backups (which are called transaction log backups in SQL server) are based on the
transaction log. If you are missing log records in the transaction log, you wouldn't be able to
restore from the transaction log backups.
Consider differential backups, perhaps?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In order
> to keep the transaction log from growing out of control - I would like to be
> able to set the recovery model as "simple". I am hoping that I could do the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>|||"TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - Just wondering if the incremental backup requires
transaction
> logs.
> We have a database upgrade process that performs a lot of updates. In
order
> to keep the transaction log from growing out of control - I would like to
be
> able to set the recovery model as "simple". I am hoping that I could do
the
> following:
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run an incremental backup
> Does this sound reasonable? Should steps 4 and 5 be reversed?
> Thanks in advance.
>
If this is a one-time upgrade, I would suggest the following...
1. Perform Full DB Backup
2. Set to Simple
3. Run upgrade
4. Perform Full DB Backup
5. Test your upgraded information. If all is OK
6. Set recovery back to full.
I'm not sure why you need the incrementals.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Tibor,
Actually - I really meant to ask the question about Differential backups
(and not Incremental). I just had my terminology messed up.
So - would I be able to do this for Differential backups...
1. Perform a full DB backup
2. Set the recovery model to Simple
3. Run the upgrade
4. Set the recovery model back to Full
5. Run a differential backup
Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> No, Incremental backups (which are called transaction log backups in SQL
server) are based on the
> transaction log. If you are missing log records in the transaction log,
you wouldn't be able to
> restore from the transaction log backups.
> Consider differential backups, perhaps?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TJT" <TJT@.nospam.com> wrote in message
news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> > For SQL2000 - Just wondering if the incremental backup requires
transaction
> > logs.
> >
> > We have a database upgrade process that performs a lot of updates. In
order
> > to keep the transaction log from growing out of control - I would like
to be
> > able to set the recovery model as "simple". I am hoping that I could do
the
> > following:
> > 1. Perform a full DB backup
> > 2. Set the recovery model to Simple
> > 3. Run the upgrade
> > 4. Set the recovery model back to Full
> > 5. Run an incremental backup
> >
> > Does this sound reasonable? Should steps 4 and 5 be reversed?
> >
> > Thanks in advance.
> >
> >
>|||Hi Rick,
The database is rather large (90GB) and we are trying to minimize downtime.
This is why I was hoping to perform a differential backup.
If the database were small and downtime were not a problem - I would perform
another Full backup after the upgrade as you suggest.
Thanks,
Tom
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uvuhVO%23eFHA.1448@.TK2MSFTNGP14.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> > For SQL2000 - Just wondering if the incremental backup requires
> transaction
> > logs.
> >
> > We have a database upgrade process that performs a lot of updates. In
> order
> > to keep the transaction log from growing out of control - I would like
to
> be
> > able to set the recovery model as "simple". I am hoping that I could do
> the
> > following:
> > 1. Perform a full DB backup
> > 2. Set the recovery model to Simple
> > 3. Run the upgrade
> > 4. Set the recovery model back to Full
> > 5. Run an incremental backup
> >
> > Does this sound reasonable? Should steps 4 and 5 be reversed?
> >
> > Thanks in advance.
> >
> >
> If this is a one-time upgrade, I would suggest the following...
> 1. Perform Full DB Backup
> 2. Set to Simple
> 3. Run upgrade
> 4. Perform Full DB Backup
> 5. Test your upgraded information. If all is OK
> 6. Set recovery back to full.
>
> I'm not sure why you need the incrementals.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Yes, that should work. Just test on a test server that the steps are fine first (possibly with less
data volume). Also, be aware that you have now broken your chains of log backups. The log backup
taken after this will not be able to apply after a log backup taken before all this. Only from the
last db backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:eySKSR%23eFHA.2244@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor,
> Actually - I really meant to ask the question about Differential backups
> (and not Incremental). I just had my terminology messed up.
> So - would I be able to do this for Differential backups...
> 1. Perform a full DB backup
> 2. Set the recovery model to Simple
> 3. Run the upgrade
> 4. Set the recovery model back to Full
> 5. Run a differential backup
> Thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
>> No, Incremental backups (which are called transaction log backups in SQL
> server) are based on the
>> transaction log. If you are missing log records in the transaction log,
> you wouldn't be able to
>> restore from the transaction log backups.
>> Consider differential backups, perhaps?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> > For SQL2000 - Just wondering if the incremental backup requires
> transaction
>> > logs.
>> >
>> > We have a database upgrade process that performs a lot of updates. In
> order
>> > to keep the transaction log from growing out of control - I would like
> to be
>> > able to set the recovery model as "simple". I am hoping that I could do
> the
>> > following:
>> > 1. Perform a full DB backup
>> > 2. Set the recovery model to Simple
>> > 3. Run the upgrade
>> > 4. Set the recovery model back to Full
>> > 5. Run an incremental backup
>> >
>> > Does this sound reasonable? Should steps 4 and 5 be reversed?
>> >
>> > Thanks in advance.
>> >
>> >
>>
>|||I can backup 90GB in just a few minutes using no third party tools
How long is "Too Long" ?
What kind of IO subSystem do you have ? (RAID Array, SAN, NAS)
Greg Jackson
PDX, Oregon