• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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

Hi,
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!
0
Rob Samuel
Asked:
Rob Samuel
  • 5
  • 4
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
I'm afraid backup file is corrupted, check it by:

RESTORE VERIFYONLY FROM DISK='D:\SQLBackup\SERVERNAME-SQL-BACKUP-20110309.060035.420-FULL.bak'

Open in new window

0
 
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.

0
 
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
RESTORE FILELISTONLY FROM DISK='D:\SQLBackup\SERVERNAME-SQL-BACKUP-20110309.060035.420-FULL.bak'

--next restore database with moving files
RESTORE DATABASE pronet FROM DISK='D:\SQLBackup\SERVERNAME-SQL-BACKUP-20110309.060035.420-FULL.bak'
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.
0
 
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.

0
 
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.
0
 
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.
0
 
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!
0
 
Daniel_PLDB Expert/ArchitectCommented:
Please also check database consistency - DBCC CHECKDB - http://msdn.microsoft.com/en-us/library/aa258278%28v=sql.80%29.aspx
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now