Migration from SQL Server 7 to 2005

Question

Migration from SQL Server 7 to 2005

Saleha Rizvi

SSC Rookie

Points: 39
More actions
April 7, 2009 at 9:13 am

#132907

Hi
I am trying to move a database from SQL Server 7 to 2005 which has
1)40 tables
2) 70 stored procedures
3)11 tables with identity fields
Has anybody accomplished migration of a similar database type.
I did run the upgrade advisor. It does not show any major issues. However I am not sure what will be the best way to deal with tables which have identity fields.
Any help will be greatly appreciated.
Thanks!
Saleha

Viewing 8 posts - 1 through 7 (of 7 total)

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

I did the similar thing. Just copied the table structures and T-SQL objects and there were no issues. But instead of copying it would be better to re-design the sps. SQL Server 7 is very old and 2005 is very new and it has somany features.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Saleha Rizvi SSC Rookie Points: 39 More actions · Answer 2

Did the identity fields work ok when you moved to the new server? Did the seed started from where it was left at the old server.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

You can use IDENTITY_INSERT to ON for the data migration. After this use DBCC CHECKIDENT to set the new seed value:

CREATE TABLE #T (Id INT IDENTITY(1, 1), Txt VARCHAR(50))

-- Allow identity insert

SET IDENTITY_INSERT #T ON

-- Insert data into identity column

INSERT INTO #T (Id, Txt)

SELECT 1, 'Hello'

UNION ALL SELECT 2, 'World'

-- Set new seeding to 300

DBCC CHECKIDENT (#T, RESEED, 100)

-- Disallow identity insert off

SET IDENTITY_INSERT #T OFF

-- Some other test data

INSERT INTO #T

SELECT 'New World'

SELECT * FROM #T

DROP TABLE #T

Greets

Flo

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Saleha Rizvi (4/7/2009)
Did the identity fields work ok when you moved to the new server? Did the seed started from where it was left at the old server.

The way i did is created the tablestructures with Identity columns in 2005 and set the IDENTITY_INSERT ON. Moved the data and re-seeded the Identity column.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

In Other words i did exactly same as Flo mentioned in this post.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Vijaya Kadiyala (4/8/2009)
In Other words i did exactly same as Flo mentioned in this post.

Hi Vijaya Kadiyala

Thanks for confirmation! 😎

Greets

Flo

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

simply

backup and restore

or

detach and attach

the SQL 7 database to the SQL 2005 server and all the identities should be fine

dont forget to update usages and rebuild indexes afterwards

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