We help IT Professionals succeed at work.

Help with a SQL restore please (2000 to newer)

Hello,

I have a SQL 2000 server that is being replaced with SQL 2008.  I've taken several SQL backups of the database (125GB) and tried to restore to both SQL 2008 and SQL 2005, and I receive the following message on any of the files at 90% completion:  "Restore failed for Server "SERVER".  System.Data.SqlClient.SqlError: The file on device 'H:\filename' is not a valid MIcrosoft Tape Format backup set.  (MIcrosoft.SqlServer.Smo)"  I see conflicting information on whether this can be done at all. I find how to upgrade from 2000 (http://msdn.microsoft.com/en-us/library/ms190775.aspx), but I'm not looking for an live upgrade, meaning the existing 2000 db has to stay online for a time.

We've also tried detaching and attaching, and I'm getting errrors...  Bottom line, I need the 2000 DB on my 2008 SQL server, and don't have the expertise or confidence to write sql statements, or even know the best way.  Can anyone help a SQL newb?

TIA!
Comment
Watch Question

Can you please check the sql error log if there are any errors recorded related to this, if so please post and also use the script and run the restore in Query window instead of ssms.

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I can think of 2 things.

Your actual database backup should be more than 127GB, but the MTF format is writing only 127GB and waiting for a 2nd "tape" to write the remaining data.  127GB is a limit for some OS/file systems.

The other thing is that SQL Server always supports at least (currently, exactly) 2 prior versions. SQL 7.0 is one step too far for 2008, but 2000 is directly restorable to 2008, which is why you are able to get as far as 90% into the restore before hitting invalid/non-existent data.

Author

Commented:
Thank you!  MohammedU, I'll find it and post it, and try restore with the query window as well.  Does this look ok to you?  

RESTORE DATABASE SHDataBase
FROM DISK='d:\backup\innov'
WITH
MOVE 'Metadata_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Metadata_Data.mdf',
MOVE 'Metadata_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Metadata_Log.ldf', STATS=5

cyberkiwi, the backup itself is 75 GB, so I don't think that's an issue.  Also, I have a 2005 box giving me the same error message (not a valid tape...), and that's only one version up, correct?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
One of the dbs I work with is >1TB.  The backups sometimes fail with the same error that you are getting when the backups are done over to a NAS.  If the file is corrupt, there is no way it will restore.  The backup command never logs an error, not even to the SQL error log because SQL thinks it has passed the correct data to the OS/NAS to store.

What happened in between is a mystery, but we just redo the backup and it works.
This is backup/restore from/to Sql Server 2005.

Author

Commented:
Ok, I ran the restore as above, and received this error log:

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:978) in database "Shiloh" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Author

Commented:
cyberkiwi,

I keep rerunning the backup... I've tried USB drives and local drives... any dbcheck I maybe need to move on to?

Author

Commented:
Hi,

Can someone provide the BCP command/syntax to bulk copy ALL DATA/VIEWS to a text file?  My only option with DTS export wizard is one at a time, and that's not possible... Or a query to do the same thing?

Thanks,

Trish
Commented:
Have you ran a dbcc chedkdb on the source database to verify that it does not contain corruption?

Author

Commented:
Thank you all for your help.  This box apparently has a myriad of problems... running checkdb revealed some errors I was able to fix with a repair, there were some problem tables which we have dropped and will recreate, all this probably created by the i/o errors we are seeing on the hardware in the error log.  I can't even seem to take a backup without a failure... at least we know what the problem(s) are... will advise and close this issue soonest.