Are Differential / Transaction Log Backups Necessary
This is a follow-up to an earlier post (https://www.experts-exchange.com/questions/24336568/SQL-Server-2000-Backup-File-Size-is-Excessive.html).
My 5.5 Gb SQL Server 2000 database grows at about 10 Mb per day. I want to implement a better backup strategy that reduces the amount of data loss at risk in the event of a failure. I'm not completely sure of the need for transactional backups. Our daily complete backup currently creates a file about 75 Mb in size.
Am I correct that transactional backups just reduce the size of the current transaction log? If I didn't perform any transactional backups, can't I still restore the database from the last complete backup and the current transaction log? Or is the transaction log trashed if there is a failure? Are there other benefits to backing up the transaction log?
Given our small transaction activity, having frequent differential backups may be sufficient, if we can live with a small loss of data. Recommendations?
My 5.5 Gb SQL Server 2000 database grows at about 10 Mb per day. I want to implement a better backup strategy that reduces the amount of data loss at risk in the event of a failure. I'm not completely sure of the need for transactional backups. Our daily complete backup currently creates a file about 75 Mb in size.
Am I correct that transactional backups just reduce the size of the current transaction log? If I didn't perform any transactional backups, can't I still restore the database from the last complete backup and the current transaction log? Or is the transaction log trashed if there is a failure? Are there other benefits to backing up the transaction log?
Given our small transaction activity, having frequent differential backups may be sufficient, if we can live with a small loss of data. Recommendations?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I understand. I thought differential backups were since the last complete or differential backup. I didn't realize it was only since the last complete backup. So by the end of the day, it would begin to be as expensive as a complete backup.
That's why the transaction log backups are important. They won't be redundant like succesive differential backup's would.
Would you still recommend using differential backups or should I just rely on a mix of complete and transaction log backups?
That's why the transaction log backups are important. They won't be redundant like succesive differential backup's would.
Would you still recommend using differential backups or should I just rely on a mix of complete and transaction log backups?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. That's what I'll do.
ASKER
We must have a lot of empty space because the full backup is consistently around 75 Mb. The backup was 5.5 Gb only when the full daily backups were temporarly suspended for a week and a half.
I read the article but my understanding of the transaction log file and its backups is still incomplete. I assume that transaction log file backups can be used to restore the database to a point in time since the last full backup. However, I don't know the following:
1) Can the transaction log itself be used to restore the database to a point in time or only transaction log backups? If the latter, then why wouldn't it be just as good to use differential backups instead of transactional ones?
2) How do transaction log backups compare in size to differential backups?
Thank you again for your response. I hope you can answer my follow-up questions.