cancel
Showing results for 
Search instead for 
Did you mean: 

Best Practice for Backing Up SQL 2012 Full Models

navar_holmes
Level 5

My Backup is 2008 R2 running BE 2012.

My MS SQL is 2012 R2 running SQL 2012.

I have both simple and full model DBs.

I have two main jobs: One for Full DBs and one for Simple DBs.

 

Backing up the simple BDs is pretty right forward.

Just one Full backup job.

Then a Duplicate to tape job.

 

The full model DBs is where it get vague.

I have 8 Full model DBs and three of them generate a large amount of T-Logs.

So what is the best practice to keep the T-Logs from growing to large?

Right now I have a daily Full (at 8pm) which is then dupicated to tape.

A single daily Log Incremental (at 11pm) No dupicate to tape.

 

Questions:

Should I add a differental and where should it go in the schedule?

I have one BDs that need to be backed up every four hours between 6am and 6pm.

1 ACCEPTED SOLUTION

Accepted Solutions

pkh
Moderator
Moderator
   VIP    Certified

To prevent the transaction logs from growing, you need to truncate them by running log backups.  What you are doing at the moment is correct.

You only use differential backup when your full backup takes so long that it exceeds your backup window.  If you are not experiencing any problems with your full SQL backups then there is no need for differential backups.

For the single database that you need to backup every four hours between 6am and 6pm, you can use differential backups for these.  You should still have your regular daily full and log backup.  To schedule your 4-hourly differential backups, see my article below

https://www-secure.symantec.com/connect/articles/be-2012-run-job-certain-hourly-intervals-between-certain-hours-day

If you upgrade to BE 2014, then it is easier to schedule the 4-hourly backups.

View solution in original post

2 REPLIES 2

VJware
Level 6
Employee Accredited Certified

BE 2012 supports Win 2012 but not Win 2012 R2. Recommended to use BE 2014 which supports this OS.

No need to add the differential, instead you can run the incremental (log) at more frequent intervals. Best to check with a SQL DBA as to how to often the transaction logs should be truncated and accordingly you can set the incremental's (log) schedule.

pkh
Moderator
Moderator
   VIP    Certified

To prevent the transaction logs from growing, you need to truncate them by running log backups.  What you are doing at the moment is correct.

You only use differential backup when your full backup takes so long that it exceeds your backup window.  If you are not experiencing any problems with your full SQL backups then there is no need for differential backups.

For the single database that you need to backup every four hours between 6am and 6pm, you can use differential backups for these.  You should still have your regular daily full and log backup.  To schedule your 4-hourly differential backups, see my article below

https://www-secure.symantec.com/connect/articles/be-2012-run-job-certain-hourly-intervals-between-certain-hours-day

If you upgrade to BE 2014, then it is easier to schedule the 4-hourly backups.