cancel
Showing results for 
Search instead for 
Did you mean: 

Backup Exec 2014 - Backuping MS SQL with Full Recovery Model & Log Shipping

pmevalenzuela
Level 5

Hi.

I got a MS SQL Database with a Full recovery model. We also implement log shipping to an off site.

 

Been testing BE 2014 & agent for application & database.

 

Hope someone can shed some light on this.

 

Question

1. As I understand for MS SQL Database we can have either a Full, Differential or Full-Copy Backup.

              1.1 Does the Full backup truncate and backup  the MS SQL Logs ?

              1.2 Does the Differential truncate and backup the MS SQL Logs ?

              1.3 Does the Full-copy  truncate and backup the MS SQL Logs ?

 

 

2. As I understand the Full-Copy backup does not affect other backup jobs. For our Database we initially plan just to do a Full-Copy, so we can use the built in MS SQL backup  running independently for period of time.

              2.1 Can I implement a  Full-Copy backup via Backup Exec 2014 and run a seperate backup using th Built in MS SQL backup (at a different schedule) using MS SQL Studio?

                             ---> I get a lot of feedback that this is not the best practice. However, we just need to use this for a time being.

 

3. Can I still implement log shipping even using Backup Exec 2014 to backup my MS SQL Database and logs?

               3.1 Will backup exec affect or mess up my log shipping?

               3.2 Would it be possible to do a full-copy backup and still implement log shipping without changing anything on my database.

               3.3 I would want my log shipping to be automatic without any manual interventions, How should I setup my backups for MS SQL?

 

Thanks,

Paul

 

            

2 ACCEPTED SOLUTIONS

Accepted Solutions

SA_Techie
Level 3

Hi Paul, 

Firstly, Full/Differential/Full-Copy is only meant for Database. It does not backup or truncate transaction logs. If you would like to perform log backups then you should configure another incremental with Log backup selected in SQL option

Here are the answers for your questions 

1. None of the backups you mentioned [Full/Differential/Full-Copy] will backup or truncate logs

2. If you prefer doing Full-Copy backup and SQL native backups, yes you can do it for time being and again that will be only database backup [Logs will not be backed up]

3. Backup Exec will not interrupt your SQL native backups. But If you have scheduled native backups between Backup exec Full and differential then BE backups will be interrupted by SQL native backups. 

http://www.symantec.com/docs/TECH58674

To backup your transaction logs via Backup Exec, need to schedule incremental and choose Log - with truncate option in SQL 

 

SQL_log.JPG

 

I would also suggest to go through this page for better understanding on how to create SQL database and Log backups 

https://www-secure.symantec.com/connect/articles/how-backup-sql-logs-and-truncate-them-be-2012

 

Hope this helps any way !!

View solution in original post

pkh
Moderator
Moderator
   VIP    Certified

Yes.  BE and MS uses the same LSN.  All SQL operations done by BE is done through calls to the SQL API.  BE does not interact with SQL directly.

When you do log shipping it will disrupt the LSN sequence and would cause problem with your backups.

The recommended way is to make a copy of your SQL backup and ship that instead.

BE 2012 - copies of SQL DB.png

A .bak file will be create in the diectory specified.  You can send the .bak file to the remote site and use SQL Studio to restore it.

View solution in original post

9 REPLIES 9

SA_Techie
Level 3

Hi Paul, 

Firstly, Full/Differential/Full-Copy is only meant for Database. It does not backup or truncate transaction logs. If you would like to perform log backups then you should configure another incremental with Log backup selected in SQL option

Here are the answers for your questions 

1. None of the backups you mentioned [Full/Differential/Full-Copy] will backup or truncate logs

2. If you prefer doing Full-Copy backup and SQL native backups, yes you can do it for time being and again that will be only database backup [Logs will not be backed up]

3. Backup Exec will not interrupt your SQL native backups. But If you have scheduled native backups between Backup exec Full and differential then BE backups will be interrupted by SQL native backups. 

http://www.symantec.com/docs/TECH58674

To backup your transaction logs via Backup Exec, need to schedule incremental and choose Log - with truncate option in SQL 

 

SQL_log.JPG

 

I would also suggest to go through this page for better understanding on how to create SQL database and Log backups 

https://www-secure.symantec.com/connect/articles/how-backup-sql-logs-and-truncate-them-be-2012

 

Hope this helps any way !!

pmevalenzuela
Level 5

Thanks for the reply.

 

Just a minor clarrification

 

"3. Backup Exec will not interrupt your SQL native backups. But If you have scheduled native backups between Backup exec Full and differential then BE backups will be interrupted by SQL native backups. "

 

Additional Questions:

 

1. If I just use a Full Copy backup via backup exec, there should be no interruption even if I do a full backup using my SQL native backups ?

--> We would want to keep log shipping. As I understand the only option left if we use backup exec 2014, is the Full-Copy to backup the database and let log shipping truncate the logs.

 

2. Correct me if I am wrong. As I udnerstand log shipping will truncate the MS SQL logs for the specific DB configured for log shipping?

 

3. I forgot to ask, does the full, full-copy or differential backups affect the Log Sequence Number "LSN" ?

 

Thanks,

Paul

SA_Techie
Level 3

Hi, 

 

Full Copy backup will not affect the Log Sequence number. However, Full / Differential backups will reset the LSN value and thats why it affects BE backups when SQL native backups are configured. 

As far my understanding, Log shipping will truncate the logs in the primary server.

 

Thanks !

 

 

pmevalenzuela
Level 5

When you refer to LSN, Log Sequence Number

is the LSN used for Backup Exec 2014 and the MS SQL database the same?

 

MS SQL use LSN to track log changes.

pkh
Moderator
Moderator
   VIP    Certified

Yes.  BE and MS uses the same LSN.  All SQL operations done by BE is done through calls to the SQL API.  BE does not interact with SQL directly.

When you do log shipping it will disrupt the LSN sequence and would cause problem with your backups.

The recommended way is to make a copy of your SQL backup and ship that instead.

BE 2012 - copies of SQL DB.png

A .bak file will be create in the diectory specified.  You can send the .bak file to the remote site and use SQL Studio to restore it.

pmevalenzuela
Level 5

Hi. pkh,

 

I plan to a Full-Copy backup of the database. As I understand this should not affect the LSN.

 

Will I be able to SQL log shipping with Full-copy database backups without any problems?

 

 

Thanks,

Paul

pkh
Moderator
Moderator
   VIP    Certified

Log shipping and full-copy database backups are two different things.

pmevalenzuela
Level 5

Hi. pkh,

 

Yes, I agree with you.

 

Would it be possible to create full-copy database backups & have log shipping running (at a seperate schedule)

 

What type of backup modifies or reset the LSN.

 

I know the full-copy does not affect the LSN. How about full and differential backups of the database (not log backups) ?

 

 

 

Thanks,

Paul

pkh
Moderator
Moderator
   VIP    Certified
Only full-copy will not change the LSN. Full and differential will change the LSN