Restore database to a different Server

Question

Post reply

Restore database to a different Server

meghardy

Default port

Points: 1453
More actions
July 9, 2003 at 4:56 am

#159384

Hi,

I am trying to update a database on the test server to be the same as our live server. What I thought I should do, is copy the most recent backup from live to test, then I deleted the database on the test, recreated another database with the same name, and tried to do a restore using the live Backup file.

It didnt work! The error is this:

"The backup set holds a backup of a database other than the existing 'dbname' database. Backup or restore operation terminating abnormally"

Please help,

Thanks, Meg

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

meghardy Default port Points: 1453 More actions · Answer 1

meghardy

Default port

Points: 1453

July 9, 2003 at 5:23 am

#464665

Ignore me, I found a check box on the options page that said to force restore over an existing database.

Cheers anyway.

Meg

devereauxj Hall of Fame Points: 3263 More actions · Answer 2

What version of SQL Server?

if 2000, you should be able to detach the database on test. Delete physical files. Do a restore from the backup you took. On the restore you can rename the physical files and place them where ever you want by changing the patch. Once restored you can use "alter database <db> modify file (name, newname)" to change the logical name.

Joseph

Stamey SSCertifiable Points: 5582 More actions · Answer 3

For future refence, just write a script to do this job for you, then you don't need to create the DB ahead of time.

Drop Database [DB Name]

Go

Restore Database [DB Name]

from Disk = [path and name for backup]

With Replace,

Move '[Logical data file name]' To '[Physical path and name of data file]',

Move '[Logical log file name]' To '[Physical path and name of log file].

Stats=5

to get the logical file names and physical file name for this script you can plug your backup name into this script:

Restore FileListOnly

from Disk = [path and name for backup file]

Plug in the correct information where the brackets are, and you have a script to take care of it for you in one click.

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

jgarp Grasshopper Points: 14 More actions · Answer 4

Here is a script I created based on a whole lot of code that I actually got off this site (and found very very useful). It's what the previous response has done, but I've just tried to make it resuable so I just have to plug in the names of the db and file locations. Hope you find it useful:

--Run each step separately as you need the results from Step 1 for Step 2.

--STEP ONE

--To perform this via Query Analyzer, first do (adjust file and path accordingly):

DECLARE @BackUpFileLocation varchar(200)

DECLARE @DatabaseName varchar(50)

SET @BackUpFileLocation='C:\My Documents\CurrentBackups\MyDatabaseName.bak'

SET @DatabaseName = 'MyDatabaseName'

RESTORE FILELISTONLY FROM DISK = @BackUpFileLocation

GO

--STEP TWO

--Now take note of the logical file names listed after executing the STEP ONE Restore Filelistonly statement

--and set variables below accordingly

DECLARE @LogicalNameData varchar(100)

DECLARE @LogicalNameLog varchar(100)

DECLARE @DatabaseName varchar(50) --same as above, repeat as separate transactSQL statement

DECLARE @BackUpFileLocation varchar(200) --same as above

--Add the appropriate values here for the variables

Set @LogicalNameData='MyDatabaseName_Data'

SET @LogicalNameLog='MyDatabaseName_log'

SET @DatabaseName = 'MyDatabaseName'

SET @BackUpFileLocation='C:\My Documents\CurrentBackups\MyDatabaseName.bak'

--Now do (replace @LogicalNameData (eg'Northwind_Data') and @LogicalNameLog (eg'Northwind_Log')

--with the logical file names from the previous step and adjust the paths as necessary):

DECLARE @RestoreDataToLocation varchar(200) --Location on server where the data file is to reside after restore

DECLARE @RestoreLogToLocation varchar(200) --Location on server where the log file is to reside after restore

SET @RestoreDataToLocation ='C:\Program Files\Microsoft SQL Server\MSSQL$MyDatabaseName\Data\MyDatabaseName_data.MDF'

SET @RestoreLogToLocation='C:\Program Files\Microsoft SQL Server\MSSQL$MyDatabaseName\Data\MyDatabaseName_Log.LDF'

RESTORE DATABASE @DatabaseName

FROM DISK =@BackUpFileLocation

WITH RECOVERY, MOVE @LogicalNameData TO @RestoreDataToLocation,

MOVE @LogicalNameLog TO @RestoreLogToLocation

GO

/*

--STEP THREE OPTIONAL

--Once that is complete, you may optionally correct the logical file names by doing (adjust logical name as necessary):

DECLARE @DatabaseName varchar(50) --same as above eg 'Northwind'

DECLARE @OldFileName varchar(50) --eg 'Northwind_Log'

DECLARE @NewFileName varchar(50) --eg'TestNorthwind_Log'

SET @DatabaseName = 'MyDatabaseNameLog'

SET @OldFileName=''

SET @NewFileName=''

ALTER DATABASE @DatabaseName

MODIFY FILE (NAME = @OldName, NEWNAME = @NewName)

GO

ALTER DATABASE [TestNorthwind]

MODIFY FILE (NAME = 'Northwind_Log', NEWNAME = 'TestNorthwind_Log')

GO

--Do this so that the logical names to reflect correct database names.

*/

Also, congradulations on a great site - I read it every day and learn heaps!!!