Full and different backup

hungnn · October 12, 2008 9:07PM

Hi Petey

I tried the following scenario: Full backup everyweek, different backup every day and transaction log backup every 1 hour. The thing in here was: SQL Backup allows me to run the different backup even I have not backed up the full database.

As I understand, I need to run the full backup first, then choose the different backup by pointing to this full backup.

So if I want to follow the above scenario, what should I do ?

Thanks

petey · October 12, 2008 10:49PM

SQL Backup allows me to run the different backup even I have not backed up the full database.

As long as you have created a full backup, SQL Server will allow you to create a differential backup. The full backup could have been created using regular SQL Server backup commands, or using SQL Backup. Thus, the important point to note is if at any time you create a full backup, all subsequent differential backups would require that full backup as a base to restore from.

As I understand, I need to run the full backup first, then choose the different backup by pointing to this full backup.

Could you please explain what you mean by 'pointing to this full backup'?

To perform a differential backup, you only need to have performed a full database backup sometime in the past. To find out which full backup you need before you can restore a differential backup, look in the msdb..backupset table, where the database_backup_lsn value of the differential backup must match the checkpoint_lsn value of the full backup.

hungnn · October 12, 2008 11:17PM

Hi Petey

The issue in here is that I can even Running the different backup without performing the full backup and aslo when doing this, SQL Backup did not asked me the Full Backup, it just allows me to create the Different backup straight away. That's why it makes me so confused.

petey · October 13, 2008 12:02AM

Try this:

- create a new database e.g. CREATE DATABASE difftest

- create a differential backup e.g. BACKUP DATABASE difftest TO DISK = 'c:\difftest_diff.bak'
SQL Server reports that no full backup was ever made, hence a differential backup cannot be performed.

- create a differential backup using SQL Backup e.g. EXEC master..sqlbackup '-sql "BACKUP DATABASE difftest TO DISK = [c:\difftest_diff.sqb]" '
The same error is reported.

If SQL Server/SQL Backup allows you to create a differential backup, it means that a full database backup exists for that database. You could use the following script to get details of that backup

SELECT TOP 1 * 
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = !!insert database name here
  AND a.type = 'D'
ORDER BY a.backup_finish_date DESC

Full and different backup

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk