July 21, 2009 at 3:16 pm
Is it ok to take backup when the data is loading. my next question is How the backup works when there is loading going on. ?
Let's say there are 20 million rows writting in the table and backup is also running.
I know that we should not take backup when the load is running on the database but I need to know what are the complication will I face. ?
July 21, 2009 at 3:51 pm
It probably isn't the best but it shouldn't break anything. The hard part is depending on your transaction size and batch size depends on whether all that data is in a single transaction. If it is then the backup will contain part of it but on the restore roll it all back as it the data was never loaded. If it is several transactions then whatever has been commited will be in the table and whatever is in the next transaction will be rolled back on the restore. You end up with an unknown state of the data on that table. But in reality this only really matters if you restore that database backup.
CEWII
July 21, 2009 at 4:07 pm
I agree but don't you think that if backup and data loads are running at the same time then it will fill the transaction log big time.
I think backup also fills the transaction log file
July 21, 2009 at 4:08 pm
Backups are not logged in the transaction log.
July 21, 2009 at 4:22 pm
However the transaction log cannot be truncated (by a log backup or checkpoint) while a full backup is running.
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
July 21, 2009 at 4:24 pm
How about when database is in a recovery mode or if we are restoring database. will it fill the transaction log file. ?
July 22, 2009 at 5:55 am
The transaction log can fill from transactions, regardless of the recovery mode, although some recovery modes are less likely to fill the log than others. You need to keep that in mind while developing apps. Some people think because the db is set to Simple that they'll never fill the log, but that's not at all true.
A database restore, like a database backup, isn't a logged operation, so it doesn't affect the size of the log, positvely or negatively, other than as Gail mentioned.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply