Backup and Restore queries

  • Illegal_Operation

    Default port

    Points: 1448

    Dear all,

    just to check with you. for restoration of database, i would need to perform the following:

    I did a full backup at around 1pm today. and a differential backup at 130pm today, follow by transaction log backup every 5 mins from 130pm onwards.

    If i wish to restore back the information that is at 2pm. I would need to do the following:

    1) Restore the full backup (with norecovery)

    2) Restore the differential backup (with norecovery)

    3) Restore all the transactions logs from 130pm onwards to 2pm (withrecovery)

    Am i right? And also there would be only 3 files. Fullbackup.bak, DiffBackup.bak and Logbackup.bak for the entire backup processes. Am i right ?

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    kesmond (6/20/2011)


    Dear all,

    just to check with you. for restoration of database, i would need to perform the following:

    I did a full backup at around 1pm today. and a differential backup at 130pm today, follow by transaction log backup every 5 mins from 130pm onwards.

    If i wish to restore back the information that is at 2pm. I would need to do the following:

    1) Restore the full backup (with norecovery)

    2) Restore the differential backup (with norecovery)

    3) Restore all the transactions logs from 130pm onwards to 2pm (withrecovery)

    Am i right? And also there would be only 3 files. Fullbackup.bak, DiffBackup.bak and Logbackup.bak for the entire backup processes. Am i right ?

    Yep restoration sequence is right.

    Am i right? And also there would be only 3 files. Fullbackup.bak, DiffBackup.bak and Logbackup.bak for the entire backup processes. Am i right ?

    Its depends upoun your backup plan if you are append all the files then there will be a single log bak file.

    See the bottom of the post .

    What is the sequence to restore the database?

    Backup types

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Illegal_Operation

    Default port

    Points: 1448

    thanks for the prompt reply. But lets say example, i do a full backup on monday and a differential backup daily and transaction log daily for every 5 mins.

    If i need to restore database on friday at 12pm. I have to restore the full backup on monday, follow by all the differential backups from monday to thursday and then from restore all the transaction logs from monday until friday 12pm? Is that correct?

    It would takes hell lots of time trying to recover, am i right? thanks

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    kesmond (6/20/2011)


    thanks for the prompt reply. But lets say example, i do a full backup on monday and a differential backup daily and transaction log daily for every 5 mins.

    If i need to restore database on friday at 12pm. I have to restore the full backup on monday, follow by all the differential backups from monday to thursday and then from restore all the transaction logs from monday until friday 12pm? Is that correct?

    It would takes hell lots of time trying to recover, am i right? thanks

    Pls read the link which i have given. It ll clear your doubt.

    Example from the article

    For more clarity. See an example:

    Assume we are scheduled every Sunday full backup @12 am and daily differential backup 10 PM.

    Note: The data changes daily 500 MB.

    Question:

    How much data got back up on Thursday?

    The student answers 500 MB.

    As I already told, differential database backup "backs up all the data that has changed since the last full database backup".

    So, the answer is,

    Monday 500MB + Tuesday 500 MB + Wednesday 500MB + Thursday 500MB= 2000MB

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Illegal_Operation

    Default port

    Points: 1448

    i gone thru the link that u have provided. but just have doubt over the transaction logs restore, as what i have painted on the scenario, we have to restore all the transaction logs from monday until thursday at the point of time? thanks

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    kesmond (6/20/2011)


    i gone thru the link that u have provided. but just have doubt over the transaction logs restore, as what i have painted on the scenario, we have to restore all the transaction logs from monday until thursday at the point of time? thanks

    No need because the differential backup contains all the data (modified extents)

    So you have differential backup upto on thursday. Just you can restore a log backup one by one after the differential backup has done.

    That is restore a log backup every 5 min on thursday onwards upto your DB crash point.

    Why do you think to restore a same data again and again ? That is diff cantains all the modified data.

    I think you are not understanding the differential backup behavior.

    Pls ask me if you have any doubt.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Warwick Rudd

    Ten Centuries

    Points: 1210

    How did you come to the decision that you need a full backup taken on a Monday , with differential backups taken on every other day , with 5 minute Tlog backups ?

    How large is your database ? How much data changes and how frequently ? A 5 minute Tlog backup could be a huge overkill but not knowing your system I can not say for certain.

    Do you have some sort of backup retention plan ?

    What is the reasoning behind the frequency of your Tlog backups ? Are you performing log shipping ?

    I am assuming that you can not afford to loose more than 5 minutes worth of transactions.

    Depending on your Recovery Point Objectives (RPO) & Recovery Time Objectives (RTO) will play a part on your backup and recovery strategy.

    If you do not know the answers to these it makes it difficult to implement an appropriate strategy.

    MCT
    MCITP Database Admin 2008
    MCITP Database Admin 2008
    MCITP Database Dev 2008
    www.jnrit.com.au/Blog.aspx

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    @Warwick rudd

    I think its not real time issue.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Illegal_Operation

    Default port

    Points: 1448

    Warwick rudd (6/20/2011)


    How did you come to the decision that you need a full backup taken on a Monday , with differential backups taken on every other day , with 5 minute Tlog backups ?

    How large is your database ? How much data changes and how frequently ? A 5 minute Tlog backup could be a huge overkill but not knowing your system I can not say for certain.

    Do you have some sort of backup retention plan ?

    What is the reasoning behind the frequency of your Tlog backups ? Are you performing log shipping ?

    I am assuming that you can not afford to loose more than 5 minutes worth of transactions.

    Depending on your Recovery Point Objectives (RPO) & Recovery Time Objectives (RTO) will play a part on your backup and recovery strategy.

    If you do not know the answers to these it makes it difficult to implement an appropriate strategy.

    This is just my own recommendation. Below are my scenario, the SQL Database will experience many transactions every second, hence the transaction log size will grow rather quickly. I need some suggestions of a backup plan that has backups for FULL, INCREMENTAL and LOG backups, which factors in the relationship of the 3 backups.

    Currently the database data is stored a SAN storage and the backup will be backed up to a separate storage device that emulates a tape backup device. Everything is running in a 64bit environment.

    Regards to the backup tapes, The transaction log might grow up to 5GB, so we are potentially looking at 5GB worth of backup daily. Is it best to reuse the same tape for the transaction log? or is rotating 2-3 tapes for the log would be good?

  • Illegal_Operation

    Default port

    Points: 1448

    muthukkumaran Kaliyamoorthy (6/20/2011)


    kesmond (6/20/2011)


    i gone thru the link that u have provided. but just have doubt over the transaction logs restore, as what i have painted on the scenario, we have to restore all the transaction logs from monday until thursday at the point of time? thanks

    No need because the differential backup contains all the data (modified extents)

    So you have differential backup upto on thursday. Just you can restore a log backup one by one after the differential backup has done.

    That is restore a log backup every 5 min on thursday onwards upto your DB crash point.

    Why do you think to restore a same data again and again ? That is diff cantains all the modified data.

    I think you are not understanding the differential backup behavior.

    Pls ask me if you have any doubt.

    Really appreciate your prompt reply. I am rather new to sql so in terms of backup, would need your advice.

    So i would do a restore from the full backup for Monday (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH NORECOVERY), after that i do a differential restore on Thursday (with the restore option of Overwrite the existing data (WITH REPLACE and also RESTORE WITH NORECOVERY), lastly i will do a restore of the transaction log from thursday onwards every 5 mins (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH RECOVERY), am i right? thanks.

    So the transaction log restore would start from the time the differential backup was done. For example, the differential backup was done on thursday 130pm, so the transaction log restore would start from thursday 130pm onwards (every 5 mins), is my understanding correct? thanks

  • Warwick Rudd

    Ten Centuries

    Points: 1210

    As I said previously, to determine a backup strategy suitable for your situation, you need to know an understand your RPO & RTO. Ie if there is a problem how long have you got to get back up and running, and how much data loss is acceptable. You mention your log could grow to 5 gb a day. Have you let it grow out ? What size is your db ?

    How much space do you have available to keep x number of backups online ? Do you have a process in place to write to tape ? How long do you need to keep things on tape ?

    Having a stab here but is your db between 5 - 10 gb ?

    MCT
    MCITP Database Admin 2008
    MCITP Database Admin 2008
    MCITP Database Dev 2008
    www.jnrit.com.au/Blog.aspx

  • Illegal_Operation

    Default port

    Points: 1448

    Warwick rudd (6/20/2011)


    As I said previously, to determine a backup strategy suitable for your situation, you need to know an understand your RPO & RTO. Ie if there is a problem how long have you got to get back up and running, and how much data loss is acceptable. You mention your log could grow to 5 gb a day. Have you let it grow out ? What size is your db ?

    How much space do you have available to keep x number of backups online ? Do you have a process in place to write to tape ? How long do you need to keep things on tape ?

    Having a stab here but is your db between 5 - 10 gb ?

    Hi Warwick, till now i haven got to touch it physically and hence i am not aware of the number of tapes available and also the database size yet but this is the scenario given to me. I think i did not make it clear, what i meant was the transaction log is growing tremendously everyday and hence it might grows to 3GB and how do we handle this type of scenarios? Given that we are using backup tapes and each tape is only around 1GB.

    As for my previous post, is my understanding as below correct? thanks

    So i would do a restore from the full backup for Monday (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH NORECOVERY), after that i do a differential restore on Thursday (with the restore option of Overwrite the existing data (WITH REPLACE and also RESTORE WITH NORECOVERY), lastly i will do a restore of the transaction log from thursday onwards every 5 mins (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH RECOVERY), am i right? thanks.

  • Illegal_Operation

    Default port

    Points: 1448

    Hi, i have a queries, i have do a testing (sample) to set the transaction logs backup to occur every 5 minutes. But i have check that how come the model db transaction log is being backup as well? And also there were some time gap between some of the transaction logs backup. (i.e the transaction log backup was at 215pm, the next one suppose to be 220pm, but it goes to 240pm, in between are model db transaction logs), am i still able to restore the transaction log as at 240pm as there are some missing logs in between? thanks

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    I need some suggestions of a backup plan that has backups for FULL, INCREMENTAL and LOG backups, which factors in the relationship of the 3 backups.

    SQL server doesn't has INCREMENTAL backup.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Illegal_Operation

    Default port

    Points: 1448

    hmmm... how come now when i restore the transaction logs file, my database is gone? only have model db transaction logs and other db transaction logs? anybody know why? what did i did wrongly? thanks

Viewing 15 posts - 1 through 15 (of 25 total)

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