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 '11 at 12:13 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 57 62 66

(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 '11 at 12:33 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Many thanks that's done it. It needed the With Format.
Jun 01 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1933
x672
x186
x101
x30

asked: Jun 01 '11 at 12:13 AM

Seen: 724 times

Last Updated: Jun 01 '11 at 12:13 AM