1

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.

  • 3
    Well, did you back up to the same set of files multiple times? Why? You need to figure out what the latest file backed up is (instead of just using FILE = 1), or back up to new files each time using WITH INIT. (If you put timestamps in the file names, this makes it much easier to maintain / archive / purge old backups, rather than just having this single set of files that grows forever.) – Aaron Bertrand Jan 9 '16 at 15:17
3

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.

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.