title: SQL Server : how to shrink .bak files after deleting older records
body: <p>I have a database and my intent is to delete some older records from a few tables after a certain time period, do a backup, and shrink the log file. I run a procedure every day to perform this action. However, my <code>.bak</code> continues to get bigger. Why is that? </p>
  
  <p>Even after shrinking the log file and deleting older records, my <code>.bak</code> file grows more each time I back it up. How do you backup a database and have the <code>.bak</code> file be a representation of the actual size consisting in all the tables currently? Any help is most appreciated. Thanks. </p>
  
  <pre><code>DECLARE @setDate DATE 
  
  SET @setDate = CAST(GETDATE() - 10 AS DATE) 
  
  DELETE recipebatches 
  WHERE CAST(dates AS DATE) &lt; @setDate 
  
  DELETE parameterdata 
  WHERE CAST(dates AS DATE) &lt; @setDate 
  
  BACKUP DATABASE xxxx
  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\xxxx.BAK'
  
  ALTER DATABASE xxxx
  SET RECOVERY SIMPLE;  
  
  -- shrink  to 1 mb.  
  DBCC SHRINKFILE (xxxx_Log, 1);  
  
  ALTER DATABASE xxxx  
  SET RECOVERY FULL; 
  </code></pre>
  
  <p>Current tables in my database - not many records but I have a 2,329,768 kB <code>.bak</code> size</p>
  
  <p><a href="https://i.stack.imgur.com/6MIjY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6MIjY.png" alt="Current tables in my database"></a></p>
  
  <p>Growth data for LOG and <code>.MDF</code> files:</p>
  
  <p><a href="https://i.stack.imgur.com/qsJWB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qsJWB.png" alt="enter image description here"></a></p>