Friday, March 9, 2012

MSSQL7 -> MSSQL2000 schema and data

Hi group,
[First time in, so feel free to shoot me if needed]
I am experienced with Postgresql, but relatively new to MSSQLServer.
I am building an application that uses MSSQL7.
Only basic stuff: 20 tables, foreign keys, a nice diagram, a few indexes.
No DTS, complex userstuff and that.
The target MSSQLServer will be 2000.
So now I need to know how to get my databaseschema and content from 7 to
2000.
As far as I can tell the option: GENERATE SQL only offers me an text-based
instruction for SCHEMA, not the content of table.
Right?
Question: Will MSSQL2000 understand the instructions created by MSSQL7?
As for the data itself:
The only way to export the data in the tables from MSSQL7 to MSSQL2000 I
found is by backing the whole thing up, as a binary.
(Using 'all tasks' --> 'Backup database')
Question: Will MSSQL2000 read and understand this file?
And will I need the databaseschema as generated by the GENERATE SQL option?
Or will MSSQL2000 be able to get that from the binary export?
Is this the way to do it, or am I missing lots of stuff?
Thanks for your time
Regards,
Erwin Moller
PS: I am sorry, but expect this is a very basic question. I just cannot find
any clear material on the subject online.> I am building an application that uses MSSQL7.
> Only basic stuff: 20 tables, foreign keys, a nice diagram, a few indexes.
> No DTS, complex userstuff and that.
> The target MSSQLServer will be 2000.
> So now I need to know how to get my databaseschema and content from 7 to
> 2000.
> As far as I can tell the option: GENERATE SQL only offers me an text-based
> instruction for SCHEMA, not the content of table.
> Right?
Right.
> Question: Will MSSQL2000 understand the instructions created by MSSQL7?
Yes.
> As for the data itself:
> The only way to export the data in the tables from MSSQL7 to MSSQL2000 I
> found is by backing the whole thing up, as a binary.
> (Using 'all tasks' --> 'Backup database')
You found the easiest way. You could use Data transformation Services (DTS),
for example, as well.
> Question: Will MSSQL2000 read and understand this file?
Yes, simply restore the backup on SQL 2000 box. But I guess you will have to
change the physical path to data and log files, unless you have created the
database in 7.0 in your custom (not SQL default) folder and you have the
same directory structure in 2000.
> And will I need the databaseschema as generated by the GENERATE SQL
> option?
No.
> Or will MSSQL2000 be able to get that from the binary export?
Yes - backup has everything, schema and data.
> Is this the way to do it, or am I missing lots of stuff?
Yes, this is a good way. You have original 7.0 db, backup and new 2000 db
after the action. So. if anything goes wrong, you have 3 copies. And the
process is quite simple.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||Thanks Dejan Sarka!
I seldom receive such to-the-point answers. :-)
Regards,
Erwin Moller

No comments:

Post a Comment