Full and different backup

Comments

• petey Redgate › Posts: 2,358 New member

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• hungnn Posts: 18

  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.

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  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
  

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter