locked
SQL Server 2012 RESTORE from Database ERROR RRS feed

  • Question

  • When I script out RESTORE from DATABASE GUI from Object Explorer, I get an error:

    USE [master]
    RESTORE DATABASE [newAdventureWorks2008] FROM  DISK = N'{3192371C-A227-42E0-82EB-208B6F9857A2}3' 
    WITH  FILE = 1,  MOVE N'FileStreamDocuments' TO N'F:\DATA\CopyOfAdventureWorks2008_2.DocumentsA',  
    MOVE N'AdventureWorks2008_Data' TO N'F:\DATA\CopyOfAdventureWorks2008A.mdf',  
    MOVE N'AdventureWorks2008_Log' TO N'F:\DATA\CopyOfAdventureWorks2008_1A.ldf',  NOUNLOAD,  STATS = 5
    GO
    /*
    Msg 3201, Level 16, State 2, Line 2
    Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\
    {3192371C-A227-42E0-82EB-208B6F9857A2}3'. Operating system error 2(The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.
    */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Friday, June 15, 2012 5:57 AM
    Answerer

Answers

  • The error seems obvious: there is no file named: 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\
    {3192371C-A227-42E0-82EB-208B6F9857A2}3'.

    To say more we need to know exactly what actions you performed to script out this restore command (where you clicked, what backups has been performed etc). My guess is that you have a 3:rd party backup solution using the VDI device and restore has to be done from that software. And SSMS isn't clever enough to realize that so it scripts out an unusable command. If that is the case, I suggest you construct a repro and report at connect.microsoft.com.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, June 15, 2012 6:38 AM

All replies

  • The error seems obvious: there is no file named: 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\
    {3192371C-A227-42E0-82EB-208B6F9857A2}3'.

    To say more we need to know exactly what actions you performed to script out this restore command (where you clicked, what backups has been performed etc). My guess is that you have a 3:rd party backup solution using the VDI device and restore has to be done from that software. And SSMS isn't clever enough to realize that so it scripts out an unusable command. If that is the case, I suggest you construct a repro and report at connect.microsoft.com.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, June 15, 2012 6:38 AM
  • Thanks Tibor.

    Here is the action:

    1. SSMS Object Explorer

    2. Restore database

    3. Source Database AdventureWorks2012 (backup set window lists last backup as June 7, 2012). Same exact date info for the other db-s as well down to the minute level.

    4. Destination newAdventureWorks2012

    5. I do script out at this point:

    USE [master]
    RESTORE DATABASE [newAdventureWorks2012] FROM  DISK = N'{3192371C-A227-42E0-82EB-208B6F9857A2}4' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 5
    GO

    I did not use 3rd party backup at least intentionally. However, the same last backup date & time for the other db-s makes me wonder if some automated software attempted a backup.

    If I do a backup media verification, I get a failure.

    If I do a new backup, error goes away, scripts out as a normal disk file.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Friday, June 15, 2012 6:53 AM
    Answerer
  • <I did not use 3rd party backup at least intentionally.>

    I have a string feeling that 3:Rd party backup sw is in use here. Check out the backup history tables and you will see (or the backup history report). Note that this 3:rd party can be the windows backup functionallity that comes with the OS.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, June 15, 2012 6:56 AM