Recovery Full vs Simple

Hi,
Once in a while I have to do a set of things to avoid the db halts in the
middle of the day to resize the log file as it grows. These are the ones in
the correct order:
1) ALTER DATABASE MyDb SET RECOVERY SIMPLE
2) dbcc shrinkfile(MyDb_log,1)
3) ALTER DATABASE MyDb SET RECOVERY FULL
4) Then allocate a big junk of space for the log file to have enough buffer.
This operation can take a long time.
Is there an alternative to that? I already set up real-time replication and
full backup every night, so I'm also wondering what other benefits that the
log file set in full mode would give me, would someone know?
Thanks!!If you don't require log backups then set recovery mode permanently to
Simple. Set the log file size as big as you need it and then leave it alone.
The one thing NOT to do is regularly shrik the log. Doing so achieves
nothing except harm performance and probably bring your server to a halt.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
David Portas
SQL Server MVP
--|||Zeng wrote:
> Hi,
> Once in a while I have to do a set of things to avoid the db halts in
> the middle of the day to resize the log file as it grows. These are
> the ones in the correct order:
> 1) ALTER DATABASE MyDb SET RECOVERY SIMPLE
> 2) dbcc shrinkfile(MyDb_log,1)
> 3) ALTER DATABASE MyDb SET RECOVERY FULL
> 4) Then allocate a big junk of space for the log file to have enough
> buffer. This operation can take a long time.
> Is there an alternative to that? I already set up real-time
> replication and full backup every night, so I'm also wondering what
> other benefits that the log file set in full mode would give me,
> would someone know?
> Thanks!!
To add to what David said, once you go from Simple to Full recovery, you
have to immediately perform a full database backup to prevent the log
file from continuing to truncate.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In which scenerios/reasons I should have log backups? thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
> If you don't require log backups then set recovery mode permanently to
> Simple. Set the log file size as big as you need it and then leave it
alone.
> The one thing NOT to do is regularly shrik the log. Doing so achieves
> nothing except harm performance and probably bring your server to a halt.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Log backup allow things like:
More frequent backup. Like every 10 minutes or every hour.
Pint in time restore. When you restore from a log backup, you can STOPAT a specified time.
Backup the log of a damaged database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
> In which scenerios/reasons I should have log backups? thanks
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
>> If you don't require log backups then set recovery mode permanently to
>> Simple. Set the log file size as big as you need it and then leave it
> alone.
>> The one thing NOT to do is regularly shrik the log. Doing so achieves
>> nothing except harm performance and probably bring your server to a halt.
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> David Portas
>> SQL Server MVP
>> --
>>
>|||It depends what level of recovery you need. If you believe backing up
once a day meets your needs then maybe you don't require transaction
log backups. In most OLTP scenarios however it's usually unacceptable
for the business to lose a day's work in the event of a disaster. Log
backups mean you can take much more frequent backups during the during
the day and therefore minimize the risk of data loss and downtime.
--
David Portas
SQL Server MVP
--|||If I have continuous replication set up, would it be any beneficial to me?
That is, is there a case where my replication db is bad that I need to
rollback/restore my db back to 10 min before the disaster happens?
thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
> Log backup allow things like:
> More frequent backup. Like every 10 minutes or every hour.
> Pint in time restore. When you restore from a log backup, you can STOPAT a
specified time.
> Backup the log of a damaged database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
> > In which scenerios/reasons I should have log backups? thanks
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
> >> If you don't require log backups then set recovery mode permanently to
> >> Simple. Set the log file size as big as you need it and then leave it
> > alone.
> >> The one thing NOT to do is regularly shrik the log. Doing so achieves
> >> nothing except harm performance and probably bring your server to a
halt.
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >>
> >>
> >
> >|||I'm not sure I understand the question. Are you saying you want to use replication for some disaster
recovery scenario instead of backup? If so, don't. If you are looking for high avability, read
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:ur59lcqdFHA.4040@.TK2MSFTNGP14.phx.gbl...
> If I have continuous replication set up, would it be any beneficial to me?
> That is, is there a case where my replication db is bad that I need to
> rollback/restore my db back to 10 min before the disaster happens?
> thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
>> Log backup allow things like:
>> More frequent backup. Like every 10 minutes or every hour.
>> Pint in time restore. When you restore from a log backup, you can STOPAT a
> specified time.
>> Backup the log of a damaged database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> > In which scenerios/reasons I should have log backups? thanks
>> >
>> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> > news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
>> >> If you don't require log backups then set recovery mode permanently to
>> >> Simple. Set the log file size as big as you need it and then leave it
>> > alone.
>> >> The one thing NOT to do is regularly shrik the log. Doing so achieves
>> >> nothing except harm performance and probably bring your server to a
> halt.
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> David Portas
>> >> SQL Server MVP
>> >> --
>> >>
>> >>
>> >
>> >
>|||It might be worth that you read about Backup/Restore Architecture in Books
On Line. It seems like you are missing some basic knowledge about how backup
works - and why you need to backup...:-).
Using replication might be ok in the case where your database becomes
corrupt, but what if a user makes a mistake in the database? Then this
mistake will be replicated as well, so you can't recreate data from the
replicated database. If you have a backup you can restore to a point in time
and then get data from there.
Regards
Steen
Zeng wrote:
> If I have continuous replication set up, would it be any beneficial
> to me? That is, is there a case where my replication db is bad that I
> need to rollback/restore my db back to 10 min before the disaster
> happens?
> thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
>> Log backup allow things like:
>> More frequent backup. Like every 10 minutes or every hour.
>> Pint in time restore. When you restore from a log backup, you can
>> STOPAT a specified time. Backup the log of a damaged database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> In which scenerios/reasons I should have log backups? thanks
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
>> message news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
>> If you don't require log backups then set recovery mode
>> permanently to Simple. Set the log file size as big as you need it
>> and then leave it alone. The one thing NOT to do is regularly
>> shrik the log. Doing so achieves nothing except harm performance
>> and probably bring your server to a halt.
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> David Portas
>> SQL Server MVP
>> --|||we back up every night as well. Why would it be so important to recover user
mistake within the same day (smaller window of time -> less amount of work
to reconstruct)? If a computer user makes a mistake - such as overwriting a
file on their personal computer, there won't be much to recover, that's
widely accepted. If we have 1000 users and 10% of them eventually want to
recover their mistake, it would be messy. Maybe you are concerned about
system mistake/bug?
If I go to my bank and withdraw a money out of the checking account and
trigger a fee because it goes below certain balance threshold, nobody would
allow me to cover it.
Rolling back entire db to certain point in time and make it production db
won't work well either, there must have been many changes since that point
in time that won't be honored in the rollback.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23LUzwSydFHA.2776@.TK2MSFTNGP10.phx.gbl...
> It might be worth that you read about Backup/Restore Architecture in Books
> On Line. It seems like you are missing some basic knowledge about how
backup
> works - and why you need to backup...:-).
> Using replication might be ok in the case where your database becomes
> corrupt, but what if a user makes a mistake in the database? Then this
> mistake will be replicated as well, so you can't recreate data from the
> replicated database. If you have a backup you can restore to a point in
time
> and then get data from there.
> Regards
> Steen
> Zeng wrote:
> > If I have continuous replication set up, would it be any beneficial
> > to me? That is, is there a case where my replication db is bad that I
> > need to rollback/restore my db back to 10 min before the disaster
> > happens?
> >
> > thanks
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote in message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
> >> Log backup allow things like:
> >>
> >> More frequent backup. Like every 10 minutes or every hour.
> >> Pint in time restore. When you restore from a log backup, you can
> >> STOPAT a specified time. Backup the log of a damaged database.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> > news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
> >> In which scenerios/reasons I should have log backups? thanks
> >>
> >> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> >> message news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
> >> If you don't require log backups then set recovery mode
> >> permanently to Simple. Set the log file size as big as you need it
> >> and then leave it alone. The one thing NOT to do is regularly
> >> shrik the log. Doing so achieves nothing except harm performance
> >> and probably bring your server to a halt.
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
>|||Generally, you don't use backups to recovery from plain user mistakes. If a user deletes an order,
that user call up the customer, admits the mistake and re-enter the order. And learn from that. Or,
in reality use some logged information by the app or on paper to recover.
What you protect is from things like deleting a table by mistake. Or deleting all rows in a table by
mistake. The problem with undoing only certain operations in a database is that the database has a
state from two different points in time. And one operations can very well be depending on another
operation. "We wouldn't have allowed this loan if it weren't for..." I.e., you have an inconsistent
database. You will only allow this if you know your data *very* well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:OiAZWU1dFHA.1384@.TK2MSFTNGP09.phx.gbl...
> we back up every night as well. Why would it be so important to recover user
> mistake within the same day (smaller window of time -> less amount of work
> to reconstruct)? If a computer user makes a mistake - such as overwriting a
> file on their personal computer, there won't be much to recover, that's
> widely accepted. If we have 1000 users and 10% of them eventually want to
> recover their mistake, it would be messy. Maybe you are concerned about
> system mistake/bug?
> If I go to my bank and withdraw a money out of the checking account and
> trigger a fee because it goes below certain balance threshold, nobody would
> allow me to cover it.
> Rolling back entire db to certain point in time and make it production db
> won't work well either, there must have been many changes since that point
> in time that won't be honored in the rollback.
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:%23LUzwSydFHA.2776@.TK2MSFTNGP10.phx.gbl...
>> It might be worth that you read about Backup/Restore Architecture in Books
>> On Line. It seems like you are missing some basic knowledge about how
> backup
>> works - and why you need to backup...:-).
>> Using replication might be ok in the case where your database becomes
>> corrupt, but what if a user makes a mistake in the database? Then this
>> mistake will be replicated as well, so you can't recreate data from the
>> replicated database. If you have a backup you can restore to a point in
> time
>> and then get data from there.
>> Regards
>> Steen
>> Zeng wrote:
>> > If I have continuous replication set up, would it be any beneficial
>> > to me? That is, is there a case where my replication db is bad that I
>> > need to rollback/restore my db back to 10 min before the disaster
>> > happens?
>> >
>> > thanks
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote in message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
>> >> Log backup allow things like:
>> >>
>> >> More frequent backup. Like every 10 minutes or every hour.
>> >> Pint in time restore. When you restore from a log backup, you can
>> >> STOPAT a specified time. Backup the log of a damaged database.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Zeng" <Zeng5000@.hotmail.com> wrote in message
>> > news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> >> In which scenerios/reasons I should have log backups? thanks
>> >>
>> >> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
>> >> message news:VY2dnZpdAtWA1CrfRVn-sw@.giganews.com...
>> >> If you don't require log backups then set recovery mode
>> >> permanently to Simple. Set the log file size as big as you need it
>> >> and then leave it alone. The one thing NOT to do is regularly
>> >> shrik the log. Doing so achieves nothing except harm performance
>> >> and probably bring your server to a halt.
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> David Portas
>> >> SQL Server MVP
>> >> --
>>
>sql