Solved

Can you back up a Log Shipping Database?

Posted on 2008-10-20
7
456 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

777 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