Solved

Can you back up a Log Shipping Database?

Posted on 2008-10-20
7
454 Views
Last Modified: 2012-08-13
Hi All

I have a backup issue which I want to try an resolve.

Basically we have a 'log shipped' copy of our SQL Servers off site which are approximately 15mins behind the Production (On Site) Servers. We also backup a file backup (.bak file) of our database off site directly to tape and this takes about 24hrs+.

Apparentely our company requires a 'warm copy' and a file copy off site, of our production server.

So what I would like to do is try and backup the 'log shipped' database straight to tape - if it is possible?

If I can do this then on a local LAN our backup takes about 4 hrs to tape.

Can anyone help or give me any advice?

Cheers

Gopher
0
Comment
Question by:Gopher1976
7 Comments
 
LVL 4

Expert Comment

by:randy_knight
Comment Utility
why not just copy the logs to tape as well as apply them to the log shipped db?
0
 
LVL 1

Author Comment

by:Gopher1976
Comment Utility
That is one of our ideas - but if one of the logs are corrupt then they will not restore.

The end result is simply that we need to get a full backup of a database to an off site tape drive as quickly as possible.
0
 
LVL 23

Expert Comment

by:bhanukir7
Comment Utility
Hi

what is the kind of budget you intend to spend on this. If you are willing to spend on this then you should look at CA xosoft which can do your current log shipping and backup the same data using CA ARCserve backup.

You also have a High availability solution which can bring your SQL server online in CA xosoft.

This is if you intend to invest on a solution. Update if you currently have a backup solution

bhanu
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:Gopher1976
Comment Utility
Hi Bhanu

I think I have a solution to my problem which to be honest is going away from the original question but the results are the same or similar - I just need to confirmation that I am either doing the right thing or going mad!!!!!!!

Ok, basically we have no budget to improve our backup solution as a few years ago we invested in BackupExec licenses (with no SQL license) so they are not keen to spend any more. So what I have decided to do is:

Saturday - Full backup and transfer off site with log backups.
Monday - Full Backup / Differential Base and Logs throughout the day - At the end of the day transfer off site.
Tuesday - Log backups during the day / Differential in the evening with transfer off site.
Wednesday -  Log backups during the day / Differential in the evening with transfer off site.
Thursday - Log backups during the day / Differential in the evening with transfer off site.
Friday - Log backups during the day / Differential in the evening with transfer off site.

My theory is, basically, that after the full backup / Differential Base has completed and transfered off site (approx 36 hours), the rest of the week will be filled with logs and differential backups. These should be smaller and transfer quicker.

So in the event of a diaster we only need to restore the full backup, last differential and then the final logs!

How does that sound??

Thanks for your time

Gopher
 
0
 
LVL 21

Accepted Solution

by:
mastoo earned 250 total points
Comment Utility
I didn't quite follow what your setup is so I kept quiet, but what you describe is close to what we do and I would answer yes.  The only gotcha I can think of is whether any non-logged operations happen, and if you run an index rebuild job.  We do the rebuild on weekends, which causes a log that is 3x the size of the database - so instead we truncate the log, do a full backup, and start log shipping from that full backup.
0
 
LVL 23

Assisted Solution

by:bhanukir7
bhanukir7 earned 250 total points
Comment Utility
Hi Gopher,

i tried to understand log shipping a little further so i referred to this article.

http://msmvps.com/blogs/omar/archive/2006/09/15/How-to-setup-SQL-Server-2005-Transaction-Log-Ship-on-large-database-that-really-works.aspx

and i agree with mastoo about going ahead with this part of log shipping.

"Basically we have a 'log shipped' copy of our SQL Servers off site which are approximately 15mins behind the Production (On Site) Servers. We also backup a file backup (.bak file) of our database off site directly to tape and this takes about 24hrs+."

are you running the backup over the WAN to remote site.

i have few queries about your backup methods. When you say backup are you referring to native SQL backups apart from running you log shipping part  or are you referring to the log shipping as backup.

You have mentioned that your backups take almost 24+ hrs.

How much time does your regular log shipping activity run for.

if you have already decided to proceed with the what you have planned go ahead with that plan. But wanted to understand a little more on what you are referring to as Full backup/differential backup.

Is that native sql backup or using a backup software as you are referring to "TAPE"

bhanu
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

11 Experts available now in Live!

Get 1:1 Help Now