What are the challenges you face when working across database platforms? Take the survey
Options

Full and different backup

hungnnhungnn Posts: 18
edited October 13, 2008 12:02AM in SQL Backup Previous Versions
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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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
Sign In or Register to comment.