Log shipping failed - Restoring portion

  • As mentioned on my first post all three can provide DR but they are all different, it all depends what you need from a DR POV. You are best looking into all three methods in BOL or googling high availability options for SQL and choosing a method which best fits your recovery time objectives and acceptable data loss interval.

    What I would say is log shipping is not used for reporting it is a high availability option which can subsequently be used for reporting.

    Have you not considered database snapshots for reporting? Do you really need real time reporting, everytime someone wants a real time report in our organisation they have to have a fully justified business case into why they want it which gets reviewed by the board, CTO and DBA's before we allow it on live, otherwise it gets reported on from the data warehouse which is a day behind.

    What I would say is 5 minutes for a reporting log shipping environment is to quick, if you want 5 minutes you will need to ensure that all your users know that every 5 minutes they will be kicked out from the secondary database and you will need to ensure that all your reports run in 5 minutes or less otherwise you will get a lot of angry users. I would say an hour interval would be better suited for this.

    Also will you be log shipping to the same server or a different server in production? Have you thought about the licencing implications about running queries on a secondary server?

  • anthony.green (6/24/2011)


    Yep exactly what I thought, its because you dont have the "disconnect users................" setting checked, which I detailed in my first post and the one above.

    What are your intentions for using logshipping?

    Is it to provide a database which is 5 minutes away from live for reporting?

    Is it to provide disaster recovery?

    Or is it for both?

    Hi, it works after i checked "disconnect users...." thanks

  • ic ic...those are mainly for Disaster Recovery. May i know what is your backup strategy?

    Currently we have not decided on our RTP and RPO yet. But the brief scenario are as such:

    We will be using 1GB backup tapes to backup the full backup, differential and transaction logs backup. The data averages to a few GBs, but it will tend to grows day by day and so does the transaction logs. So i will need to tackle this issue in the future. The backup software that i will be using is HP Data PRotector which can handles compression. Thanks

  • We have an RTP of 15 minutes with an RTO of 2 hours, for this we use logshipping but we use the no recovery setting as we do not report off the secondary databases as they are in the standby data centre some 50 miles away.

    We backup to local disk and keep 3 days retention on local disk for all full and trn backups which are robocopied to the standby server.

    The backups are sent to the SAN backup area every 15 minutes where they are kept for 3 months, at the end of each 3 month period the last days worth of data on the SAN backup area is copied to tape which is then sent off site and housed in a 3rd parties warehouse for 7 years to ensure that we can comply with regulations which are set by the tax man.

    Personally the only tools I would use for backing up the databases are SQL itself with compression, LiteSpeed from Quest or SQL safe from Idera, I have not had any experience with Red-Gates SQL Backup so I cannot comment on that.

    Can I ask what is the reason for using HP Data Protector?

  • anthony.green (6/24/2011)


    We have an RTP of 15 minutes with an RTO of 2 hours, for this we use logshipping but we use the no recovery setting as we do not report off the secondary databases as they are in the standby data centre some 50 miles away.

    We backup to local disk and keep 3 days retention on local disk for all full and trn backups which are robocopied to the standby server.

    The backups are sent to the SAN backup area every 15 minutes where they are kept for 3 months, at the end of each 3 month period the last days worth of data on the SAN backup area is copied to tape which is then sent off site and housed in a 3rd parties warehouse for 7 years to ensure that we can comply with regulations which are set by the tax man.

    Personally the only tools I would use for backing up the databases are SQL itself with compression, LiteSpeed from Quest or SQL safe from Idera, I have not had any experience with Red-Gates SQL Backup so I cannot comment on that.

    Can I ask what is the reason for using HP Data Protector?

    By the way, how do you calculate RTO as 2 hours or is just a rough estimation? Did you actually time how long does it takes to restore the database?

    The reason they are using HP Data protector is that they have already purchase the software and they want to utlise this. So do you recommend that i

    1) Create a job schedule daily every 15 mins to backup Transaction log

    2) Create a job schedule daily at 9am to backup database differential

    3) Create a job schedule weekly at 9am full backup on every monday 9am

    all backups to the 1GB tape. But we have around 200 over such tapes to be used in all kinds of backups.

  • I cannot tell you how to setup your backups, that varies between companies as they will all have a slightly different requirements but your backups full/diff should be done as out of hours as you possibly can, we are a 24/7/365 company so we dont have out of hours but the quiet time is around 1am so all our full backups are done at this time.

    The RTO of 2 hours is the objective not the actual recovery time, until you know the RTO and the RTP you cannot fully impliment a disaster recovery solution, while you will always endovur to meet the RTO it may not always be possible, we have had recovery times of between 1 hour to a day depending on the scale of the disaster, especially when both primary and secondary sites went offline.

  • anthony.green (6/24/2011)


    We have an RTP of 15 minutes with an RTO of 2 hours, for this we use logshipping but we use the no recovery setting as we do not report off the secondary databases as they are in the standby data centre some 50 miles away.

    We backup to local disk and keep 3 days retention on local disk for all full and trn backups which are robocopied to the standby server.

    The backups are sent to the SAN backup area every 15 minutes where they are kept for 3 months, at the end of each 3 month period the last days worth of data on the SAN backup area is copied to tape which is then sent off site and housed in a 3rd parties warehouse for 7 years to ensure that we can comply with regulations which are set by the tax man.

    Personally the only tools I would use for backing up the databases are SQL itself with compression, LiteSpeed from Quest or SQL safe from Idera, I have not had any experience with Red-Gates SQL Backup so I cannot comment on that.

    Can I ask what is the reason for using HP Data Protector?

    Just to check with you on the backup strategy, ur company backup the database to the local disk for all full and trn backups which are then robocopied to the standby server. <Why don't you backup to the SAN directly rather than robocopied to the standby server and from there backup to the SAN?>

    So after the data is in the SAN, you do a backup to tapes and sent it off site? so it would be as below:

    Database -->backup --->Local Disk --->Standby Server--> SAN Backup --> Tapes --> offsite

    Is it true?

    We were thinking of implementing SAN Replication to offsite location and was planning how to go about it.

  • database backups to local backup disk

    backup file is copied to remote server and to NAS

    backup disk and NAS copy are backed up to the virtual tape device

    every 1/4 year one full backup is written to physical tape and stored off site for 7 years

    we do backup direct to the SAN, the local backup drive is in a RAID6 raid group with multiple LUNS on the RG assigned to different hosts which is on the SAN

    we dont do SAN to SAN replication as all of our backups happen at the same time so if the SAN replicated the LUN you are talking about replicating over 10TB of data a night at exactly the same time so we use robocopy to stagger the copy times.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply