Full vs Differential vs Transaction Log Backup
SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.
Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.
Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.
Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.
You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.
Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.
The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.
If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.
It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.
Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.
Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.
Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.
You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.
Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.
The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.
If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.
It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.
**************************************************************
way to restore full/diff/log backup
http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
I received the following email from one of my readers.
Dear Pinal,
I am new to SQL Server and our regular DBA is on vacation. Our production database had some problem and I have just restored full database backup to production server. When I try to apply log back I am getting following error. I am sure, this is valid log backup file. Screenshot is attached.
[Few other details regarding server/ip address removed]
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Screenshot attached. [Removed as it contained live IP address]
Please help immediately.
Well I have answered this question in my earlier post, 2 years ago, over here SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward. However, I will try to explain it a little more this time.
For SQL Server database to be used it should in online state. There are multiple states of SQL Server Database.
- ONLINE (Available – online for data)
- OFFLINE
- RESTORING
- RECOVERING
- RECOVERY PENDING
- SUSPECT
- EMERGENCY (Limited Availability)
If the database is online, it means it is active and in operational mode. It will not make sense to apply further log from backup if the operations have continued on this database. The common practice during the backup restore process is to specify the keyword RECOVERY when the database is restored. When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.
However, if you want to restore more than one backup files, i.e. after restoring the full back up if you want to apply further differential or log backup you cannot do that when database is online and already active. You need to have your database in the state where it can further accept the backup data and not the online data request. If the SQL Server is online and also accepts database backup file, then there can be data inconsistency. This is the reason that when there are more than one database backup files to be restored, one has to restore the database with NO RECOVERY keyword in the RESTORE operation.
I suggest you all to read one more post written by me earlier. In this post, I explained the time line with image and graphic SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.
Sample Code for reference:
RESTORE DATABASE AdventureWorksFROM DISK = 'C:\AdventureWorksFull.bak'WITH NORECOVERY;RESTORE DATABASE AdventureWorksFROM DISK = 'C:\AdventureWorksDiff.bak'WITH RECOVERY;
In this post, I am not trying to cover complete backup and recovery. I am just attempting to address one type of error and its resolution. Please test these scenarios on the development server. Playing with live database backup and recovery is always very crucial and needs to be properly planned. Leave a comment here if you need help with this subject.
--Full Back Backup
BACKUP DATABASE [test] TO
DISK = N'D:\HDE\TestBackup\a1' WITH NOFORMAT
GO
--Transaction Log Backup
BACKUP LOG [test] TO
DISK = N'D:\HDE\TestBackup\a3' WITH NOFORMAT, NOINIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--Differential Backup
BACKUP DATABASE [test] TO DISK = N'D:\HDE\asdf'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'test-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--restore full backup
restore database test from
DISK = N'D:\HDE\TestBackup\a1' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
--restore transaction log backup or differential backup
restore database test from
DISK = N'D:\HDE\TestBackup\a2' WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 10
Lets take an example to understand this better: TransactionLog backup
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take transaction log backup - a2
4) insert one more record in test table
5) take transaction log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will not allow. a1, a2 and a3 needs to be in sequence.
Lets take an example to understand this better: TransactionLog backup
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take transaction log backup - a2
4) insert one more record in test table
5) take transaction log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will not allow. a1, a2 and a3 needs to be in sequence.
--Full Back Backup
BACKUP DATABASE [test] TO
DISK = N'D:\HDE\TestBackup\a1' WITH NOFORMAT
GO
--Transaction Log Backup
BACKUP LOG [test] TO
DISK = N'D:\HDE\TestBackup\a3' WITH NOFORMAT, NOINIT,
NAME = N'test-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--Differential Backup
BACKUP DATABASE [test] TO DISK = N'D:\HDE\asdf'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'test-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--restore full backup
restore database test from
DISK = N'D:\HDE\TestBackup\a1' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
--restore transaction log backup or differential backup
restore database test from
DISK = N'D:\HDE\TestBackup\a2' WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 10
Lets take an example to understand this better: TransactionLog backup
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take transaction log backup - a2
4) insert one more record in test table
5) take transaction log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will not allow. a1, a2 and a3 needs to be in sequence.
Lets take an example to understand this better: TransactionLog backup
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take transaction log backup - a2
4) insert one more record in test table
5) take transaction log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will not allow. a1, a2 and a3 needs to be in sequence.
Lets take an example to understand this better: Differential backup
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take differential log backup - a2
4) insert one more record in test table
5) take differential log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will allow. after a1 either a2 or a3 can be restored.
[Trasaction log backup is bascially incremental backup while differential backup is a additional back up from last full backup]
database test is having a table test and its having 1 record
1) Take full backup - a1
2) insert one more record in test table
3) take differential log backup - a2
4) insert one more record in test table
5) take differential log backup again - a3
now restore a1 with no recovery and then try to restore a3, it will allow. after a1 either a2 or a3 can be restored.
No comments:
Post a Comment