Solved

Can you back up a Log Shipping Database?

Posted on 2008-10-20
7
460 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 250 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 250 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

695 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