+ Post New Thread
Results 1 to 10 of 10
Windows Thread, Backup Exec SQL in Technical; A full SQL backup doesn't backup and truncate logs however from what I understand these logs are only needed if ...
  1. #1

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,161
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181

    Backup Exec SQL

    A full SQL backup doesn't backup and truncate logs however from what I understand these logs are only needed if you want to restore to a particular point in time. Correct?

    If that is the case then will a full daily backup with a single log file backup daily suffice? How do you guys do it?

  2. #2

    Join Date
    Nov 2010
    Location
    California
    Posts
    137
    Thank Post
    0
    Thanked 24 Times in 22 Posts
    Rep Power
    12
    Generally we setup our SQL servers like the following:
    OS Drive
    Databases Drive
    Log File Drive
    Backups Drive

    These are all virtual disks configured on different LUNs (which have different RAID arrays) depending on which it is. We run a full backup daily and transaction log backups every hour. We then backup the data on those drives using Avamar to a location off site.

    The idea being with the daily backup and hourly log files is we only have to manage 1 full backup and then we can use the log file backups to restore up to the last hour it backed up before it went down. We use Avamar to backup all of the drives to an alternate location incase of disaster (we are in a mountain region that frequently has fires, snow, plenty of senior citizen drivers (lol) and everything else lol).

    Having all your bases covered makes you sleep well at night
    Last edited by qcomer; 26th April 2011 at 04:26 AM.

  3. #3

    Join Date
    Dec 2008
    Location
    Essex
    Posts
    2,144
    Thank Post
    1
    Thanked 326 Times in 316 Posts
    Rep Power
    77
    @Edu-IT

    I assume you are using a full recovery model. Therefore, your thought is correct. Simply doing a full backup won't mean log truncation will occur. You have to backup the log files. If you're not concerned about point in time recovery then you can backup the logs to disk and keep for x days which will then delete the logs. Then continue with your normal backups in additon to your transaction logs backups for x days and deletion after x days.

    Sukh

  4. #4

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,161
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    So in this case, the 'full' backup of the database will be sufficient to restore to the time the full backup was taken? The log files won't be needed really unless you're going back to a particular point in time?

  5. #5

    Join Date
    Dec 2008
    Location
    Essex
    Posts
    2,144
    Thank Post
    1
    Thanked 326 Times in 316 Posts
    Rep Power
    77
    correct. Full will only let you recover from your last full successful backup.

  6. #6

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,161
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    So with regards to SQL in general, all the transaction log does is keep a track of the changes that are made to the actual database file?

  7. #7

    Join Date
    Dec 2008
    Location
    Essex
    Posts
    2,144
    Thank Post
    1
    Thanked 326 Times in 316 Posts
    Rep Power
    77
    Correct. Any transaction get's written to logs then is replayed into the DB. So after your full back on 12pm on Mon, you dont need the logs to recover up to 12pm Mon. Between Mon 12pm and Tue 12pm, all information is in the logs and written to the DB. On Tue at 12pm you have another full backup. Now you can recover as pf Mon 12pm or Tues 12pm.

    If you wanted to recover say Mon at 3pm and disregard all other transactions then you cant.

    Also, depends how critical your DB is to you. Say you wanted to recover up to Monday 5pm on a Tue, as you realised transactions after 5pm transactions were wrong. You could only roll back to 12pm Mon and you will lose all the other transaction up to 5pm Mon and would have to make those again (no transaction log backups).

    Sukh

  8. Thanks to sukh from:

    Edu-IT (26th April 2011)

  9. #8

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,161
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    Makes sense now, thanks Sukh! :-)

  10. #9

    Join Date
    Nov 2010
    Location
    California
    Posts
    137
    Thank Post
    0
    Thanked 24 Times in 22 Posts
    Rep Power
    12
    May I ask what your DB is hosting? Our is our SIS system (student information system) and also our transportation system (bus) so we like to be able to restore atleast up until the hour. If any of that data goes missing beyond that it can really cause havoc and be a nightmare.

    Our help desk software runs on SQL also but we dont do nearly that intensive of a disaster recovery strategy on it since it is not as mission critical.

  11. #10

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,161
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    The biggest database is 1.5GB in size so in theory I see no reason why we cannot run a full backup multiple times each day? We backup to external media and my concern with transaction log backups is keeping track of which cartridge has what.

SHARE:
+ Post New Thread

Similar Threads

  1. Windows 2008, SQL 2008 and Backup Exec 12
    By joe90bass in forum Windows
    Replies: 2
    Last Post: 5th May 2010, 02:49 PM
  2. Backup Exec 10d and MS SQL 2008
    By Duke in forum Windows
    Replies: 4
    Last Post: 12th April 2010, 10:21 AM
  3. Replies: 4
    Last Post: 6th January 2010, 11:59 AM
  4. Replies: 4
    Last Post: 3rd November 2009, 01:05 AM
  5. ...Install MS SQL 2005 and Veritas Backup Exec??
    By LordGrey in forum How do you do....it?
    Replies: 3
    Last Post: 27th September 2006, 05:44 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •