?
Solved

Can you back up a Log Shipping Database?

Posted on 2008-10-20
7
Medium Priority
?
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:randy_knight
ID: 22759695
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
ID: 22759812
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
ID: 22765376
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:Gopher1976
ID: 22780102
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 1000 total points
ID: 22780299
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 1000 total points
ID: 22781991
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…
Suggested Courses

771 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