Post reply

Restore database to a different Server

  • 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

  • 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

  • July 10, 2003 at 6:43 am

    #464831

    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

  • July 10, 2003 at 11:38 am

    #464946

    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.

  • July 10, 2003 at 3:12 pm

    #464978

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

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

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