Restore gives 'invalid logical page number' error

[Janet McQuade]

We tried to restore an empty database from a backup and got:
'Load database encountered an invalid logical page number' error.
This then set the database as 'loading'

Anyone got any ideas?

We aren't exactly sure how big the first database was when the backup was done but if the new database isn't big enough to contain the backed up data, wouldn't we get a more meaningful error?

Janet

[Ed Alexander]

This sounds like a device size issue. I'm assuming you are using SQL Server 6.5.

When restoring a database, the target database must reside on database devices that are defined exactly as the database devices were when the database was backed up.

For example:
Let's say you create a database device that is 100mb. Then you extend the device by another 100mb. You create a database now that is 200mb. Now you are using the database and taking regular backups.

Now a problem occurs where you need to drop the database and restore. You must create a device that is 100mb and extend it by another 100mb. Then create the database and restore. You CANNOT just create a device of 200mb. This will cause your restore to fail or when restore, corrupted page links.

If you are unsure of your original device sizes and have a master database backup from when the original devices existed, you could:
1. backup your current master
2. restore the old master from when the original devices existed
3. get the device sizes
4. restore the current master back

The other option is if you have a development box, restore the old master to that box so you are messing with a production box. Then query the sysdevices and sysusages tables to get the device allocations.

The Key is that the devices restore to MUST be exactly the same as the ones you backed up from.

Drop me an email if you have any other questions.


------------
Janet McQuade at 6/24/99 11:53:54 AM

We tried to restore an empty database from a backup and got:
'Load database encountered an invalid logical page number' error.
This then set the database as 'loading'

Anyone got any ideas?

We aren't exactly sure how big the first database was when the backup was done but if the new database isn't big enough to contain the backed up data, wouldn't we get a more meaningful error?

Janet

[Cindy Gross]

I searched sysmessages and found error 3104 for your message. The only thing I could find for that message on TechNet had to do with a backup to DLT tape.

As for the devices having to be created exactly the same, there is an additional twist. The data and log have to be created in the same ORDER, but not necessarily identically. For instance, 100MB data + 100MB data + 50MB log + 100MB data could be created in any of the following ways:

100MB data + 100MB data + 50MB log + 100MB data
200MB data + 50MB log + 100MB data
30MB data + 70MB data + 100MB data + 50MB log + 100MB data (or other variations as long as data and log are in the same order)

Generally a violation of this order will not prevent you from loading, but you might start getting strange errors about the log being full when it doesn't appear to be or being out of data space when you think there is some left. See dbcc error 2558 for more information.

------------
Craig at 6/24/99 1:58:01 PM

Ed's statement while correct is not always the case.

However in your case (and without an error number, what number did you get?) it appears if you follow his advice you will be in good shape. It is not the size, you would have gotten another error message.


------------
Ed Alexander at 6/24/99 1:23:06 PM

This sounds like a device size issue. I'm assuming you are using SQL Server 6.5.

When restoring a database, the target database must reside on database devices that are defined exactly as the database devices were when the database was backed up.

For example:
Let's say you create a database device that is 100mb. Then you extend the device by another 100mb. You create a database now that is 200mb. Now you are using the database and taking regular backups.

Now a problem occurs where you need to drop the database and restore. You must create a device that is 100mb and extend it by another 100mb. Then create the database and restore. You CANNOT just create a device of 200mb. This will cause your restore to fail or when restore, corrupted page links.

If you are unsure of your original device sizes and have a master database backup from when the original devices existed, you could:
1. backup your current master
2. restore the old master from when the original devices existed
3. get the device sizes
4. restore the current master back

The other option is if you have a development box, restore the old master to that box so you are messing with a production box. Then query the sysdevices and sysusages tables to get the device allocations.

The Key is that the devices restore to MUST be exactly the same as the ones you backed up from.

Drop me an email if you have any other questions.


------------
Janet McQuade at 6/24/99 11:53:54 AM

We tried to restore an empty database from a backup and got:
'Load database encountered an invalid logical page number' error.
This then set the database as 'loading'

Anyone got any ideas?

We aren't exactly sure how big the first database was when the backup was done but if the new database isn't big enough to contain the backed up data, wouldn't we get a more meaningful error?

Janet