Post reply

Backup and Restore as failover

  • August 3, 2007 at 9:37 am

    #153726

    I have backup and restore job for failover.

    First step of job to backup the production database and then copy the backup file to failover server

    Second step is to restore the backup file as read only on failover server (in case of production fail)

    The good part of this process is it's work flawlessly for 4 to 5 days.

    The bad part of this process is some time (once in week) fail to restore the database on failover database because the backup file copied from production to the failover server is not in good format.

    I have check size of backup file on both production and failover server it's same but when i ran the restore verifyonly command on both production and failover server on the same backup files. Production server backup file is valid but copied backup file on failover is not in right format that why it can not restore here is the error i get:

    Msg 3242, Level 16, State 2, Line 1

    The file on device 'R:\SQL_Backup\DBNAME\Databases\DatabaseName_2007-08-03_02-11-59.BAK' is not a valid Microsoft Tape Format backup set.

    Msg 3013, Level 16, State 1, Line 1

    VERIFY DATABASE is terminating abnormally.

    So I am suspecting is the file get corrupted while it's copy but size are same. Both production and failover server drives are on SAN. Why my suspicion is strong because after this error when I copy the file manually to overwrite the file and ran the verifyonly command it’s good.

    I have report this problem to our network team but they say it’s because of to much data copy activity during the night but I can understand that too much data copy will take longer time but not corrupt the file. I also proved them by presenting results of verifyonly command but they are helpless right now.

    Any one has any idea what causing this problem.

    Thanks in advance.

     

  • August 6, 2007 at 4:29 am

    #724776

    Why do you want run two jobs and take the headache. You can try for

    Log shipping, Replication or SQL Server cluster for the above issue.

    For the issue you are facing I think you should look into this KBs.

    http://support.microsoft.com/kb/905616/en-us

    http://support.microsoft.com/kb/290787/en-us

    Minaz

    "More Green More Oxygen !! Plant a tree today"

  • August 6, 2007 at 10:28 am

    #724907

    It sounds to me that the two jobs aren't synced.  If the backup is still being copied when the restore job starts, you will get this failure although I'm not even sure if you would be able to verify the size of a file if it is still being copied.  I know that if you look at the file in windows explorer while it is being copied, it will have the same size as the master backup. 

    The next time it happens, try to run the restore job manually.  If it works, then this is probably what is happening.  This will probably only be an issue if your servers are not sitting on the same network and if your database is relatively large.  If this is the problem, then you can tell the job to try N times after N minutes which will give the backup more time to copy, or you can call the restore job from the backup job by making it a linked servers (for example.  There are several other ways to do this).  For simplicity, just telling the job to keep trying is the easiest.

    I agree with the first opinion though from Minaz.  With a logshipping solution your databases can be snyced more often which gives you a much better chance of recovering the database to either the point of failure or at least some reasonable point of time before that (15 minutes seems to be the defacto standard).  If you can't afford any loss (like a bank with its credit card transactions) then logshipping isn't a solution for you either.  I assume that you are not in that situation however.  But if you are, look into replication.

  • August 7, 2007 at 8:39 am

    #725165

    Thanks for reply,

    I have every steps in one SP backup,Copy and restore one after one.

    So i think last step restore will not start before copy statment finish so this is not the case.

    As i have mention file size are same but when i ran restore verifyonly on both backup file the copied backup file is not valid backup. but the one on originial location are good.

    We are using 2000 Standard of SQL so log shipping is not the option.

  • August 8, 2007 at 8:08 am

    #725444

    Why copy the file?  Restore from the backup directly without copying it first. 

Viewing 5 posts - 1 through 4 (of 4 total)

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