SQL Server Restored Database – Differences from Original

sql serversql-server-2008-r2

I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment. After the restore I did a comparison and it looks like the database sizes are different. Staging_Original is 8246 MB while Staging_New is 7571.63 MB. It appears that 2 tables in the new db are missing and for some reason the new database has a table that doesn't exist in the old database. How is this possible if I've performed a full backup and restore? How can I get an exact copy of one database and restore it into a second instance? Both of the model databases appear to be the same as well.

Backup scrtip:

BACKUP DATABASE [BI_Staging] TO  DISK = N'G:\BI_Staging.bak' 
  WITH NOFORMAT, NOINIT,  NAME = N'BI_Staging-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset 
  where database_name=N'BI_Staging' 
  and backup_set_id=(select max(backup_set_id) 
    from msdb..backupset where database_name=N'BI_Staging' )
if @backupSetId is null 
begin 
  raiserror(N'Verify failed. Backup information for database ''BI_Staging'' not found.', 16, 1) 
end
RESTORE VERIFYONLY FROM  DISK = N'G:\BI_Staging.bak' 
  WITH FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Restore Script:

USE [master]
GO
ALTER DATABASE [BI_Staging] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [BI_Staging] FROM  
DISK = N'G:\BI_Staging.bak'
WITH  FILE = 1
,  MOVE N'BI_Staging' TO N'E:\MSSQL10_50.BI_DSS2\MSSQL\DATA\BI_Staging.mdf'
,  MOVE N'BI_Staging_log' TO N'F:\MSSQL10_50.BI_DSS2\MSSQL\Tlogs\BI_Staging.ldf'
,  NOUNLOAD,  REPLACE, RECOVERY, STATS = 10
GO

Best Answer

You've used NOINIT in your backup, and then FILE = 1 in your restore. So it seems like you have multiple backups in the .bak file, and you aren't pulling the right one for the restore. In fact you can see that in your RESTORE VERIFYONLY script you specify the FILE = @backupSetId but in the actual restore you just hard-code FILE = 1.

This is one of many reasons I don't like reusing a single file for multiple backups. What does it gain you? Not a whole lot, unless you are creating a gazillion backups every day and exceeding the capacity of file count by NTFS. I would much rather have a single .bak file with a timestamp in the name or something, then it is very easy to identify oldest or most current backups both visually and programmatically, restores are simpler, and it is also easier to purge expired backups.

For now, try a much simpler command with a unique filename and WITH INIT (I don't think you really need all of those other options)...

BACKUP DATABASE [BI_Staging]  
  TO DISK = N'G:\BI_Staging_This_Time_For_Sure.bak' WITH INIT;

...and then restore that file.

Related Question