SQL Backup job with backup Exec

Posted on 2014-07-24
Last Modified: 2014-07-31
Hi Experts,

I make my SQL backups with BackupExec 2014.
For a fully backup of the SQL Databases what is the best way.
I have FULL and INCREMENTAL and LOG backups.

When I backup the full SQL Instance, is it also needed to backup the mdf and ldf files ?
Question by:Eprs_Admin
    LVL 51

    Accepted Solution

    use sql server to create the backups. easy to schedule and then backupexec can do the physical copy of the backup files to avoid any locking conflicts or exclusive access (yes, backup exec is meant to be sql friendly, but the tools are readily available within sql server for creating DB backups).

    you will always need a FULL backup and that does both the transaction log (ldf) and database file (mdf).

    you will always need LOG backups at regular intervals (depends on your DR plan as to frequency - maybe hourly).

    log backups are crucial with FULL RECOVERY DB's (default recovery model) for point in time recovery and to keep the transaction log healthy.

    questionable if differential / incremental backups are required. Typically only on large DB's where a part of the DB is highly active to cut down time spent doing FULL backups.

    But if you have time overnight, then best to do FULL each night then hourly transaction log backups during the day.

    Have a read of :
    LVL 32

    Assisted Solution

    Depending on how busy your system is, you may want a different backup strategy. For example, the backups on the main databases where I work consist of a Full backup every night, log backups every 15 minutes, and an incremental backup every 4 to 6 hours.

    The incremental backup is very useful if you have frequent log backups.  An incremental backup is a snapshot of everything that has changed in the database since the last full backup, whereas a log backup is a record of all of the changes since the last full backup, incremental backup, or log backup.  So, if it has been 20 hours since your last full backup and you have a failure, you could restore 20 log files, or (assuming every six hour incremental backups) one incremental backup and two log backups.  This is even better with every 15 minute log backups such as we use.  At 20 hours out, we have 80 log backups to restore, or one incremental backup and 7 log backups.  Personally, I know which one I would prefer to use.

    mark willis is correct - use SQL Server to make the backups onto a local drive, then Backup Exec to backup to tape, external disk arrays,  or whatever destination you choose.

    Featured Post

    Too many email signature updates to deal with?

    Are you constantly visiting users’ desks making changes to email signatures? Feel like it’s taking up all of your time? Wish you could manage all signatures from one central location, easily design them and deploy them quickly to users? Well, there is an easy way!

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now