SQL Server Performance

Update SQL Backup

Discussion in 'General DBA Questions' started by fauzanf, Jul 13, 2009.

  1. fauzanf New Member

    Dear My Friends,
    I have a question about backup database.
    i have been create a database and running for more than one month. Suddenly, i have to update the database... From varchar to Real.
    It's not too complicated to update the current running database. But, if i restore the backup file to the current database. The field is change back to varchar, not real.
    How can i update the backup file...? So if i restore the backup file, the field is still real not varchar...
    Need advise please.
    Thanks & Regards,
  2. Luis Martin Moderator

    I can't follow you.
    Did you backup the database after change from varchar to real?
    Let me tell you some alternatives:
    1) You change from varchar to real, no backup after that. Now you restore to actual database then you loose your change. And that is ok!.
    2) You change from varchar to real, backup after that. Now you restore to actual database then your change is there. If you want to back to the old database (varchar), you have to find and older backup. And that is Ok. too!.
    What is you case?
  3. Adriaan New Member

    If you restore a backup, it not only restores the data, but before that it creates the data structure from the ground up too.
    So if you have to restore an older backup, then you have to apply the changes to the table definition once again.
  4. satya Moderator

    As you got the correct reply, what I want to ask is why do you want to restore the backup if you are happy with your current update?
  5. fauzanf New Member

    Thanks for replying my post,
    I create a database, letsay dbTest... dbTest has one table, Table1. Table1 has 2 Fields,
    1. Item -- Varchar(50)
    2. Length -- Varchar(50)
    This table has been run for about 3 months and backup schedule is daily.
    After three months, i have to change the datatype of Length from varchar to Real.
    OK, now i change the datatype. But, if i restore the database from the previous backup file, the datatype of length is change back to varchar not real.
    My Question is, how can i update the backup file...? So, if i restore from the previous backup file. The datatype of Length is still real not varchar.
    Need advise please.
  6. johnson_ef Member

    HI Fauzan,You have taken a backup and kept somewhere as a daily practice, okay,Now you are modifying the table. This change is not going to reflect in your backup file (which you have already taken).Now your question is, how the changes in the database will reflect in your database.It so simple. Please perform one more backup. This new backup file will have the changes, which you have done with the database.In brief; the full backup file will have all the changes, what had happened till the backup done. I hope you understand it.Regards-Johnson
  7. Luis Martin Moderator

    I'm afraid you don't understand the previous answers.
    Now back to your question: "My Question is, how can i update the backup file...?"
    Easy, run the backup again.
  8. fauzanf New Member

    Hello My Friends,
    Thank for all.
    I really understand about your post.
    But, if i want to restore the previous backup... Let say, 3 days after the database running.
    The datatype will change back to the previous datatype, right...?
    So, it means... The previous backup file, before i change the datatype... Can't be updated.
    Is it correct...? Tell me if i'm wrong...
  9. Luis Martin Moderator

    Let me say this:
    Day 1)
    You have one user database: RACING.
    Also you backup RACING every day at 10PM.
    Day 2)
    Now, the next morning you change one datatype from Varchar to Real.
    At 10PM the backup run again overwriting RACING backup.
    Day 3)
    At 9AM you need to restore the yesterday backup.
    After restore, your change is there.
    Now if the Day 2) backup don't run for any reason, then you have to restore the Day 1) backup. In this case, the datatype remain varchar.
  10. fauzanf New Member

    Hello Brothers... [H]
    if i have manual backup day by day and backup file sort by backup date.
    I'm really sure the backup file can't be updated right...?
    Thanks & Regards,
  11. moh_hassan20 New Member

    So, it is necessary document changes in schema , to know which valid backup for that schama can be used.
  12. Adriaan New Member

    You cannot update the backup itself.
    You can restore a backup, then apply the update to the restored backup. If you want to keep a backup of this restored database including the update, you will have to backup this updated database once more.

Share This Page