best recovery mode

hi friends,
what should be the best recovery mode to implement.it is simple,full,bulklog
back.
which should be the efficient .
what is the microsoft recommendations for this
please help
thanks
reddyHi,
We can not differentiate and tell which one is good and which one is bad,
Recover model selection purely depends
on the data inside the database and type of recovery business requires.
FULL :- For production data which requires point-in-time recovery
BULK_LOGGED :- For production with high bulk operations and which do not
need a point-in-time recovery
SIMPLE :- For test and development environments. The recovery can be done
only till the last full backup.
Have a look into "Selecting a Recovery Model" in Books online for more
details.
Thanks
Hari
MCDBA
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:E42CF3FF-F4EC-476C-AFC0-D81428240791@.microsoft.com...
> hi friends,
> what should be the best recovery mode to implement.it is
simple,full,bulklogback.
> which should be the efficient .
> what is the microsoft recommendations for this
> please help
> thanks
> reddysql

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alot
Maryam,
Here is a nice article:
http://insight.zdnet.co.uk/software/...4171-1,00.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>
|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)
|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
..If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>
|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>
|||Correct. But for the first scenario, you also need to perform regular transaction log backups. When
you restore a transaction log backup, you can stop at a certain point in time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to the
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> lose
>

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alotMaryam,
Here is a nice article:
http://insight.zdnet.co.uk/software...34171-1,00.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
.If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>|||Correct. But for the first scenario, you also need to perform regular transa
ction log backups. When
you restore a transaction log backup, you can stop at a certain point in tim
e.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to t
he
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> lose
>

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alotMaryam,
Here is a nice article:
http://insight.zdnet.co.uk/software/developer/0,39020469,2134171-1,00.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
.If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> > Hi
> >
> > What's the benefit usage to select Full recovery model for a database?
> > I know it's better to select simple mode for developement and test
> > enviroment.
> > But I don't know excatly how can the full recovery model be usefull
> > for critical enviroment?
> >
> > Can any body give me an example?
> > Thanks alot
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>|||Correct. But for the first scenario, you also need to perform regular transaction log backups. When
you restore a transaction log backup, you can stop at a certain point in time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to the
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> > maryam rezvani wrote:
> > > Hi
> > >
> > > What's the benefit usage to select Full recovery model for a database?
> > > I know it's better to select simple mode for developement and test
> > > enviroment.
> > > But I don't know excatly how can the full recovery model be usefull
> > > for critical enviroment?
> > >
> > > Can any body give me an example?
> > > Thanks alot
> >
> > short:
> >
> > if your DB is in full recovery model, you will have to backup the log, if
> > you can several times/day. In case of a restore, you probably will not
> lose
> > any data, and you can recovery to the point in time (useful if someone
> > deletes something or some other human error)
> >
> > long:
> >
> > see BooksOnLine (mssql help)
> >
> >
>