backup the difference with sql server express between 2 timestamps

I am newbie to sql and have sql server express edition installed on my PC and contains
a database ‘Jdatabase’ on 1sept and took a backup(by right clicking on database -> tasks->backup using ‘sql server management studio’) of it and backup is 20GB
after 1 month , I took the backup for the same database and it increased to 35GB.
Likewise saving the backup is going to be huge after certain time and is a problem for me.

If I want to backup only the difference ie., 15GB that has grown in 1 month time.
what is the best way to do it using SQL server express.

My guess is that you ran this:

BACKUP DATABASE foo TO DISK = 'c:backupfoo.bak';

... wait a week ...

BACKUP DATABASE foo TO DISK = 'c:backupfoo.bak';

The result of this is that your backup file contains two backups, so the file itself will increase in size, whether that delay is a week or a minute.

To avoid this, you can choose one of two options:

1. You can reinitialize the file to remove any old backups and replace them with the new one:

   BACKUP DATABASE foo TO DISK = 'c:backupfoo.bak' WITH INIT;
   

   This may or may not be desirable, since you only ever have a single backup, and if something should go wrong in between reinitialization and backup completion, you have zero backups.

2. You can write to a new file each time, typically people put a timestamp on the file or write to a folder made for that day.

   BACKUP DATABASE foo TO DISK = 'c:backupfoo201811010843.bak';
   

   You can use a variable for the destination filename, so you can construct that based on GETDATE() as opposed to hard-coding it.

   DECLARE @fn varchar(32) = 'c:backup' + CONVERT(char(8), GETDATE(), 112)
     + REPLACE(CONVERT(char(5), GETDATE(), 108),':','') + '.bak';
   
   BACKUP DATABASE foo TO DISK = @fn;
   

   The upside is that you don’t have to be tied to a single backup file (and hence single point of failure). The downside is that you will have multiple files to manage… you may want some kind of cleanup task that automatically deletes everything except, say, the last two backups.

Now, there are a couple of issues that are tangential that I have to mention:

1. Are you only taking full backups? If so, then realize you will never be able to restore your database to a specific point in time, only to the point in time the last successful backup started. If that is a problem, you should use full recovery, not simple recovery, and determine a proper log backup schedule that meets your business requirements for how much data you can lose (RPO) and how long you can wait to get back up and running (RTO) in the event of a disaster.

2. If you are going to continue using simple recovery, you do have an option to take a backup of only deltas since the last backup, e.g.

   BACKUP DATABASE foo TO DISK = 'c:backupfoodiffwhatever.bak' WITH DIFFERENTIAL;
   

   You’ll need to manage files here, too, because all the differentials in the world are useless without the original full backup they reference. For more info, see Differential Backups.

3. If you are worried about your database growing to 35 GB but not having enough room to store a backup of that database, stop what you’re doing right now and go find more disk space. You should not be making a decision between having data and being able to back it up and, frankly, you should not be storing your backups on the same disk as the database anyway (that kind of defeats the purpose). It’s like keeping your car key in a jacket pocket and keeping the extra key (you know, in case you lose your jacket) in the other jacket pocket.

If I want to backup only the difference ie., 15GB that has grown in 1 month time. what is the best way to do it using SQL server express.

Are you sure you have SQL Server express edition. The data file of express edition is limited to 10 GB for SQl Server 2008 R2 and above. Yes log file has no such limitation, anyways you cannot backup just the “difference”. When you backup a database it will backup complete data not the difference and there is no way that you backup only the data changed after certain days using TSQL as well.

You can move out the data to new table if you have some column to identify data which was added after certain date like datetime column.