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

Question

Post reply

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

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 13, 2009 at 12:19 am

#224746

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;-)

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

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

ashwin4all1 Ten Centuries Points: 1131 More actions · Answer 1

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 ...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

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;-)

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

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;-)

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 5

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

Brett Anderson-332664 Valued Member Points: 57 More actions · Answer 6

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.