What are we doing wrong? We get an "internal consistency" error when
we attempt to restore our SQL database to a new machine (running
Win2000). We're running a web site using Cold Fusion 5.0 (running on a
Win2000 machine) and SQL7 (running on an NT machine). The database
we're attempting to restore is spread out over two logical drives.

---

Request for Question Clarification by axe-ga on 13 May 2002 17:24 PDT

Hi,

Have you tried re-creating the backup set? This information would be
useful in answering your question.

Thanks,

Axe

Hi there!

Our search returned the following results:

The Windows IT Library contains a comprehensive online book for
administrating Microsoft SQL Server. Though was created when 6.5 was
the current version the version you are running is the same with
respect to data backup and recovery.
http://www.windowsitlibrary.com/Documents/Book.cfm?DocumentID=77

Chapters 12 and 13 of this guide are perhaps most pertinent

Chapter 12: SQL SERVER DATABASE BACKUP 
http://www.windowsitlibrary.com/Content/77/12/toc.html

Chapter 13: SQL SERVER DATABASE RECOVERY
http://www.windowsitlibrary.com/Content/77/13/toc.html

This note in Chapter 13 places reference to Chapter 12 when warning
how to avoid "internal consistency" errors:

"Special Note: As discussed in Chapter 12, “Database Backups,” it is
very important that the internal consistency of a database be checked
before a dump is made. Dumping a database that has inconsistencies
will give you a database that cannot be loaded. Always run consistency
checks before and after dumping and loading a database."
http://www.windowsitlibrary.com/Content/77/13/2.html

Chapter 12 provides the reader with an idea of HOW to back up their
database. In an extreme majority of the cases, a proper backup will
result in a proper restoration. Pay particular attention to the
section about "Developing a Backup Strategy".

After a thorough search for documented problems when moving an SQL
Server database from WinNT to Win2000, I must come to the conclusion
that the problem is independant of the operating systems involved.

The fact that your data is spread over two logical drives is a
possible reason for concern, since when you backup a database the
system assumes that it will be restored onto a system with exactly the
same physical properties. Logical drives, file system type, and disk
space fall into this category among other things. More information on
logical drives on a Windows 2000 machine is available here:
http://www.microsoft.com/windows2000/en/server/help/default.asp?url=/windows2000/en/server/help/dm_create_partitions.htm

If you are not backing up the ENTIRE database as is, you may come
across issues dealing with referential integrity (ie. not including
tables that have defined relationships with included tables).

In summary, there is no "clear-cut" solution for this issue. By
following through with the backup strategy you will gain a stronger
understanding of how your database stores information and will come to
realize where the problem lies.

All the best in your quest :)
if you need help with any of the information here please post a
clarification and i will be happy to help.

Cheers,
answerguru-ga

Hi dbenatar-ga!

Please refer to the following URL, which contains a post by someone
who had what appears to be the exact same problem you are having, and
found a solution:

http://groups.google.com/groups?hl=en&threadm=%23hB3mJmvBHA.2092%40tkmsftngp03&rnum=7&prev=/groups%3Fq%3Dinternal%2Bconsistency%2Bsql%2Berror%26hl%3Den%26selm%3D%2523hB3mJmvBHA.2092%2540tkmsftngp03%26rnum%3D7

Let me know if that helps.