Can LOG PI or Log Explorer recover deleted records BEFORE

installation of their softwares? If there has NEVER been a backup of
the Database (.mdf), Recovery Model Set to Full, and there has never
been a backup of the Log (.ldf) files?

Using the trial software for both, this was unclear.

Thank you for your time,

bd"bd420" <bd420@.hotmail.com> wrote in message
news:1136940831.527840.99440@.o13g2000cwo.googlegro ups.com...
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?

My understanding is that as long as you have NOT truncated your log file the
answer should be yes.

> Using the trial software for both, this was unclear.
> Thank you for your time,
> bd|||bd420 (bd420@.hotmail.com) writes:
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?
> Using the trial software for both, this was unclear.

I believe the answer is no. The key here is that you have never taken a
backup of the database. As long as you have done that, the log works as
if you were in simple recovery. After, a log is supposed to be applied
to a database back, so if there isn't any...

But hadn't it been for this fine detail, the answer would be yes. That
is, there is a full backup taken some time, and you are running in
full recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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...[vbcol=seagreen]
transaction[vbcol=seagreen]
order[vbcol=seagreen]
to be[vbcol=seagreen]
the
>
|||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...[vbcol=seagreen]
> "TJT" <TJT@.nospam.com> wrote in message
> news:OWT5nJ#eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
to
> be
> the
> 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...
> server) are based on the
> you wouldn't be able to
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
> to be
> the
>
|||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

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 ser
ver) 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...[vbco
l=seagreen]
> For SQL2000 - Just wondering if the incremental backup requires transactio
n
> logs.
> We have a database upgrade process that performs a lot of updates. In ord
er
> 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 t
he
> 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.
>[/vbcol]|||"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...
transaction[vbcol=seagreen]
order[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
>|||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...
> transaction
> order
to[vbcol=seagreen]
> be
> the
> 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 fi
rst (possibly with less
data volume). Also, be aware that you have now broken your chains of log bac
kups. The log backup
taken after this will not be able to apply after a log backup taken before a
ll 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...[vbco
l=seagreen]
> 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 i
n
> message news:u4zfMN%23eFHA.3012@.tk2msftngp13.phx.gbl...
> server) are based on the
> you wouldn't be able to
> news:OWT5nJ%23eFHA.3808@.TK2MSFTNGP14.phx.gbl...
> transaction
> order
> to be
> the
>[/vbcol]|||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

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

Can I switch recovery model from full to simple and back to full

Here is my scenerio: A developer wants to switch recovery modes from Full
to Simple and then back to Full after his nightly batch cycle for two
databases. Will I have to take two backups a Full at the start of the
nightly batch cycle and then another backup after switching to Simple, right?
Is this possible and will I be able to recover successfully? and what are
the pros and cons? Does anyone have a better idea?
Thanks,
Katherine
The recovery mode will be switched during the STARS/TMS Batch Cycle from
“Full” to “Simple” and then back to “Full” after the successful completion of
the batch processes. Changing the database mode will require that there are
two daily back-up so that in a disaster the transaction logs can be applied.
If you switch to simple and then back to full, then SQL Server would have truncated the log while in
simple. This mean that if you do log backups when back in full, you cannot use those log backups.
You first have to do a db backup (after going back to full) and then the log backups you do after
that will be usable. In essence, you have lost point in time restore possibility for the time from
the last log backup before switching to simple until the first log db backup after going back to
full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple, right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> “Full” to “Simple” and then back to “Full” after the successful completion of
> the batch processes. Changing the database mode will require that there are
> two daily back-up so that in a disaster the transaction logs can be applied.
|||The full database backup before the recovery model change is redundant. One
method to address recovery in your scenario is:
- backup log
- change recovery model to SIMPLE
- execute batch process
- change recovery model back to FULL
- backup database
This will allow you to recover to any point in time from the last full
backup until the change to SIMPLE recovery. Your potential data loss are
changes during the batch process (between the SIMPLE recovery model change
and the database backup).
Hope this helps.
Dan Guzman
SQL Server MVP
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from
> Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple,
> right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> "Full" to "Simple" and then back to "Full" after the successful completion
> of
> the batch processes. Changing the database mode will require that there
> are
> two daily back-up so that in a disaster the transaction logs can be
> applied.

