SQL Server : differential backup not updating

Question

I'm using this script to create a differential backups for my database :

 BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.dif'

But what amazes me is that the generated file does not change in size, even if I insert more information or create new tables in my database. I expect the size to change because the full backup size changes if I add more data into my database.

Full backup script :

BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.bak'

I run the above scripts manually (every hour) because I'm using SQL Server Express, and apparently it does not have the Maintenance Plan feature.

Answer 1

The extension of a backup file is just to have a notation and easy identification. The script you are using is for Log backup and not for Differential backup.

Differential backup backs up the extents that had changes since the last full backup. Below is the t-sql for that :

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL
GO

The backup that you are taking is log backup and in addition to letting you restore the backed-up transactions, a log backup truncates the log to remove the backed up log records from the log file. This is very important to have a point in time recovery and has to be scheduled to run frequently.

Please go through below link to understand how backups work :

https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx