Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1904
  • Last Modified:

SQL Backup job with backup Exec

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 ?
2 Solutions
Mark WillsTopic AdvisorCommented:
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 : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html
Brendt HessSenior DBACommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now