Restore database losing views

Question

A process that is run requires us to have 2 snapshots of data at different points in time to use to compare data. To do this we use Redgate's data compare which works perfectly.

The script I am writing takes the current database, backs it up and restores it (with move) to the previous database, then we have another script that creates the new database. I have then written a script to take the views from the previous database (taken from a restore of current) and scripts them into the current database. I have tested my code that creates the views and that works fine so I think the issue must be with my backup and restore. The script I'm using for this is:

BACKUP DATABASE Standard_ASCII_8_5_Current
TO DISK = 'D:\AABackups\NewStandard_ASCII_8_5_Current.bak'
WITH  INIT
go

PRINT 'Drop previous database'

-- Drop the old 'Previous' database
DROP DATABASE Standard_ASCII_8_5_Previous

USE master
-- Restore database with move option

PRINT 'Restoring databases'

RESTORE DATABASE Standard_ASCII_8_5_Current
   FROM DISK = 'D:\AABackups\Standard_ASCII_8_5_Current.BAK'
   WITH MOVE 'Standard_ASCII_8_5_Current' TO 'D:\AABackups\Standard_ASCII_8_5_Previous.MDF',
        MOVE 'Standard_ASCII_8_5_Current_log' TO 'D:\AABackups\Standard_ASCII_8_5_Previous_Log.LDF', REPLACE



-- rename database
PRINT 'Renaming database'
USE master;
GO
--EXEC sp_dboption Standard_ASCII_8_5_Current, 'Single User', True
--GO
EXEC sp_renamedb 'Standard_ASCII_8_5_Current', 'Standard_ASCII_8_5_Previous'
GO
--EXEC sp_dboption Standard_ASCII_8_5_Previous, 'Single User', False
--GO

-- rename the logical file names
PRINT 'rename the logical files'
ALTER DATABASE Standard_ASCII_8_5_Previous
MODIFY FILE (NAME = Standard_ASCII_8_5_Current, Newname = Standard_ASCII_8_5_Previous)

ALTER DATABASE Standard_ASCII_8_5_Previous
MODIFY FILE (NAME = Standard_ASCII_8_5_Current_log, Newname = Standard_ASCII_8_5_Previous_log)

The views do not appear in the previous database which I would expect as that has come from a restore from Current.

Any ideas greatfully received.

Many thanks

Answer 1

Can you try this script and see how the FORMAT option works and then see if the backup volume you are creating has more than one backup set in it ?

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\temp\AW.bak'
WITH NAME='A backup', STATS=5
go

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\temp\AW.bak'
WITH NAME='Another backup', STATS=5
go

RESTORE HEADERONLY FROM DISK = N'c:\temp\AW.bak'
GO

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\temp\AW.bak'
WITH FORMAT, NAME='A new backup', STATS=5
GO

RESTORE HEADERONLY FROM DISK = N'c:\temp\AW.bak'
GO

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\temp\AW.bak'
WITH init, NAME='Another new backup', STATS=5
GO

RESTORE HEADERONLY FROM DISK = N'c:\temp\AW.bak'
GO

I think you may be accessing a previous backup of the database...

Restore database losing views

Follow this question

Related Questions