backup

Hello, I have a backup plan as below:
A. Everyday midnight
Step 1.
USE myDB
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDB_Log, 1)
Step 2.
USE myDB
SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
B. Every 2 hours
USE myDB
SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
BACKUP LOG myDB TO DISK = @.db_myDBlog
Can I move Step 1 after Step 2( backup database first then shrinkthe dblog)?
Any suggestions about this plan? Thanks.Hi,
No need to shrink the file daily. To restrict the LDF growth you could
perform the transaction log backup every 30 minutes.
This will ensure the LDF will not grow heavily.
So :-
1. Do a full database backup daily once at midnight
2. Do a transaction log every 30 minutes.
Thanks
Hari
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:eUOSH6irFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Hello, I have a backup plan as below:
> A. Everyday midnight
> Step 1.
> USE myDB
> BACKUP LOG myDB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(myDB_Log, 1)
> Step 2.
> USE myDB
> SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
> BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
> B. Every 2 hours
> USE myDB
> SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
> BACKUP LOG myDB TO DISK = @.db_myDBlog
> Can I move Step 1 after Step 2( backup database first then shrinkthe
> dblog)?
> Any suggestions about this plan? Thanks.
>
>
>|||Thanks for the quick response.
"Hari Pra" wrote in message :
> No need to shrink the file daily. To restrict the LDF growth you could
> perform the transaction log backup every 30 minutes.
> This will ensure the LDF will not grow heavily.
is it shrink file a bad thing? what situation shrink file is good?

> So :-
> 1. Do a full database backup daily once at midnight
> 2. Do a transaction log every 30 minutes.
What's diff between 30 minutes and 2 hours(except the Frequency)?
Please advice.|||> is it shrink file a bad thing? what situation shrink file is good?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> What's diff between 30 minutes and 2 hours(except the Frequency)?
You do more modification during two hours compared to 30 minutes. I.e., you
produce more log records
during a longer time period. Also, more frequent backups allow you to lose l
ess data in the unlikely
event...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:e$Sm8IjrFHA.3884@.TK2MSFTNGP11.phx.gbl..
.
> Thanks for the quick response.
> "Hari Pra" wrote in message :
> is it shrink file a bad thing? what situation shrink file is good?
>
> What's diff between 30 minutes and 2 hours(except the Frequency)?
> Please advice.
>
>
>
>|||Thanks Tibor,

> You do more modification during two hours compared to 30 minutes. I.e.,
> you produce more log records during a longer time period. Also, more
> frequent backups allow you to lose less data in the unlikely event...
>
Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up log)?|||Whether a log backup will be noticable to users depends on the load of the s
erver and also your disk
layout (if you separated the tlog file, RAID config etc). But if you do log
backup every two hours,
it will take 4 times as long time as if you do it every 30 minutes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl..
.
> Thanks Tibor,
>
> Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up
> log)?
>
>
>|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:empQRttrFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Whether a log backup will be noticable to users depends on the load of the
> server and also your disk layout (if you separated the tlog file, RAID
> config etc). But if you do log backup every two hours, it will take 4
> times as long time as if you do it every 30 minutes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl...
>