Database Backup Strategy

Question

Database Backup Strategy

shohelr2003

Hall of Fame

Points: 3535
More actions
November 27, 2012 at 1:47 am

#267186

Dear,
Currently we are taking full database backup twice. There is no log backup in our plan. If transaction log grows larger, we just shrink the log file to 1 MB.
We have already implemented snapshot replication. It is scheduled to occur once in a day.
Please let us know the ins and outs of our database backup strategy. If you have better suggestion, please let me know.
Thank in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

There is no good or bad backup strategy, if it meet yours company RTO (recovery time objective) and RPO (recovery point objective) then it is good. But why you truncate log, and why to 1mb? If there are any dml operations, that cause to grow log, than this is too small amount and bad idea

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

shohelr2003 (11/27/2012)
There is no log backup in our plan. If transaction log grows larger, we just shrink the log file to 1 MB.

You can consider to change the recovery model to "Simple"

Note that file growth and shrinking is bad for performance. You should pre-size the files.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

Actually transaction log file keeps on growing, so my database file also grows which consumes more disk spaces. To make database size smaller, I shrink log file that serves my purpose.

If I set recovery model SIMPLE then how would I be benefited?

Please clarify me these things.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Simple recovery will mark the log as re-usable as soon as a checkpoint happens within the DB which will prevent the need for regular log backups and will stop the log from growing and growing as it sounds like your not doing proper transaction log management.

Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/

Also read the stairway to transaction log management section

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

shohelr2003 (11/27/2012)
Actually transaction log file keeps on growing, so my database file also grows which consumes more disk spaces. To make database size smaller, I shrink log file that serves my purpose.
If I set recovery model SIMPLE then how would I be benefited?
Please clarify me these things.

All inactive VLF's in the transaction log will be truncated upon Checkpoint. Causing the transaction log to stay at a minimum size.

Note: Long running transactions can still fill up your log and and ause it to grow

sanket kokane Hall of Fame Points: 3465 More actions · Answer 6

http://msdn.microsoft.com/en-us/library/ms189275.aspx

http://www.codeproject.com/Articles/380879/About-transaction-log-and-its-truncation-in-SQL-Se

-----------------------------------------------------------------------------
संकेत कोकणे