How to migrate SQL 2000 Stored Procedures and Views to SQL 2008

Question

Post reply

How to migrate SQL 2000 Stored Procedures and Views to SQL 2008

Scarabee

SSChasing Mays

Points: 622
More actions
June 28, 2012 at 12:23 pm

#276985

Hello,
We're running several MSSQL 2000 databases having to be migrated into 2008 R2.
I made following approaches:
-Simply (de-) attaching the SQL2000 databases to 2008R2 did not work 'cause another collation is in use on MSSQL 2000 dbs.
-The DTS-Data Import-Export assistant (2008) is copying the db but, unfortunately, it does ignore the stored procedures. Also it treats views as tables which comes along with the result views are inserted the same way as a table.
-The SSIS task 'SQL Server-Objects transfer' does not work, either, because it is designed for 2005ff.
-Same way, the SSIS task 'Transfering SQL Database' between two instances (copy or move) is not usable
So, I guess, I have to transfer the stored procedures and views by hand.
How can I design a Task to achieve this in SSIS ?
Maybe, anyone has another idea ?
Thank you, in advance
Stephan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

First step I would take, and have taken is to run the upgrade advisor available at:

http://www.microsoft.com/en-us/download/details.aspx?id=11455

then do what has to be done.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

CapnHector SSCoach Points: 16143 More actions · Answer 2

have you tried restoring a backup taken from the 2k server on the 2008 server?? i believe that should work provided you set the compatibility level correctly on the new 2008 instance. you will need to update any *= join syntax to the correct INNER or OUTER join syntax since 2008 will error out on it.

i believe that should work but i would try on a non production box just to make sure.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Scarabee SSChasing Mays Points: 622 More actions · Answer 3

@capn.hector

Yes, I have attached the db on a non productive server in Hyper V (of course).

The result was a language-collation error when attempting to add users (schemes).

So, instead, I decided to copy each table and rebuild it on sql 2008 r2 by converting sql 2000 db to the language of the 2008 r2 system dbs.

The reason for my approach lies in certain stored procedures which create temporary tables

and eventually collide with the other sort language in temp db.

😉 At least, so far is my theory and assumption to bridge the error the sql advisor reported.

The used sql syntax does not consist of any of your mentioned join syntax. I have run the sql advisor several times and this is not my problem.

thanks,

Stephan

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

I'd script them all out from SSMS (in object explorer details mode) and move them that way. Not pretty, but fairly straightforward since you can get them all at once.

And then again, I might be wrong ...
David Webb

CapnHector SSCoach Points: 16143 More actions · Answer 5

StephenNL (6/28/2012)
@capn.hector
Yes, I have attached the db on a non productive server in Hyper V (of course).
The result was a language-collation error when attempting to add users (schemes).
So, instead, I decided to copy each table and rebuild it on sql 2008 r2 by converting sql 2000 db to the language of the 2008 r2 system dbs.
The reason for my approach lies in certain stored procedures which create temporary tables
and eventually collide with the other sort language in temp db.
😉 At least, so far is my theory and assumption to bridge the error the sql advisor reported.
The used sql syntax does not consist of any of your mentioned join syntax. I have run the sql advisor several times and this is not my problem.
thanks,
Stephan

i did not mean attach i meant run the following on your 2000 box

BACKUP DATABASE blah TO DISK='SomeFile.bak' [and any other options you want

http://msdn.microsoft.com/en-us/library/ms186865.aspx

then run the following on your 2008 box:

RESTORE DATABASE blah FROM DISK='SomeFile.bak' [and any other options you want

http://msdn.microsoft.com/en-us/library/ms186858.aspx

Simply attaching the raw mdf and log files is something i have never tried. its always been a backup and restore

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]