Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 1st, 2008, 09:23 AM
Registered User
Join Date: Aug 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Restore/copy database from SQL 2005 to 2000

I have a database on a SQL 2005 server that was migrated from SQL Server 2000. I migrated by backing up and then restoring the database to the SQL 2005 server.

What we'd like to do is use the old server for testing and I am trying to restore/copy the database to the old server.

I have tried restoring a backup but this terminates stating there is more than 64 headers.

I am trying to use SSIS but am being told that the source connection must have a version equal to or less than the destination.

How can I restore/copy the database from SQL 2005 to 2000? Would be grateful for any pointers.

Old April 1st, 2008, 10:44 AM
Authorized User
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Preethi Send a message via Yahoo to Preethi

I remmeber creating linked servers in SQL server 2005 pointing to SQL Server 2000. So I belive that it is possible to create a conenction in SSIS too. But you can't use Copy database here. You may have to copy tables one by one.
Moving the users/permissions may be tricky one. You may not be able to do that at all.
Moving the tables is quite possible if you dont have any new columns like vachar(MAX) or XML.

G.R.Preethiviraj Kulasingham
Old April 1st, 2008, 11:14 AM
planoie's Avatar
Friend of Wrox
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

You can set a database's compatibility level:

On the database properties dialog in SQL Server Management Studio, under "Options", change "Compatibility Level" to "SQL Server 2000 (80)". You might then be able to back it up (or detach it) and restore (attach) to the SQL2000 instance.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem to restore database in C#2005 database acmuralee MySQL 0 March 25th, 2008 04:42 AM
Upgrade 2000 DBs to 2005 or install SQL 2000/2005 cJeffreywang SQL Server ASP 0 February 13th, 2008 10:44 PM
restore thePhile database with 2005 SQL Server ecarnews BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 4 July 2nd, 2007 12:58 PM
copy a database using SQL Server 2000 samnachilomo SQL Server 2000 3 November 20th, 2006 01:15 AM
Restore SQL 2000 .bak file into SQL 7.0 ?? mat41 SQL Server 2000 9 April 8th, 2005 04:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.