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 s
pecified 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...
> alone.
>|||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...[vbcol=seagreen]
halt.[vbcol=seagreen]|||I'm not sure I understand the question. Are you saying you want to use repli
cation for some disaster
recovery scenario instead of backup? If so, don't. If you are looking for hi
gh avability, read
http://www.microsoft.com/technet/pr...oy/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 i
n
> message news:OdGFl0idFHA.960@.TK2MSFTNGP10.phx.gbl...
> specified time.
> news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.phx.gbl...
> halt.
>|||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:[vbcol=seagreen]
> 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...
> news:uC%237rVfdFHA.1456@.TK2MSFTNGP15.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:
>