June 20, 2011 at 1:50 am
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 ?
June 20, 2011 at 2:20 am
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?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 20, 2011 at 2:30 am
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
June 20, 2011 at 2:39 am
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/
June 20, 2011 at 2:43 am
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
June 20, 2011 at 2:52 am
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/
June 20, 2011 at 3:47 am
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
June 20, 2011 at 3:56 am
@Warwick rudd
I think its not real time issue.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 20, 2011 at 6:52 pm
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?
June 20, 2011 at 7:01 pm
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? thanksNo 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
June 20, 2011 at 11:55 pm
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
June 21, 2011 at 12:08 am
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.
June 21, 2011 at 12:48 am
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
June 21, 2011 at 1:12 am
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/
June 21, 2011 at 1:16 am
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 24 total)
You must be logged in to reply to this topic. Login to reply