Re: SQL Server 6.5 Data Conversion

Gudni G. Sigurdsson (GudniGSigurdsson@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I need to convert SQL Server 6.5 data to SQL Server 2005 (Express). How
do I do that? Where do I get the necessary tools?

If you want to do it with wizards etc, you need to first get hold of SQL
2000. There is no direct upgrade path.

However, I would suggest that you are best of rolling your own. If you
have the scripts for the database definition under version control, use
these. Else script the database definition from the 6.5 Enterprise Manager.
Then run the script on SQL 2005.

To move the data, run this on SQL 6.5:

SELECT 'bcp db..' + name + ' format dummy -f ' +
name + '.fmt -n -U sa -P xxx -S SQL65'
FROM sysobjects
WHERE type = 'U'

Copy the output to a .CMD file and run in an empty directory. This create
format files for the export.

Then run

SELECT 'bcp db..' + name + ' out ' + name + '.bcp -f ' +
name + '.fmt -U sa -P xxx -S SQL65'
FROM sysobjects
WHERE type = 'U'

Again, copy the output and run. You have now exported the data. Next:

SELECT 'bcp db..' + name + ' in ' + name + '.bcp -f ' +
name + '.fmt -T -S SERVER\SQLEXPRESS -E'
FROM sysobjects
WHERE type = 'U'

Copy and run again. You have now loaded the data.

At this point run in SQL 2005:

DBCC CHECKCONSTRAINTS
EXEC sp_updatestats

If this reports any errors, analyse and repair.

Run in SQL 2005:

SELECT 'ALTER TABLE ' + name + ' NOCHECK CONSTRAINT ALL; ' +
'ALTER TABLE ' + name + ' WITH CHECK CONSTRAINT ALL'
FROM sys.objects
WHERE type = 'U'

Copy and run output in a query window.

This ensures that all constraints are trusted. If constraints are not
trusted, the optimizer will ignore them, and this can lead to performance
degradation.

Note: all of this was typed on free hand, so there may be some syntax
errors here and there. I encourage to you look closer in Books Online
what these commands do.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... Rebuild the node in the failover cluster. ... Scenario 1" in SQL Server 2000 Books Online. ... This setup process updates to SP4 only the binaries on the new ...
    (microsoft.public.sqlserver.clustering)
  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)
  • Re: WSUS
    ... I'm not seeing performance issues with the full enchilada installed, and 25 users busy hitting SQL. ... WSUS isn't difficult to uninstall - if you have WSUS v2 (installed with SBS R2) uninstall R2 from add/remove programs. ... How can anyone work with 4 instances of SQL Server on the same box? ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL Server 2005 Cluster Setup Quiz
    ... I did test and it did not install the client tools. ... http://www.clusterhelp.com - Cluster Training ... Microsoft SQL Server MVP ... Provide a template on how to read SQL Server 2005 setup log files. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Cannot setup SQL Mail on SBS 2003
    ... Microsoft CSS Online Newsgroup Support ... Cannot setup SQL Mail on SBS 2003 ... The account you use to start the SQL Server service must be a domain ...
    (microsoft.public.windows.server.sbs)