24/7 backup strategy

[guymachine]

Hi,

How would i go about using a backup strategy combining full, differential and transaction logs to insure no loss of data at any time? My database is small (50MB).

This is the current option i am using:

- trunc. log on chkpt. = true
- every 2 hours a script forces a checkpoint
- every 2 hours (2 minutes after the checkpoint), a full backup is performed
- the backups for that day are sent to tape at night

Any suggestions or recommendation on this strategy or is there a better/simpler one?

thanks,

Guy

 

[tlbroadbent]

Truncate log on checkpoint destroys any chance of doing transaction recovery. You will only be able to recover to the latest full backup. Checkpoints can occur any time, not just when you issue the command. If you want full recovery capability turn of that option.

We do full backup once each day and rtansaction backups every 30 minutes. On some databases we do differential backups every 4 hours.

See FAQ faq183-1784: How Do I Develop a Backup and Restore Strategy?
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[tlbroadbent]

Please post future questions about SQL Server administration in forum962 - Microsoft SQL Server: Setup and Administration If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[guymachine]

Hi,

thanks for your reply. When you backup the transaction logs every 30 minutes, do you use with_log, truncate_only or no_log?

How long should i keep the backups? After the last full backup?

thanks,

Guy