SQL Server Performance

Question about Backup Schedule

Discussion in 'SQL Server 2008 General DBA Questions' started by J Missouri, Aug 5, 2009.

  1. J Missouri New Member

    Just wanted some feedback for some small (2GB) SQL 2008 databases and an answer to a question. My backups look like this...
    8 am - Full Backup
    9, 10, 11 am - Transaction Log Backups
    12 pm - Full Backup
    1, 2, 3 pm - Transaction Log Backups
    and so on.
    First - See any holes? We are comfortable having up to four or five file restore operations used to bring back a down db.
    I plan to shrink the Trans Log file once a week (via the change in recovery and shrikfile operation). Will this break the log chain?
  2. MohammedU New Member

    Welcome to the forum, I hope you get the info needed...
    I don't see any holes in your plan but why do you need FULL backup every four hours? Any special reason?
    You can replace subsequent full backups (12 pm, 4 pm...) with Differential backups....with one full backup daily at 8 am or what ever time suitable...
    Shrinking the log will not break the log sequence/chain unless you change the recovery model from FULL to SIMPLE...
    Why do you need to shrink the log file? It will grow again when it needed which will effect the performance during the growth...
    Still you can shrink the log file then you can do it without changing the recovery model...
    Just run the log backup and then shrink the log file immediately after the log backup...
    HTH
  3. J Missouri New Member

    We ship the full backup files off to a reporting server and development server for use, sometimes twice a day.
    Since the backup log with truncate_only is not an option in 2008 I had read that changing the recovery model and shrinking the file and then changing the model back was the only option. I guess a log backup would do the same by clearing out the inactive portions in the file.

Share This Page