restore BAK file to different server

  • I am trying to restore a database to a different server from where the backup was created using the following script:

    RESTORE FILELISTONLY

    FROM DISK = 'C:\Extract\REZ_db_201506240733.BAK'

    RESTORE DATABASE [REZ]

    file = 'DB_1',

    file = 'REZ_bio',

    file = 'REZ_gift',

    file = 'REZ_index',

    file = 'REZ_temp'

    FROM DISK = N'C:\Extract\REZ_db_201506240733.BAK' WITH FILE = 1,

    MOVE N'DB_1' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_bio' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_gift' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_index' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_temp' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'DB_1_log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ_mst_1.ldf',

    NOUNLOAD, STATS = 10

    GO

    and I am getting the following errors:

    (6 row(s) affected)

    Msg 3176, Level 16, State 1, Line 4

    File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_bio'(3) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3176, Level 16, State 1, Line 4

    File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_gift'(4) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3176, Level 16, State 1, Line 4

    File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_index'(5) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3176, Level 16, State 1, Line 4

    File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_temp'(6) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3013, Level 16, State 1, Line 4

    RESTORE DATABASE is terminating abnormally.

    Although I have done this successfully a couple of other times before, I am not very knowledgeable about the restore process.

    Can someone help me figure out what I am doing wrong?

    I appreciate any help I can get.

    Thanks,

    Sup

  • Can you post the output of this please

    RESTORE FILELISTONLY

    FROM DISK = 'C:\Extract\REZ_db_201506240733.BAK'

    The problem you have is the fact you're specifying the same file multiple times in the MOVE clause

    RESTORE DATABASE [REZ]

    file = 'DB_1',

    file = 'REZ_bio',

    file = 'REZ_gift',

    file = 'REZ_index',

    file = 'REZ_temp'

    FROM DISK = N'C:\Extract\REZ_db_201506240733.BAK' WITH FILE = 1,

    MOVE N'DB_1' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_bio' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\ REZ.mdf'

    MOVE N'REZ_gift' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_index' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'REZ_temp' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf',

    MOVE N'DB_1_log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ_mst_1.ldf',

    NOUNLOAD, STATS = 10

    GO

    The error detail here tells you what the problem is

    File 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLX2008WKS\MSSQL\DATA\REZ.mdf' is claimed by 'REZ_temp'(6) and 'DB_1'(1). The WITH MOVE clause can be used to relocate one or more files.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I corrected the file names on the TO side of the move clauses and that did it!

    I appreciate your help very much Perry!

    Thanks,

    Sup

  • you're welcome

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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