4

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
0

2 Answers 2

11

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.

1
  • Thanks again for the insight Aaron. You have once again come through for me on stackexchange! Oct 8, 2014 at 15:01
2

For the sake of completeness, you can inspect the contents of your backup file to determine the appropriate FILE=x parameter using the following T-SQL:

RESTORE HEADERONLY  FROM  
DISK = N'G:\BI_Staging.bak';

This will return some pertinent fields (among many others), such as:

  1. BackupStartDate - you can use this to determine the most recent backup stored in the file.
  2. BackupTypeDescription - this shows if the backup was a database or log backup, etc.
  3. Position - this is the number you would use in the RESTORE DATABASE ... FILE=x command.

See http://technet.microsoft.com/en-us/library/ms178536%28v=sql.110%29.aspx for further details about RESTORE HEADERONLY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.