SQL Server Database backup regressed to historical date I have the following code snippet that takes backup of my production database every night -

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10
go


When I'd tried restoring the database to a test server I identified the backup was regressed to a historical date, as identified from different application related tables. This was not in sync with the date backup had been actually generated.

I am actually a Java / Java EE developer and not very versatile with SQL Server, any help in this area will be much appreciated. This database runs SQL Server 2008R2.

There was no latency, manually the files were copied 5 files at a time, was complete in 30-45 minutes. The command used for restoration was:

RESTORE DATABASE [MY_TESTDB] 
FROM DISK = N'W:\...\My_database._dmp1.bak', 
DISK = N'W:\...\My_database._dmp2.bak', ... ... ..., 
DISK = N'W:\...\My_database._dmp30.bak' 
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 
GO


Also this backup had run last time on 23 Oct 2015 but the restored test DB got updated only till 31 Jul 2015.

There was only one backup in each backup device. I would hardly believe there had been a problem with restoration, as it was refreshed to multiple databases by others with DBA knowledge, but result was always 31 Jul 2015.
Short answer: add ", INIT" to the end of your WITH statement to re-initialize the backup file on every backup. Per Microsoft:

INIT Specifies that all backup sets should be overwritten...NOINIT is the default.

https://msdn.microsoft.com/en-us/library/ms186865.aspx

I modified your code below to include the new change:

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10, INIT
go


p.s. I'd get rid of your SHRINKFILE in production because it fragments the database and it can cause interruptions in service because SHRINKFILE is usually slow. Any index rebuild/reorganize will probably bloat your database back to a similar size anyway.

And you could get rid of checkpoint as well as SQL Server issues an automatic checkpoint when a backup is taken to insure that the current state matches the log.