Post reply

RESTORE DATABASE

  • October 23, 2007 at 3:00 pm

    #376004

    I am attempting to restore a database on a different server via QA from a full backup. The syntax I'm using is as follows:

    RESTORE DATABASE TEST_SQL

    FROM DISK = '\\SQL\PROD\Prod_db_200710230300.BAK'

    WITH MOVE 'Prod_Data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\Prod_Data.MDF'

    , MOVE 'Prod_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\Prod_Log.LDF'

    I am getting the following errors:

    Server: Msg 5105, Level 16, State 2, Line 4

    Device activation error. The physical file name 'F:\MSSQL\Data\Prod_Data.MDF' may be incorrect.

    A similar error pops for the log file. The paths in the error are those of the physical files for the Prod database on other server.

    I double-checked all paths and they are correct. Could this be a network permissions issue? Does the restore process somehow validate the physical file paths of existing database before it restores to a different server?

    Thanks in advance.

  • October 23, 2007 at 9:10 pm

    #744732

    Just so I'm clear, you are running this through SSMS connected to QA, correct?

    I wouldn't think network permissions would matter other than the copy. Are you positive the logical names are correct?

    Can you copy the file locally and see if that makes a difference?

  • October 24, 2007 at 7:05 am

    #744935

    I am using QA only, not Management Studio. The logical names are correct. I'll copy the backup locally and see if that works.

  • October 24, 2007 at 10:25 am

    #745034

    I copied the backup file locally to do the restore and received the same error. Now I'm really confused!

  • October 24, 2007 at 12:16 pm

    #745068

    Are you certain these files don't already exist on the server? If they do, delete them and/or move them to an alternate location.

    i.e. 'F:\MSSQL\Data\Prod_Data.MDF'

    Tommy

    Follow @sqlscribe

  • October 24, 2007 at 1:43 pm

    #745097

    Suggestion: for errors like these always use stored procedure restore_filelistonly from disk = 'C:\Test.bak' this will show you how the backup set looks like.

    Also use the option "WITH MOVE" if the physical file location in the backup set and the location where you want to restore do not match.

    I hope this helps!!!!

    Razi, M.
    http://questivity.com/it-training.html

  • October 24, 2007 at 1:57 pm

    #745107

    Yes, all good suggestions. I ran RESTORE FILELISTONLY to verify logical file names. I also am using WITH MOVE as noted in the script above. That is what's so confusing. As far as I can tell, everything is how it should be.

  • October 24, 2007 at 1:59 pm

    #745109

    Reply to TommyB - the paths don't even exist on the server where the database is being restored. The paths noted in the error are from the server where the database originally resides.

  • October 25, 2007 at 4:56 pm

    #745516

    Cy, That's very strange. I'm not sure what it isn't working.

    It sounds like there must be something I'm missing, but not sure what it is. If you open the backup in SSMS (as a restore) , does it show the meta data (dates, file names, etc.) you expect?

  • October 26, 2007 at 8:45 am

    #745765

    I figured the problem out. The drives where the .mdf and .ldf files reside (on live server) have to be shared. For some reason, SQL Server must verify the original location of the files before it does a restore to another server. I am attempting to do the restore using the RESTORE DATABASE command because it is currently done manually via EM. I want to automate the process which is how I came upon this problem. EM must not verify the original paths of the database files.

    Thanks to all for your responses.

Viewing 10 posts - 1 through 9 (of 9 total)

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