0

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.

  • 3
    Both scripts in your question are identical and only backup the transaction log. Full and differential database backups are different beasts. – Dan Guzman Jul 22 '17 at 21:08
  • @chosenOne Thabs, As i am seeing your "Log Backup" extension is (.bak). Could you please update to us about database full backup extension. That is also (.bak) or anything else. – Md Haidar Ali Khan Jul 23 '17 at 12:06
  • 1
    @chosenOne Thabs, I also notice that as you have mention the sql syntax under "Full backup script :" like 'BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.bak' . if you are taking the full database backup then you are doing wrong script for database backup. – Md Haidar Ali Khan Jul 23 '17 at 12:08
  • @DanGuzman and @ Md Haidar Ali Khan i verified your suggestion on Google. Now i'm using 'BACKUP DATABASE' to create full backup and 'BACKUP DATABASE WITH DIFFERENTIAL' to backup differential backups. Thank you very much. – chosenOne Thabs Jul 23 '17 at 18:56
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

| improve this answer | |

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.