Problem with RESTORE on same server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Problem with RESTORE on same server

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-04-23 : 05:10:40

I'm trying to restore a backup (made with Enterprise Manager) to a brand new database I just created (on the same server)

I'm sure I've done this before, but I'm getting

Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'MyNewDB' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Here's the script I'm using:


RESTORE DATABASE MyNewDB
  FROM DISK = 'D:\DATA\MSSQL\BACKUP\MyOldDB_040423_0552_GoLive.BAK'
    WITH
      REPLACE,
      RECOVERY,
      STATS = 10,
  MOVE 'MyOldDB_data' TO 'D:\DATA\MSSQL\DATA\MyNewDB.mdf', 
  MOVE 'MyOldDB_log' TO 'D:\DATA\MSSQL\DATA\MyNewDB.ldf'

and then once it is restored I would normally do:


ALTER DATABASE MyNewDB MODIFY FILE (NAME = 'MyOldDB_data', NEWNAME = 'MyNewDB_data')
ALTER DATABASE MyNewDB MODIFY FILE (NAME = 'MyOldDB_log', NEWNAME = 'MyNewDB_log')

Thanks

Kristen

Kristen
Test

22859 Posts

Posted - 2004-04-23 : 06:34:23

Forgot to include this, in case it is useful

Kristen


RESTORE FILELISTONLY FROM DISK = 'D:\DATA\MSSQL\BACKUP\MyOldDB_040423_0552_GoLive.BAK'

LogicalName     PhysicalName                Type FileGroupName Size       MaxSize                
--------------- --------------------------- ---- ------------- ---------- --------------
MyOldDB_data D:\DATA\MSSQL\DATA\MyOldDB.mdf D    PRIMARY       1007353856 35184372080640
MyOldDB_log  D:\DATA\MSSQL\DATA\MyOldDB.ldf L    NULL          115081216  35184372080640

Kristen
Test

22859 Posts

Posted - 2004-04-23 : 08:38:22

AGGGGGGGGGggggggggggggggggghhhhhhhhhhhhhhh!

Would you Adam&Eve it, that BAK file was from a differential backup. I've spent most of the morning faffing around trying to work out what was going wrong and in desperation let Enterprise Manager sort it out - which immediately offered to grab the original BACKUP and then the DIFF. How embarassing is that!

Lousy, misleading, error messages :-(

I now see that RESTORE HEADERONLY FROM DISK returns the BackupType, so I'll have to build that into my RESTORE SProc for safety.

Bother! Bother! Bother!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources