Re: SQL Server 6.5 Data Conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • It can be Done
    ... I just installed a 3 SQL Server 2005 instances on a 2 node Active/Passive cluster. ... IWiz will then offer you a choice of Group on where you can install teh Fail Over Clustered Instance of SQL. ...
    (microsoft.public.sqlserver.clustering)
  • 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)