Backing up and restoring Windows 2000 database with binary fields

noirfan
noirfan used Ask the Experts™
on
I have a database backup that came from a Microsoft SQL Server 2000 install.  I've restored it to a Microsoft SQL Server 2008 R2 machine and all the fields (there are about three in each row) that have binary data in them are all showing as NULLs.   Is there some special way the backup has to be executed to include these?  I just used the standard backup Task via SSMS.   Or is the issue that I need to restore it a Microsoft SQL Server 2000 machine?  I'm trying to avoid having to spin one up if I can avoid it...but if that's my only option here...  Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You cannot do anything more than perform a backup. Binary data should not be an issue. What type are those empty fields now, and is it the same as on 2000 (if you know that)?

Author

Commented:
Values show as NULL in the restored SQL 2008 R2 database.  In the source system they show as BINARY.   As far as type, in SQL 2008 R2 they show as image; in the source SQL2K system they show as image, as well.
Distinguished Expert 2017
Commented:
Often you have to go through SQL 2005 first to maintain /transition through ...

Upon restore, did you change the DB mode to 80 ( to match SQL 2000)

What is the issue?  Older DB likely has older code which ....... Might not be functioning as ....

Author

Commented:
The restore to 2005 seems to have done the trick.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial