Search code examples
sql-serverdatabasedatabase-restore

Restore SQL Server database backup of one machine to another


Scenario:

  • I have a database backup (abc.bak) on one machine
  • I copied it to another machine to some path let's say G:\SQLDB\backup\master_copy.bak
  • I used the following T-SQL to try and restore - but I get errors.

T-SQL used:

RESTORE DATABASE New_DB
FROM DISK = 'G:\SQLDB\backup\master_copy.bak' 
WITH 
    MOVE 'coop_test_dat' TO 'G:\SQLDB\livedb\new_db_data.mdf',
    MOVE 'coop_test_log' TO 'G:\SQLDB\livedb\new_db_log.ldf',
    REPLACE

Error generated:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\SQLDB\masterdb\master_blank.mdf" failed with the operating system error 3(The system cannot find the path specified.).

Msg 3156, Level 16, State 3, Line 1
File 'coop_demo' cannot be restored to 'C:\SQLDB\masterdb\master_blank.mdf'. Use WITH MOVE to identify a valid location for the file.

Here given specified path is from old machine which doesn't exists in new machine.

How can I fix this?


Solution

  • what Jeroen suggested was, you might be missing some files..so to see all files use below command..

    restore filelistonly from disk ='your .bak path' with file=1
    

    The output of above command will show you all the files that were backed up,then you can use restore with replace

    so if restore file list showed below as logical file names

    logicalname  
    db_Data
    db_Data1
    db_log
    

    now you can use

    RESTORE DATABASE New_DB
    FROM DISK = 'G:\SQLDB\backup\master_copy.bak' WITH 
    MOVE 'db_Data' TO 'G:\SQLDB\livedb\new_db_data.mdf',
    MOVE 'db_Data1' TO 'G:\SQLDB\livedb\new_db_data1.ndf',
    MOVE 'db_lof TO 'G:\SQLDB\livedb\new_db_log.log',
    REPLACE