Database Doesn't Contain Current Data After Restoring Last Full Backup from Production

Question

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?

Answer 1

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.

Answer 2

When you do a database restoration, all your current data as well as objects - tables, views, functions, procedure anything which is inside database is lost and you get all the objects of the database whose(from) full back up was restored.

In case, you were not aware of this - check if you had any scheduled job running on reporting server which was taking full backup and that backup is clean. if the backup is clean, you can restore on another database or on this database based on your requirement.

Ideally you should have taken full backup of your reporting database before restoring your production database over this server.

Hope above helps.