Results 1 to 5 of 5

Thread: diff backup help

  1. 03-22-2006, 07:31 AM #1
    abhijitnt
    abhijitnt is offline Registered User
    Join Date
    Mar 2006
    Posts
    2

    diff backup help

    Dear all
    I am new to the MS SQL, my problem is as follows.
    I am having a online database on sql 2k.
    every 15 days we have to give payout from our system, so we
    have a offline server in our office, we take the complete backup of that day & restore the same on the offline server,
    & start the payout process.
    The problem is that the full backup is a big file & take a lot of time for downloading from online server.
    is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download.
    but my offline backup is 15 days old, will that update all the records or not?


    regards
    Abhijit
    Reply With Quote Reply With Quote
  2. 03-22-2006, 09:13 AM #2
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    How often do you do full db backup? If you do db full backup every 15 days, diff. backup file maybe little bit smaller depends on how do you handle data in the db. Otherwise, you can't apply diff. backup on offline server.
    Reply With Quote Reply With Quote
  3. 03-22-2006, 03:35 PM #3
    nosepicker
    nosepicker is offline Registered User
    Join Date
    Dec 2004
    Posts
    502
    You can only restore a differential backup after you have restored a full backup. And, in SQL2000, you can only restore a differential backup if you leave the restore of the full backup in a "no recovery" (unusable) state. Therefore, if you have restored a full backup from 15 days ago, and that restore is in a usable (recovery) state, you won't be able to apply a differential backup to it. You would have to start over again with a full backup and restore.
    Reply With Quote Reply With Quote
  4. 03-23-2006, 01:46 AM #4
    abhijitnt
    abhijitnt is offline Registered User
    Join Date
    Mar 2006
    Posts
    2
    Thanks guys for your feedback.
    what you are saying is correct, what I am looking at is this-

    currently we have a scheduled full backup everyday at 2AM,2PM & 7PM, now what we are planning is we will not disturb the schedule & after full backup of 2AM, we will schedule a diff
    backup at 3AM daily & after that we will copy the full backup file of 2AM only once in offline & after that we will just take the diff backup file download erveryday & restore the same,
    will this is OK without loosing anything.
    Reply With Quote Reply With Quote
  5. 03-23-2006, 10:00 AM #5
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Don't think so since you have to put target db in norecovery mode to restore from diff. backup, and you can't access db that is in norecovery mode.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules