March 17, 2020 at 3:44 pm
I have 5 TB size of database
We take full backup every sunday and Log backup every 1 hour
Now User accidentally updated some records
Can I just take the Last Log Backup and How?
I don't want to take full restore since I cannot effort the database to be down more than 15 min.
March 17, 2020 at 3:59 pm
If you need to restore, then you need to restore the FULL, then all the LOGS up to and before the time of the incident.
What your most likely want to do is to restore it side by side of the production database and then re-add the data that was deleted. You wouldn't want to restore the production database as you would have to re do every transaction that happened manually after the delete or data occurred.
March 17, 2020 at 4:06 pm
Nita
if you take a full backup every Sunday and then log backups every hour on a 5 TB database then you are in trouble.
the only way I can see you getting those rows back is to restore the database somewhere else, then restore the t-logs to a point just before where they were deleted. then you have to find the records
there are a few tools for t-log analysis, but I've never had any success with them.
Whilst i can't give you a magic fix for a user who deleted data, I can offer some advice
1) buy a Disaster recovery server and restore to it frequently (then you have the last data set immediatly available on the DR server)
2) back up daily
3) 5Tb??? see what you can trim down
For a DR server we just purchased a small workstation with a huge bunch of SSD Drives... no need for CPU or RAM - all it has to do is restore the database after it has been backed up.
that gives you 24 hours breathing room, but there is nothing stopping you putting a restore in place from a file a few days ago
but do try and trim the database down
MVDBA
March 17, 2020 at 4:11 pm
Let me phrase again.
My Sunday full backup finished at 6 AM
I have 2 transaction after full backup one at 7 AM and other at 8 AM. I want to just restore 7AM last transaction
The log backup size is only 2 GB for 7 AM.
I don't want to restore Full backup because I don't want DB to be down for more than 15 min.
March 17, 2020 at 4:45 pm
I think what you might not be understanding - we are advising that you restore it to somewhere else... zero downtime.
you cannot restore a single log to your live server
if it helps have a look at some documentation on LSN (log sequence number) - when you create a full backup then each log backup is linked by the LSN to the previous backup (be it full or t log).
if you want the data from either 7 or 8 am then you have to restore the full backup first - so that is going to take quite a while - hence our suggestion of restoring somewhere else and grabbing your data and putting the rows into your production system
MVDBA
March 17, 2020 at 4:52 pm
I got your point...Thanks
So basically full restore has to be done either it's a differential or Tlog.
Thanks once again
March 17, 2020 at 7:44 pm
Every SQL database restore must start with a full backup restore.
After that, you can, if you want to, restore a differential backup (that was taken after the full backup you restored).
After the full backup restore -- and diff restore if you chose to do one, or not, if you didn't -- you can, if you want, restore one or more tran log backups. They must start with the first relevant log backup and include every tran log backup, in order, up to the final time you want to restore to.
Assuming you have the disk space, you could always pre-restore the latest backup after it is taken. Then, if you need to use it, you'd only have to apply diff / tran log backups, and not wait for a full 5TB restore.
Btw, does the 5TB include the log space? How large is the log file (.ldf)?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 18, 2020 at 10:58 am
Every SQL database restore must start with a full backup restore.
After that, you can, if you want to, restore a differential backup (that was taken after the full backup you restored).
After the full backup restore -- and diff restore if you chose to do one, or not, if you didn't -- you can, if you want, restore one or more tran log backups. They must start with the first relevant log backup and include every tran log backup, in order, up to the final time you want to restore to.
Assuming you have the disk space, you could always pre-restore the latest backup after it is taken. Then, if you need to use it, you'd only have to apply diff / tran log backups, and not wait for a full 5TB restore.
Btw, does the 5TB include the log space? How large is the log file (.ldf)?
not wishing to divert from the issue, but there is nothing you can do about a restore of some stupid database with a huge transaction log file.... Microsoft please add a filesize option for the log when you restore!!!!!
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply