SQL 2000 - Database Restore Issue - Holding up SQL 2005/8 upgrade project!

I need to do some testing/upgrade work on our SQL-based database system in order to upgrade our front-end client software from 2.1 to 2.7.

The upgrade/testing process isn’t too bad, just a case of updating a few table schemas & introducing new fields/types. Once the database work is complete, it’s just a case of installing the latest client software.

However, I’m having some issues restoring the database to a test environment...
I’ve restored the disk image of the server (from Acronis True Image) onto a spare PC and it’s working great as an offline carbon copy of the server configuration. Next, I added in a 160GB HDD into the test PC ready to restore our SQL database to.

Currently, the database is around 90GB. First of all, I verified the backup:

“restore filelistonly from disk = ‘D:\SQLBackup\SERVERNAME-SQL-BACKUP-20110309.060035.420-FULL.bak’.

The result of this  command was fine and the 3 data files were listed:
ProNet_Data – 90GB – ProNet_Data.mdf
ProNet_CVDATA – 1MB – ProNet_CVData.ndf
ProNet_Log – 2GB – ProNet_Log.ldf

Next, I used the following command to attempt a restore (note: the disk I’m restoring the DB to is labelled E: on both the live and test server):
“Restore database pronet from disk = ‘D:\SQLBackup\SERVERNAME-SQL-BACKUP-20110309.060035.420-FULL.bak’.

This command runs for 33 mins, then fails with the following error:
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance.

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally

When I go to Enterprise Manager, the database is greyed out and has the (Loading) status.

I cannot understand why this error is occurring. There is 134GB free on the hard disk I’m restoring it too and there’s no other processes taking over the machine.

I've tried formatting the spare HDD so it's completely empty and tried restoring the disk image to another PC - the problem still remains the same.

Please could you let me know your thoughts as I’m desperate to start work on this upgrade project but cannot do anything until I get a test database restored. Once the client is up to date we'll be upgrading to SQL 2005/2008.

If I get time this weekend, I'll VPN-in and run a restore on the live server while no users are logged on. (restore it as another database name in a different folder to the live DB).

Any questions, feel free to ask.

Thanks in advance!
Rob SamuelIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
I'm afraid backup file is corrupted, check it by:


Open in new window

Rob SamuelIT ManagerAuthor Commented:
Hi, I've run the VERIFYONLY command and the result after 20 mins was:

"The backup set is valid"

Any ideas as to what the issue could be? Otherwise my next step was to either detach/copy the live DB out of hours or do a restore to a test DB out of hours.

Daniel_PLDB Expert/ArchitectCommented:
Whre did you verified backup file? You should verify it on your test location.
Do you have the same volume letters on the test server? When you don't have the same volumes you need to restore your database using WITH MOVE clause.
--first check logical file names

--next restore database with moving files
WITH MOVE 'ProNet_Data' TO '<new path to this file>\ProNet_Data.mdf',
WITH MOVE 'ProNet_CVDATA' TO '<new path to this file>\ProNet_CVData.ndf',
WITH MOVE 'ProNet_Log' TO '<new path to this file>\ProNet_Log.mdf'

Open in new window

Check also whther you are restoring from compressed directory.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Rob SamuelIT ManagerAuthor Commented:
I verified the backup file onto the test machine, the volume letters are the same:

C: & D: - primary HDD
E: - Secondary HDD (and SQL data location)

The backup location (D:) isn't compressed either.

Rob SamuelIT ManagerAuthor Commented:
I'll try the restore command with move and see if that makes a difference. It usually fails after 35 mins, so I will post the results as they appear.
Daniel_PLDB Expert/ArchitectCommented:
Are you able to take another backup of that database to restore? It seems that your backup is corrupted. Verifyonly option only allows to check whther backup is readable, there is no guarantee that database will successfully restore.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob SamuelIT ManagerAuthor Commented:
Hi, last night I detached/copied/re-attached the database, and took a copy into our test environment. It's re-attached OK, and I'm just re-building the CV indexes now.

I will check the validity/integrity of the backups just in case something isn't backing up correctly.

Many thanks for your help & tips!
Daniel_PLDB Expert/ArchitectCommented:
Please also check database consistency - DBCC CHECKDB - http://msdn.microsoft.com/en-us/library/aa258278%28v=sql.80%29.aspx
Rob SamuelIT ManagerAuthor Commented:
Thanks, I'm just waiting for the document indexes to rebuild then I'll have a look at the DBCC checks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.