July 6, 2011 at 10:48 pm
Hi all.
I'm trying to get a better understanding of how SQL backups work and in particular how restores from said backups work.
I've created a test database and a job the inserts some data every ten minutes.
I created a FULL backup job that runs every Sunday, and creates a new file each time.
I created a DIFF backup job that runs every night, and creates a new file each time.
I created a LOG backup job that runs every hour, and it appends to the same file.
Will this allow me to restore to almost any point in time ? (at least in the last week)
Wouldn't the LOG backup file just keep growing and growing and growing ?
How do I manage this, and how do I manage all the other backup files that would build up in time ?
thanks.
July 6, 2011 at 11:12 pm
Try starting here first. Will give you the basics. Then we can go from there as there is simply way to much information for a single post.
http://msdn.microsoft.com/en-us/library/ms175477.aspx
Fraggle
July 6, 2011 at 11:14 pm
[font="Times New Roman"]
Hi,
Full back up the baseline backup meaning full back up should be taken before diffrential backup and transaction log backup.
Once you have restored full backup in Norecovery mode you should restore recent diffrential backup with Norecovery afterthat all trasanction log backup taken after recent diffrential backup must be restored with norecovery except the last log back up
1. Yup, this backup plan will allow point in time recovery.
2. When log backup is completed the Non-active portion of the transaction log file is truncated usually.
3. Once you have successfully taken full backup you can delete all older backup files.
Regards,
Anil
[/font]
July 7, 2011 at 12:07 am
malcolm 81944 (7/6/2011)
I created a FULL backup job that runs every Sunday, and creates a new file each time.
I created a DIFF backup job that runs every night, and creates a new file each time.
I created a LOG backup job that runs every hour, and it appends to the same file.
I will kept a copy backup file (Full, different, log) at least 2 weeks for this implementation
I have rare experience where my full backup is running and override my last week full backup, server died and I don't have backup file to restore.
for log backup, normally we will create different file name for each backup. every backup will create a file
with add a time stamp behind.
1. This will help us do housekeeping, example, if we want to purge the backup log file every 2 week, we can purge accordingly
2. If you want restore the database to certain date, you can choose what log file want to restore
drawback:
1. You need to restore log log file when recovery
Note:
for one hour log backup, we may lost data up to one hour.
for half hour log backup, we may lost data up to half hour.
July 7, 2011 at 12:30 am
[font="Times New Roman"]
If recovery model for database is Full you should first take tail log backup before restoring. It will ensure that the modications that are not included in any back up will be backed up.After that you can start restoring from back files and at last you should restore tail-log back up file.
Regards,
Anil
[/font]
July 7, 2011 at 2:44 am
anil_kumar32 (7/7/2011)
[font="Times New Roman"]If recovery model for database is Full you should first take tail log backup before restoring. It will ensure that the modications that are not included in any back up will be backed up.After that you can start restoring from back files and at last you should restore tail-log back up file.
Regards,
Anil
[/font]
This may help you. But it's doesn't cover PITR.
SQL server Database backup restore Steps
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply