1

I created a full backup with SQL Server 2014 Management Studio. After that I removed it and created Differential backup, everything worked with no errors. How the server knows against what differential backup needs to be done if original full backup was removed?

1
  • As noted by Max Vernon, it is not what you still have available, but it depends on the transitions that the data has experienced. (FWIW, many people backup to a local drive, then move the backup file to another location not on the local server.)
    – RLF
    Oct 22, 2015 at 19:10

1 Answer 1

6

SQL Server creates a differential backup bitmap in all databases.

This bitmap contains a bit for each extent (8 pages) in the database.

Upon completion of a full backup, SQL Server clears the contents of each bit in the bitmap.

When pages are written to (ie rows are updated, deleted, inserted, etc, etc), the differential bitmap is updated to reflect the modified pages.

When SQL Server takes a differential backup, it uses the differential backup bitmap to determine what pages need to be backed up.

If you delete the full backup upon which the differential backup is based, the differential backup becomes useless.

To prove this, you can use the following test-bed:

USE master;
GO

CREATE DATABASE [Test];
GO

Backup the database:

BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Full_Backup.bak'
WITH FORMAT
    , INIT
    , SKIP
    , STATS = 1;
GO

Add something to the database:

USE Test;
GO

CREATE TABLE dbo.SomeDDL
(
    ID INT NOT NULL
);
GO

Take a differential backup:

USE master;
GO

BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH DIFFERENTIAL
    , STATS = 1;

Delete the full backup (this requires xp_cmdshell to be enabled):

EXEC xp_cmdshell 'DEL D:\SQLServer\Backups\Test_Full_Backup.bak';

Attempt to restore the differential backup:

RESTORE DATABASE [test]
FROM DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH RECOVERY;

Several errors occur:

Msg 3117, Level 16, State 1, Line 9

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 9

RESTORE DATABASE is terminating abnormally.

For details about how differential backups work, see this MSDN page.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.