Can I switch recovery model from full to simple and back to full

Here is my scenerio: A developer wants to switch recovery modes from Full
to Simple and then back to Full after his nightly batch cycle for two
databases. Will I have to take two backups a Full at the start of the
nightly batch cycle and then another backup after switching to Simple, right
?
Is this possible and will I be able to recover successfully? and what are
the pros and cons? Does anyone have a better idea?
Thanks,
Katherine
The recovery mode will be switched during the STARS/TMS Batch Cycle from
“Full” to “Simple” and then back to “Full” after the successful
completion of
the batch processes. Changing the database mode will require that there are
two daily back-up so that in a disaster the transaction logs can be applied.If you switch to simple and then back to full, then SQL Server would have tr
uncated the log while in
simple. This mean that if you do log backups when back in full, you cannot u
se those log backups.
You first have to do a db backup (after going back to full) and then the log
backups you do after
that will be usable. In essence, you have lost point in time restore possibi
lity for the time from
the last log backup before switching to simple until the first log db backup
after going back to
full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...[vbcol=seagreen]
> Here is my scenerio: A developer wants to switch recovery modes from Ful
l
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple, rig
ht?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> “Full” to “Simple” and then back to “Full” after the successfu
l completion of
> the batch processes. Changing the database mode will require that there a
re
> two daily back-up so that in a disaster the transaction logs can be applied.[/vbco
l]|||The full database backup before the recovery model change is redundant. One
method to address recovery in your scenario is:
- backup log
- change recovery model to SIMPLE
- execute batch process
- change recovery model back to FULL
- backup database
This will allow you to recover to any point in time from the last full
backup until the change to SIMPLE recovery. Your potential data loss are
changes during the batch process (between the SIMPLE recovery model change
and the database backup).
Hope this helps.
Dan Guzman
SQL Server MVP
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from
> Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple,
> right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> "Full" to "Simple" and then back to "Full" after the successful completion
> of
> the batch processes. Changing the database mode will require that there
> are
> two daily back-up so that in a disaster the transaction logs can be
> applied.

Can I switch recovery model from full to simple and back to full

Here is my scenerio: A developer wants to switch recovery modes from Full
to Simple and then back to Full after his nightly batch cycle for two
databases. Will I have to take two backups a Full at the start of the
nightly batch cycle and then another backup after switching to Simple, right?
Is this possible and will I be able to recover successfully? and what are
the pros and cons? Does anyone have a better idea?
Thanks,
Katherine
The recovery mode will be switched during the STARS/TMS Batch Cycle from
â'Fullâ' to â'Simpleâ' and then back to â'Fullâ' after the successful completion of
the batch processes. Changing the database mode will require that there are
two daily back-up so that in a disaster the transaction logs can be applied.If you switch to simple and then back to full, then SQL Server would have truncated the log while in
simple. This mean that if you do log backups when back in full, you cannot use those log backups.
You first have to do a db backup (after going back to full) and then the log backups you do after
that will be usable. In essence, you have lost point in time restore possibility for the time from
the last log backup before switching to simple until the first log db backup after going back to
full.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple, right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> â'Fullâ' to â'Simpleâ' and then back to â'Fullâ' after the successful completion of
> the batch processes. Changing the database mode will require that there are
> two daily back-up so that in a disaster the transaction logs can be applied.|||The full database backup before the recovery model change is redundant. One
method to address recovery in your scenario is:
- backup log
- change recovery model to SIMPLE
- execute batch process
- change recovery model back to FULL
- backup database
This will allow you to recover to any point in time from the last full
backup until the change to SIMPLE recovery. Your potential data loss are
changes during the batch process (between the SIMPLE recovery model change
and the database backup).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from
> Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple,
> right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> "Full" to "Simple" and then back to "Full" after the successful completion
> of
> the batch processes. Changing the database mode will require that there
> are
> two daily back-up so that in a disaster the transaction logs can be
> applied.

