asked
Restore database losing views
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
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...
