SQL Server Performance Forum – Threads Archive

move database in SQL7 to SQL2005

I have one database in a SQL Server 7 (7.00.1077) instance that I want to move to a SQL Server 2005 instance. What are my options in getting this done? I cannot upgrade the existing SQL Server 7 instance to SQL Server 2005. Thanks in advance for any and all assistance. Jethro

hi,
pls find the below link for ur question…….. for moving user dbshttp://support.microsoft.com/kb/314546
u can use the conventional backup and restore methods…….but to use 2005 features u need to change compatability to 90 from 70………
Regards
Deepak
SQL DBA

http://technet.microsoft.com/en-us/library/ms144245.aspx
Upgrading to SQL Server 2005 You can directly upgrade instances of SQL Server 2000 Service Pack 3 (SP3) or later, and instances of SQL Server 7.0 SP4 or later, to SQL Server 2005. You can perform most upgrade operations through Setup; however, some components support or require you to migrate applications or solutions after running Setup. For more information, see Upgrading SQL Server Components. Before running Setup to upgrade to SQL Server 2005, you should first review system requirements and the upgrade matrix. To review system requirements, see Preparing to Install SQL Server 2005. To review the upgrade matrix, see Version and Edition Upgrades. After reviewing system requirements and the upgrade matrix, run SQL Server Upgrade Advisor to analyze your instances of SQL Server 2000 and SQL Server 7.0. Upgrade Advisor produces lists of issues specific to your installations that you must fix before or after upgrading. SQL Server Setup will detect blocking issues that will prevent you from upgrading to SQL Server 2005, but will not list issues that may affect your applications. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks to everyone for their feedback. As stated, I cannot migrate the source database from 7 to 2005. I have tried to restore the database (version 7) to 2005 but encountered the following issues: Backgroup info: The database is sitting on SQL Server 7 with compatibility set to 6.5. First attempt: I backed up the database and restored it onto a SQL Server 2005 instance. The restore was successful. However, no object in the database were recognized. The database is marked "db (6.5 compatible)". Nothing can be done with it. Second attempt: I changed the source db’s compatibility to 7.0. Performed a backup and attempted to restore it onto a fresh install of SQL Server 2005. I get the following error: Restore failed for server ‘xyz’,(Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: File ‘e:db.mdf’ is claimed by ‘db_1_data'(3) and
‘db_ddate'(1). The WITH MOVE clause can be used to relocate one or more files.
(Microsoft.SqlServer.Smo) The restore was done via Microsoft SQL Server Management Studio. New file locations were stated but I still got the error above. I am now going to try to restore the db (with compatibility now to 7.0) onto a SQL Server 2000 instance and see if I get the same error. If anyone has any idea why this is happening, I would appreciated any comments/assistances. Thanks,
Jethrojaw

It is regular restore error, it is nothing to with upgrade…
Use WITH MOVE option as suggested by the MS in the error… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

hey,
last week i had faced exactly the same issue while restoring a SQL 2000 DB which has a compatability of 65,so i changed it to 80 and took a fresh backup and restored that in destination….u can restore it as mohammed said jus use with move option Regards
Deepak
SQL DBA

In any case I would suggest to refer tohttp://www.microsoft.com/sql/solutions/upgrade/default.mspx upgrade advisor too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

quote:Originally posted by satya In any case I would suggest to refer tohttp://www.microsoft.com/sql/solutions/upgrade/default.mspx upgrade advisor too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi, 1.) change the db’s compatibility level to 70 on the source server.
2.) Backup the db’s and copy those backup files to the destination server.
3.) Drop the db’s that already restored with compatibility level 65 or 70 in destination server
4.) restore the db’s
5.) use sp_dbcmptlevet ‘dbname’, 90 to the cmptlevel for all the dbs restored. Then try accessing the dbs. I think this will work. Sagar

Welcome to SSP Sagar [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Don’t forget to update statistics/reindexing too… MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>