April 11, 2006 at 3:56 pm
Problem: after copying a database, many, many tables are missing rows:
Background: I needed to copy Database A on Box 1 to a new SQL install on Box 2. I used EM to backup Database A. I copied the backup for Database A to Box 2 (we have the drives mapped). I created Database B on Box 2 using RESTORE DATABASE. No errors.
What we found was that Database B has all the tables, but many tables are missing rows; some a few rows, some hundreds of rows. No discernable pattern (e.g., 20%). I ran DBCC CHECKxxx on Database B, but no errors returned.
The backup file on Box 2 I used to create Database B is the same size as the one on Box 1. I wondered if the problem was that the copy was trashing the backup file in some way, so I copied the backup file back to Box 1 to a different location, and then RESTORE DATABASE to create Database C. And, wonder of wonders, Database C has all the rows that Database A has (and Database B does not).
So, now we're suspecting something about the installation of SQL on Box 2. Does anyone have any ideas. Google and MSDN have come up dry (although I may not be using the right search words).
Thanks.
April 11, 2006 at 4:23 pm
What is the database recovery model set to i.e. Full, Simple, or Bulk-logged?
Td Wilson
April 12, 2006 at 3:51 am
Is it possible that your backup file contains more than one backup ? It sounds to me like one of those classical cases where you have two or more backups in one file and during restore if you don't tell which one you want to restore, the oldest one will be picked.
Markus
[font="Verdana"]Markus Bohse[/font]
April 12, 2006 at 7:03 am
It's a simple backup recovery model, and there is only one backup in the backup (as I only did the backup step once - simplicity working for me here!).
I don't think the backup is the problem, as it restores perfectly on one instance and not perfectly on the other.
April 13, 2006 at 1:20 pm
I have no idea if it matters, but are the servers at the same service pack?
Tim S
April 13, 2006 at 2:24 pm
I had been afraid of that, but no, they are reported at the same level in Properties. I have since tried restoring other databases and gotten the same result. I don't think it is an issue with the databases themselves (although it could be as they all were created in the instance on Box 1).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply