Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 1st, 2008, 09:23 AM
Registered User
 
Join Date: Aug 2006
Location: , , United Kingdom.
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.

Reply With Quote
  #2 (permalink)  
Old April 1st, 2008, 10:44 AM
Authorized User
 
Join Date: Jun 2003
Location: Colombo 04, WP, Sri Lanka.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Preethi Send a message via Yahoo to Preethi
Default

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
Reply With Quote
  #3 (permalink)  
Old April 1st, 2008, 11:14 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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.

-Peter
peterlanoie.blog
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:33 AM.


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