March 4, 2010 at 8:31 am
Hi,
I have noticed that when i make a backup of a database that is in simple recovery model, i can restore it directly through the GUI (MS) and i don't need to restore the tail of the log, this is because , if the database is in simple recovery model then i can not make backups to the log.
I can only do full backups and differencial backups.
When i make a backup of a database that is in full recovery model, when i try to restore it, through the GUI , the GUI does not let me, first i have to do a backup of the tail of the log and only then i can restore my backup.
I don't understand why....
So, what i do is restore the backup using the option of the GUI "overwrite the existing database". Is there a problem in doing this?
What does the option of the GUI "overwrite the existing database." do exactly?
Thank you
March 4, 2010 at 9:01 am
river1 (3/4/2010)
When i make a backup of a database that is in full recovery model, when i try to restore it, through the GUI , the GUI does not let me, first i have to do a backup of the tail of the log and only then i can restore my backup.I don't understand why....
It's telling you that the database has transactions in the log that have not been backed up and, if you restore over the database you will lose those transactions
So, what i do is restore the backup using the option of the GUI "overwrite the existing database". Is there a problem in doing this?
If it's not a production database and the loss of said transactions is not a concern, then no.
What does the option of the GUI "overwrite the existing database." do exactly?
It's the same as the WITH REPLACE option of RESTORE DATABASE. Just acknowledging that you know you are replacing a database. Prevents someone accidentally restoring a database over another one in a production environment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 9:25 am
"It's telling you that the database has transactions in the log that have not been backed up and, if you restore over the database you will lose those transactions"
This is a new database. I have created the database and then i have made a backup ,so, how can i LOSE transactions if a full backups as all the information on the database?
In production enviroment if i do a FULL backup (wich is the case) shouldn't i be able to reatore it without having to make a backup of the tail of the log?
I understand that you can say:
Well, when you make a backup (full backup) , if no one as worked more on that database, you can restore it using the option "overwrite the existing database" even in production enviroment (because no body as worked on the database since the backup was made).
But if you have made the backup and then someone as made some work in the dabase (inserts, updats, deletes), when you try to restore this backup, then it should ask for the tail of the log, so that the transactions that where made after you made the backup can be then restored
I think that this is how thinks work in termos of data lose.
So, what i do is restore the backup using the option of the GUI "overwrite the existing database". if no one have worked after i made my full backup.
If someone as used the database after i have made the backup, what i do is backup the log (not the tail) and then restore first the full backup and then transaction log backup.
So, if my undertanding of SQL Server 2005 backups system is correct, i shoud not be asked to do a tail of the log backup if no one as worked on the database after i made the full backup that i want now to restore. correct?
The tail of the log backup is not a normal log backup (backup log test to disk=c:\test-trn), correct?
Thank you.
March 4, 2010 at 10:22 am
river1 (3/4/2010)
This is a new database. I have created the database and then i have made a backup ,so, how can i LOSE transactions if a full backups as all the information on the database?
Because there could have been transactions between the time of the full backup that you took and the time of the restore. Those will be lost
In production enviroment if i do a FULL backup (wich is the case) shouldn't i be able to reatore it without having to make a backup of the tail of the log?
Yes, if you don't mind losing any changes that were made after the full backup, before the restore.
It may be that no one worked on it, but there will still likely be system changes that were logged. I don't think there's a check for LSN matches, it's simply a precaution so that you don't accidentally overwrite a database and lose work.
If you don't care about that, use the WITH REPLACE option. That's why it's there.
The tail of the log backup is not a normal log backup (backup log test to disk=c:\test-trn), correct?
Books Online. Subject Tail-log backup. Read it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply