Full database backup

Question

I'm using third party tool for making schedule database backups. I need to make one additional full backup. Should it be affected on the scheduled backups? If yes, is there any solution?

Answer 1

The short answer is yes.

As usual, after full backup follows differential backups, that completely depend on the last full backup. It means that if the database failure occurs there will be no chance to apply scheduled differential backups because they will be with another LSN.

The solution is to use COPY_ONLY option.

Answer 2

The new additional full database backup will be affected on the scheduled backups.

Let's consider the following example: Assume that the full database backup starts at 12:00 once a day, a differential backup starts every three hours and transaction log backup makes every hour.

Then after scheduled full backup was made at 12.00 you decided to make the extra one at 14.00 but all other differential and transaction log backups are making according to the schedule. Let's say that at 22.20 some failure occurs and you are trying to restore your database starting from the full backup that was made at 12.00, in this case, SQL Server will send you the error message.

The reason is that differential backups that were made at 15.00, 18.00 and 21.00 are complete depended on the full backup that was made at 14.00. Of course, if you have all transaction log backups you can restore your database without differential backups, but such process will take more time, otherwise everything that you can do - is to restore last scheduled full backup and all changes from 12.00 will be lost.

So, to make additional full database backup use COPY_ONLY option:

BACKUP DATABASE your_database TO DISK = 'd:/full.bak' WITH COPY_ONLY

Answer 3

Yes, it will affect on the scheduled backups (LSN chain).

If you need to make one additional full backup. You need to use COPY_ONLY option. This option will make the additional full backup that does not alter the differential chain. This way you do not affect the LSN chain at all. The LSN’s of any differential backups taken after the new ad hoc backup still refer to the original full backup, not to the impostor.

So to make a full backup with COPY_ONLY option us the following T-SQL command:

BACKUP DATABASE database_name TO DISK = 'y:/add_full.bak' WITH COPY_ONLY