Backup Strategy

I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.

Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.

*** TASK 1 ***

Backup transaction log

/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/

BACKUP LOG [DSS]

TO DISK = N'g:\mssql\backup\log\dss_log.bak'

WITH

INIT

, NAME = N'DSS-Transaction Log Backup'

GO

*** TASK 2 ***

/* This script shrinks the DSS database transaction log file

*/

BACKUP LOG [DSS] with truncate_only

dbcc shrinkfile(DSS_log)

**** TASK 3 ****

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

*** TASK 4 ***

Update statistics on the DSS database

(a) what you need to understand here is... if you want to restore the database in POINT IN Time (eg. just 5 min before the database corrupted), you need to keep the TL Backup Chain. Ie. you should not overwrite the TL Backup in any case. If you don't what Point Time restore , you don't need to keep the TL backup atall. Change the Recovery Model to Simple and take frequen Full Backup, it will control the grwoth of TL.

So your first step , Re-writing the TL Backup need to Re-looked as per the requirement.

(b) IF you want to keep the Datbase in Full recovery and you need to take the TL backup, you should not truncate & shrink the TL frequently. By doing this what will happen is, again your backup chain will fail. IF you want to shrink the TL, after shrinking the first step should be the full backup, all other old TL backups are invalid if you shrink the TL. So create a full backup script run after shrinking the TL if at all you want to shrik TL

(c) If your are keep in the full backup to any other place after backup up its ok.. otherwise, you are verifying after you overwrites the backup file, if the new backup taken is corrupted , then you will end up with no backup

Madhu

|||

Thank you for the clarification Madhu. Your comments have helped me to wrap my mind around how backup strategies work in SQL2005. A full database back up to disk is performed each night and then written to tape. In the event of a failure, we would only lose 24 hours which at this time is acceptable to the company. To provide better recovery I believe I will perform full backups several times throughout the day.

With this said, it would appear that a good strategy for us would be to change the recovery mode to simple, to manage the transaction log files size, and perform full database backups every 2 hours.

Thanks again!