May 6, 2022 at 3:43 pm
I would like to know the difference of full back up between Oracle and SQL server.
In SQL server, for a medium size database, If I do a full backup, then I can use to restore to another database that is exactly the same as the original database. I don't need to do online transaction logs to restore.
For Oracle if I issue a full backup, like : backup database, it will only backup the data files not the transactions in the archive logs. And If I use it to restore to anther database, it will only go to the point the data file is, not the archive logs. So I need to restore use full data file backup + archive logs.
Is my understanding correct?
or Do both SQL server and Oracle full backup mean backup data files only?
Thanks,
May 6, 2022 at 9:19 pm
My understanding, and a quick google confirmed what I thought, is that they are essentially the same thing. A FULL backup is a backup of ALL data up until that point in time. My understanding is that an Oracle Archive Log is similar to a SQL Server Log Backup in that it has the data that has changed since the last backup.
which states "Archived redo logs are used in case you lost your data due to any situation: DB crashed, data corruption, or any. In such a situation archive logs (from the last backup taken to a time when DB is corrupted) are used." The example they give is that in the event of corruption, you can restore from the FULL and then restore the LOG.
So, to do a restore, in both SQL and Oracle, if you have a FULL backup only, you restore from that BUT it will only have data up until the time the backup was taken. So if you do the full backup at midnight daily and you want data from today at noon, you would need to wait until the next full backup at midnight before you could see that data. However, if you do log backups or differential backups, you can get to a better point in time prior to the latest log backup.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 6, 2022 at 9:31 pm
That make sense. For this situation I asked is to restore database to the point that I start do the last full backup. So as I understand now it should be the same concept in SQL server. that I only need the full backup.
Another related question about Oracle:
For case if there is a a media failure, I want to restore the database to a point of time back, I need the last good full backup, and also the archived logs. My question, why it does not need online redo logs?
As I understand archive logs are archiving older online redo logs, what if the transactions time I want to restore is in one of the online redo logs that has not been archived into archive logs yet?
Thanks,
May 7, 2022 at 4:33 pm
There really isn't an 'online' redo logs in SQL Server - but there can and will be transactions in the log that have not yet been backed up. In the event of a disaster where you want to be able to restore to the point in time of that disaster - you would need access to the database files and transaction log file and the ability to perform a tail-log backup.
If you cannot meet the requirements for a tail-log backup, then all data from the last log backup to current would be lost. This is one of the reasons we recommend frequent log backups. For production systems I generally set that to 15 minutes or less - ideally every 10 minutes or even every 5 minutes depending on the system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2022 at 5:04 pm
Thanks, the tail log backup makes sense now for me.
Additionally can I confirm the following statement is true?
When doing full backup in SQL server , It will include all data that has been written into data files, and also include data written in transactions logs at that time into data files , correct?
So that the data is complete for that point of time, and I can use the full backup files to restore database to the point of time- that is the time I do the backup.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply