SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup and Restore queries


Backup and Restore queries

Author
Message
Illegal_Operation
Illegal_Operation
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 74
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
Muthukkumaran kaliyamoorthy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17724 Visits: 5493
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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum
Illegal_Operation
Illegal_Operation
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 74
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
Muthukkumaran kaliyamoorthy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17724 Visits: 5493
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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum
Illegal_Operation
Illegal_Operation
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 74
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
Muthukkumaran kaliyamoorthy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17724 Visits: 5493
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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum
Warwick rudd
Warwick rudd
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 436
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
Muthukkumaran kaliyamoorthy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17724 Visits: 5493
@Warwick rudd

I think its not real time issue.

Muthukkumaran KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum
Illegal_Operation
Illegal_Operation
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 74
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
Illegal_Operation
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 74
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search