Can I restore msdb from another sqlserver 's backup

My msdb,system database, of sqlserver is marked suspected,
I cant recovery it for any method.
Therefore, Can I restore it from another sqlserver's
msdb backup?
Hi,
It is not required to restore the MSDB from another server. Best option
will be drop the MSDB database and run the INSTMSDB.SQL script
from sql server installation directory\install folder.
Follow the below steps:-
1. Login to sql server using query analyzer
2. Drop the MSD database
DROP database MSDB
3. Open the INSTMSDB.SQL file in query analyzer
4. Just execute the script. This create the MSDB database, ALL objects ,
......
5. After the succesful execution you will get a fresh MSDB.
Thanks
Hari
MCDBA
"StarNet" <anonymous@.discussions.microsoft.com> wrote in message
news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> My msdb,system database, of sqlserver is marked suspected,
> I cant recovery it for any method.
> Therefore, Can I restore it from another sqlserver's
> msdb backup?
|||Hi,
We have the same problem with a suspected MSDB.
We tried to drop the msdb but got the message that the database cannot be
droped because it'a a system database.
How can we drop the database anyway and solve our problem?
Best Regards,
Karin W
"Hari Prasad" wrote:

> Hi,
> It is not required to restore the MSDB from another server. Best option
> will be drop the MSDB database and run the INSTMSDB.SQL script
> from sql server installation directory\install folder.
> Follow the below steps:-
> 1. Login to sql server using query analyzer
> 2. Drop the MSD database
> DROP database MSDB
> 3. Open the INSTMSDB.SQL file in query analyzer
> 4. Just execute the script. This create the MSDB database, ALL objects ,
> ......
> 5. After the succesful execution you will get a fresh MSDB.
> Thanks
> Hari
> MCDBA
>
> "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
>
>
|||You should find info on this in KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karin W" <Karin W@.discussions.microsoft.com> wrote in message
news:417700A9-7115-481C-8884-BA26136A8E93@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have the same problem with a suspected MSDB.
> We tried to drop the msdb but got the message that the database cannot be
> droped because it'a a system database.
> How can we drop the database anyway and solve our problem?
> Best Regards,
> Karin W
> "Hari Prasad" wrote:

Can I restore msdb from another sqlserver 's backup

