x

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

more ▼

asked Jun 01, 2011 at 12:13 AM in Default

avatar image

Mrs_Fatherjack
5.1k 64 66 74

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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...

more ▼

answered Jun 01, 2011 at 12:33 AM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

Many thanks that's done it. It needed the With Format.

Jun 01, 2011 at 01:57 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2016
x987
x235
x122
x39

asked: Jun 01, 2011 at 12:13 AM

Seen: 992 times

Last Updated: Jun 01, 2011 at 12:13 AM

Copyright 2016 Redgate Software. Privacy Policy