Cannot find file ID 2 on device '\\hqi\C$\REPORT\CVT_REPORT_backup_200908112100.bak'

  • hi

    I ran the following script to restore database

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

    RESTORE DATABASE [CVT_REPORT]

    FROM DISK = N'\\hqi\C$\REPORT\CVT_REPORT_backup_200908112100.bak'

    WITH FILE = 2,

    MOVE N'CVT_REPORT' TO N'D:\REPORT\MSSQL.4\MSSQL\DATA\CVT_REPORT.mdf',

    MOVE N'CVT_REPORT_log' TO N'D:\REPORT\MSSQL.4\MSSQL\DATA\CVT_REPORT_log.ldf', NOUNLOAD, REPLACE, STATS = 10

    GO

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

    but i got following error

    but when i use WITH FILE = 1 option , it workrd fine .

    is it Ok , to use this FILE =1 instaed of FILE = 2

    and when we got this error ????

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi

    Bhuvnesh,

    You can't restore database from network drive ,it will take lvery long time to restore,because you mentioned in yoir from location is network path,so better move your backup file to local drive and then restore database.

    Thanks and regards

    AShwin vp

    Ashwin VP
    CSC India ...

  • Thanks for reply

    Backup is working with option FILE = 1 (it restored sucessfully)

    but not with FILE = 2, as i am getting below error

    Cannot find file ID 2 on device '\\hqi\C$\REPORT\CVT_REPORT_backup_200908112100.bak'"

    why is it working with FILE =1 ???

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • The FILE= option is only relevant when more than one backup is in a single backup file (i.e. backups are specified with NOINIT).

    If it isn't working with FILE=2, then that's because you only have a single backup in that file.

  • I restored the backup with FILE = 1 (instead of FILE =2 )

    Is it OK ? you can review thw script i sent in the first post mail (when i stared this post )

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • If you ever want to see what's actually on the backup File you can use the following:

    Restore FILELISTONLY from disk='path\file'

    (this will show you all the files listed in this backup)

    or

    Restore HEADERONLY from disk = 'path\file'

    (this will show you all the backup information for this backup)

    Tim White

  • Hi There. I had same restored the database to a different server.

    I created two backup of a database on server 1. After the second backup, the .bak file includes the information about the number and location of .bak files.

    So when I happened to be restoring the database to another server the restore script referenced "with file =2" but since the database I was restoring over was empty, the file=1 is bogus and should then become file=1. The "file=#" is relative to the database it's being imported to and originates from the database/server that it was backed up from.

Viewing 7 posts - 1 through 6 (of 6 total)

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