My msdb,system database, of sqlserver is marked suspected,
I cant recovery it for any method.
Therefore, Can I restore it from another sqlserver's
msdb backup?Hi,
It is not required to restore the MSDB from another server. Best option
will be drop the MSDB database and run the INSTMSDB.SQL script
from sql server installation directory\install folder.
Follow the below steps:-
1. Login to sql server using query analyzer
2. Drop the MSD database
DROP database MSDB
3. Open the INSTMSDB.SQL file in query analyzer
4. Just execute the script. This create the MSDB database, ALL objects ,
.....
5. After the succesful execution you will get a fresh MSDB.
Thanks
Hari
MCDBA
"StarNet" <anonymous@.discussions.microsoft.com> wrote in message
news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> My msdb,system database, of sqlserver is marked suspected,
> I cant recovery it for any method.
> Therefore, Can I restore it from another sqlserver's
> msdb backup?|||Hi,
We have the same problem with a suspected MSDB.
We tried to drop the msdb but got the message that the database cannot be
droped because it'a a system database.
How can we drop the database anyway and solve our problem?
Best Regards,
Karin W
"Hari Prasad" wrote:
> Hi,
> It is not required to restore the MSDB from another server. Best option
> will be drop the MSDB database and run the INSTMSDB.SQL script
> from sql server installation directory\install folder.
> Follow the below steps:-
> 1. Login to sql server using query analyzer
> 2. Drop the MSD database
> DROP database MSDB
> 3. Open the INSTMSDB.SQL file in query analyzer
> 4. Just execute the script. This create the MSDB database, ALL objects ,
> ......
> 5. After the succesful execution you will get a fresh MSDB.
> Thanks
> Hari
> MCDBA
>
> "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> > My msdb,system database, of sqlserver is marked suspected,
> > I cant recovery it for any method.
> > Therefore, Can I restore it from another sqlserver's
> > msdb backup?
>
>|||You should find info on this in KB 224071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karin W" <Karin W@.discussions.microsoft.com> wrote in message
news:417700A9-7115-481C-8884-BA26136A8E93@.microsoft.com...
> Hi,
> We have the same problem with a suspected MSDB.
> We tried to drop the msdb but got the message that the database cannot be
> droped because it'a a system database.
> How can we drop the database anyway and solve our problem?
> Best Regards,
> Karin W
> "Hari Prasad" wrote:
> > Hi,
> >
> > It is not required to restore the MSDB from another server. Best option
> > will be drop the MSDB database and run the INSTMSDB.SQL script
> > from sql server installation directory\install folder.
> >
> > Follow the below steps:-
> >
> > 1. Login to sql server using query analyzer
> > 2. Drop the MSD database
> >
> > DROP database MSDB
> >
> > 3. Open the INSTMSDB.SQL file in query analyzer
> >
> > 4. Just execute the script. This create the MSDB database, ALL objects ,
> > ......
> >
> > 5. After the succesful execution you will get a fresh MSDB.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> > news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> > > My msdb,system database, of sqlserver is marked suspected,
> > > I cant recovery it for any method.
> > > Therefore, Can I restore it from another sqlserver's
> > > msdb backup?
> >
> >
> >

Can I restore msdb from another sqlserver 's backup

My msdb,system database, of sqlserver is marked suspected,
I cant recovery it for any method.
Therefore, Can I restore it from another sqlserver's
msdb backup?Hi,
It is not required to restore the MSDB from another server. Best option
will be drop the MSDB database and run the INSTMSDB.SQL script
from sql server installation directory\install folder.
Follow the below steps:-
1. Login to sql server using query analyzer
2. Drop the MSD database
DROP database MSDB
3. Open the INSTMSDB.SQL file in query analyzer
4. Just execute the script. This create the MSDB database, ALL objects ,
.....
5. After the succesful execution you will get a fresh MSDB.
Thanks
Hari
MCDBA
"StarNet" <anonymous@.discussions.microsoft.com> wrote in message
news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
> My msdb,system database, of sqlserver is marked suspected,
> I cant recovery it for any method.
> Therefore, Can I restore it from another sqlserver's
> msdb backup?|||Hi,
We have the same problem with a suspected MSDB.
We tried to drop the msdb but got the message that the database cannot be
droped because it'a a system database.
How can we drop the database anyway and solve our problem?
Best Regards,
Karin W
"Hari Prasad" wrote:

> Hi,
> It is not required to restore the MSDB from another server. Best option
> will be drop the MSDB database and run the INSTMSDB.SQL script
> from sql server installation directory\install folder.
> Follow the below steps:-
> 1. Login to sql server using query analyzer
> 2. Drop the MSD database
> DROP database MSDB
> 3. Open the INSTMSDB.SQL file in query analyzer
> 4. Just execute the script. This create the MSDB database, ALL objects ,
> ......
> 5. After the succesful execution you will get a fresh MSDB.
> Thanks
> Hari
> MCDBA
>
> "StarNet" <anonymous@.discussions.microsoft.com> wrote in message
> news:9e5b01c4792d$3cf98050$a601280a@.phx.gbl...
>
>|||You should find info on this in KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karin W" <Karin W@.discussions.microsoft.com> wrote in message
news:417700A9-7115-481C-8884-BA26136A8E93@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have the same problem with a suspected MSDB.
> We tried to drop the msdb but got the message that the database cannot be
> droped because it'a a system database.
> How can we drop the database anyway and solve our problem?
> Best Regards,
> Karin W
> "Hari Prasad" wrote:
>