backup from one server, restore to another

[Rich]

Hi there,

I've been sent a backup file from a SQL Server 2000 DB and tried
restoring into a blank DB (with the same name) on my SQL Server 2K,
only it errors, see below:
(I've separated each line with a return, to make it more readable)

2005-07-08 11:08:23.93 spid51 BackupIoRequest::WaitForIoCompletion:
read failure on backup device 'C:\file location to backup'. Operating
system error 87(The parameter is incorrect.).

2005-07-08 11:08:23.94 spid51 Internal I/O request 0x03BDFCEC: Op:
Read, pBuffer: 0x03BDF800, Size: 512, Position: 5136, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0x1410, OffsetHigh: 0x0, m_buf:
0x03BDF800, m_len: 512, m_actualBytes: 0, m_errcode: 87, BackupFile:
C:\file location to backup

2005-07-08 11:08:23.94 spid51 The backup data in 'C:\file location
to backup' is incorrectly formatted. Backups cannot be appended, but
existing backup sets may still be usable.

2005-07-08 11:08:23.94 spid51 Starting up database 'databizDB'.

2005-07-08 11:08:24.02 spid51 Bypassing recovery for database
'databizDB' because it is marked IN LOAD.

2005-07-08 11:08:24.16 spid51 BackupMedium::ReportIoError: read
failure on backup device 'C:\file location to backup'. Operating system
error 87(The parameter is incorrect.).

2005-07-08 11:08:24.16 spid51 Internal I/O request 0x42FC5530: Op:
Read, pBuffer: 0x03980000, Size: 512, Position: 5648, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0x1610, OffsetHigh: 0x0, m_buf:
0x03980000, m_len: 512, m_actualBytes: 0, m_errcode: 87, BackupFile:
C:\file location to backup

It may be something simple as I've never really got that involved with
this side of it before. Hopefully someone out there can shed some light
on it, please?

This may be normal, but I had to backup the blank DB locally first
before it would let me select a file to restore, so I could change the
location.

Cheers,
Rich.

[joshsackett]

Be sure you have the CORRECT name next to 'Restore as database:' in
order for these steps to work:

When restoring the database, select the 'From device' radio button.
Then click 'Select Devices...' then click 'Add' then input the file
location and name. Click 'OK', 'OK' and then click on the 'Options' tab
and check 'Force restore over existing database'.

Under 'Restore database files as:' and 'Move to physical file name'
change those folders to the correct location. Click 'OK'.

You will be restoring over your blank database.

[Rich]

Hi joshsackett,

Thanks for the advice, I tried the Restore the way you suggested
(through 'From device') only I get the same error.

I'm starting to think that maybe the backup file I have is corrupt, as
I didn't do the original back, plus when I click on View Contents...
after selecting the file I get the following error. 'Error 3013:
RESTORE HEARDERONLY is terminating abnormally.'

I've defiantly got the right DB as I right click and select Restore.
I've even tried it on another SQL Server 2K, with the same results.

Can you think of anything else it might be?

Many thanks,
Rich.
joshsackett wrote:

Be sure you have the CORRECT name next to 'Restore as database:' in
order for these steps to work:

When restoring the database, select the 'From device' radio button.
Then click 'Select Devices...' then click 'Add' then input the file
location and name. Click 'OK', 'OK' and then click on the 'Options' tab
and check 'Force restore over existing database'.

Under 'Restore database files as:' and 'Move to physical file name'
change those folders to the correct location. Click 'OK'.

You will be restoring over your blank database.

[joshsackett]

Run this command from Query Analyzer:

RESTORE FILELISTONLY
FROM DISK = 'C:\BACKUPS\MyDb.BAK'

If you get an error then it is quite possible the database is
corrupted. Maybe it was backed up using a 3rd party application like
SQL LiteSpeed? In that case you'd need to have the same application on
your side to perform the restore.

[Rich]

Hi Josh,

Many thanks for that, it seems to have sorted it, plus I've also
installed Service Pack 3a.

Cheers,
Rich.