none
Restore error

    Question

  • I have taken Backup of msdb on my local machine and restored.

    then When I try to restore one database along with transaction logs on my machine ..

    it gives me the following error.....

     

    Restore failed for Server 'WRCP2F1CWS315'.  (Microsoft.SqlServer.SmoExtended)

    System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\helpdesk.mdf' is claimed by 'Helpdesk_01'(3) and 'Helpdesk'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

     

    Please let me know how I can restore the database on my local machine....

     

    Wednesday, July 21, 2010 5:56 AM

Answers

  • See example from BOL

    USE master
    GO
    -- First determine the number and names of the files in the backup.
    -- MyNwind_2 is the name of the backup device.
    RESTORE FILELISTONLY
       FROM DISK='C:\db.bak'
    -- Restore the files for MyNwind2_Test.
    RESTORE DATABASE MyNwind2_Test
       FROM DISK='C:\db.bak'
       WITH RECOVERY,
       MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
       MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 6:29 AM
    Answerer
  • I think it is unable to locate the folder into which you are trying to restore the log file. Below is the syntax that you may use to get this done:

    RESTORE FILELISTONLY FROM DISK = <Path with the backup file name>

    This command gives you the logical file names that are associated with the backup file. Use the below command to restore the database:

    RESTORE DATABASE <DBName> FROM DISK = <Path with the backup file name>

    WITH MOVE <File1> TO <Location/Filename>

    , MOVE <File2> TO <Location/Filename>

    This way it will be very clear for you to know to which location the file is being moved while restoring. Hope this helps.


    Phani Note: Please mark the post as answered if it answers your question.
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 6:16 AM
  • Hi Jaspatil,

    It is quite possible that the file size is more than the backup size since the mdf and ldf files are compressed while taking the backup. So, please try with the same method by restoring the file on to different physical drive which has enough amount of space.

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    • Unmarked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 8:58 AM

All replies

  • I think it is unable to locate the folder into which you are trying to restore the log file. Below is the syntax that you may use to get this done:

    RESTORE FILELISTONLY FROM DISK = <Path with the backup file name>

    This command gives you the logical file names that are associated with the backup file. Use the below command to restore the database:

    RESTORE DATABASE <DBName> FROM DISK = <Path with the backup file name>

    WITH MOVE <File1> TO <Location/Filename>

    , MOVE <File2> TO <Location/Filename>

    This way it will be very clear for you to know to which location the file is being moved while restoring. Hope this helps.


    Phani Note: Please mark the post as answered if it answers your question.
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 6:16 AM
  • See example from BOL

    USE master
    GO
    -- First determine the number and names of the files in the backup.
    -- MyNwind_2 is the name of the backup device.
    RESTORE FILELISTONLY
       FROM DISK='C:\db.bak'
    -- Restore the files for MyNwind2_Test.
    RESTORE DATABASE MyNwind2_Test
       FROM DISK='C:\db.bak'
       WITH RECOVERY,
       MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
       MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 6:29 AM
    Answerer
  • @ subnivis. I tried that query provided by u... My database backup is of 12 gb and my c drive free space is 26 gb but still it is giving me the following error.. Msg 3257, Level 16, State 1, Line 1 There is insufficient free space on disk volume 'C:\' to create the database. The database requires 93994024960 additional free bytes, while only 26274910208 bytes are available. Msg 3119, Level 16, State 4, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
    Wednesday, July 21, 2010 7:16 AM
  • @ URI I tried your query... My database backup is of 12 gb and my c drive free space is 26 gb but still it is showing the same error....... Msg 3257, Level 16, State 1, Line 1 There is insufficient free space on disk volume 'C:\' to create the database. The database requires 93994024960 additional free bytes, while only 26274910208 bytes are available. Msg 3119, Level 16, State 4, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
    Wednesday, July 21, 2010 7:19 AM
  • Hi Jaspatil,

    It is quite possible that the file size is more than the backup size since the mdf and ldf files are compressed while taking the backup. So, please try with the same method by restoring the file on to different physical drive which has enough amount of space.

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    • Unmarked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    • Marked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 8:58 AM
  • to all,

            Thank you very much ...

    It was helpful.....

    • Marked as answer by jaspatil Wednesday, July 21, 2010 10:59 AM
    • Unmarked as answer by jaspatil Wednesday, July 21, 2010 11:00 AM
    Wednesday, July 21, 2010 10:59 AM