Migrating data from SQL server 2000 to SQL server 2008

Question

Migrating data from SQL server 2000 to SQL server 2008

punit.ok

SSC Enthusiast

Points: 136
More actions
February 12, 2010 at 9:02 am

#229943

I have my database set up in SQL server 2000. Our company has currently decided to upgrade it to SQL server 2008. I wanted to know the steps to be followed to migrate the data from SQL 2000 to SQL 2008. I am a complete fresher at this. I have no knowledge at all. Detailed explanation will be highly appreciated.
Regards,
Punit

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

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

Craig@Work SSCrazy Points: 2346 More actions · Answer 1

Well there a various approaches ...

Perhaps the simplest is to perform a full backup of your existing SQL 2000 database, restore this onto your SQL 2008 instance.

Ensure that the database still runs in SQL 2000 compatability level, and only switch to full SQL 2008 compatability if you are certain nothing breaks.

I know you asked for a detailed explanation, but I hope the above prompts you to make a start and if necessary come back with further questions.

I appreciate it is not fun when your organisation dumps a problem on you that you do not know at the start how to solve.

Tharg SSC Eights! Points: 980 More actions · Answer 2

Are you doing an in-place upgrade? I assume not. I haven't done SQL 2000 -> SQL 2008 but have done SQL 2000 -> SQL 2005.

Back and restore (and check compatability as previous poster noted) and transfer your logins.

If you are using DELL...

http://www.dell.com/downloads/global/solutions/public/white_papers/SQL_2000_SQL_2008_Migration_Guide.pdf

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

You might want to download and use:

Microsoft SQL Server 2008 Upgrade Advisor

Brief Description

Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.

At:

http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en

Also refer to

SQL Server Database Engine Backward Compatibility

http://technet.microsoft.com/en-us/library/ms143532.aspx

Another item

ALTER DATABASE Compatibility Level (Transact-SQL)

http://technet.microsoft.com/en-us/library/bb510680.aspx

And then to show your company that you are budget conscious purchase SQL Server Developer Edition, although it can NOT be used for production, it would make a good sandbox for you to test your databases. It is available from Amazon.com for less that $50 (USD).

Hope this gets you started .

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]

punit.ok SSC Enthusiast Points: 136 More actions · Answer 4

Thanks a lot everyone for your suggestions...I migrated the data by backing it up in sql 2000 and restoring with sql 2008. It worked fine. The only problem that I found was that my ntext datatype in sql 2000 was automatically converted to varchar(2000). As a result my text got truncated to 2000 characters. Any suggestions with what I can do about that data type? It does not allow me to change it in sql 2008 either.

Regards,

Punit

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Pretty sure a backup/restore from SQL 2000 to SQL 2008 cannot convert an ntext datatype to varchar(2000).

text, ntext and image are all deprecated in SQL 2008, but they still exist. How did you do the backup/restore?

The proper non-deprecated replacement for ntext is nvarchar(max), so if you need unicode support and > 8000 chars then this is what you should use going forward, but you can still work with text/ntext in this version.