Sql-server – Database Doesn’t Contain Current Data After Restoring Last Full Backup from Production

backupsql server

I restored my last full backup from production database to reporting database server, but last restored database on my reporting server doesn't contain current tables, procedures, functions etc. What can cause this situation? How can I solve it?

Best Answer

This is a common issue when you back up to the same file over and over again. Without WITH INIT, the new backup gets appended to the file, it doesn't overwrite it. So in this example:

BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;
GO
USE model;
GO
CREATE TABLE dbo.newtable(id int);
GO
BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;

We can see from RESTORE HEADERONLY that there are two backups:

RESTORE HEADERONLY FROM DISK = 'c:\temp\model.bak';

Partial results:

Position        BackupFinishDate
-------------   -----------------------
1               2019-09-03 14:01:08.000
2               2019-09-03 14:01:11.000

Guess which one has dbo.newtable? Guess which one is used as the default when you just say RESTORE ... FROM DISK ...;?

If you're going to continue using this method to back up your databases, you'll need to determine the Position to pass to RESTORE ... WITH FILE = <position> so that you get the latest backup. A better practice IMHO is to always use BACKUP ... WITH INIT and append timestamps to the file name. This makes it easier both to ensure you're using the latest backup and also to remove older backups.

Related Question