Move Database from SQL2000 to SQL 2008R2

Question

Post reply

Move Database from SQL2000 to SQL 2008R2

Pulivarthi Sasidhar

SSCertifiable

Points: 6706
More actions
December 21, 2010 at 5:27 am

#232180

Hi All,
I have to upgrade only the Database from SQL 2000 to SQL 2008R2.
Please give suggestions or Guidelines.
I will Deattach from SQL 2000 And Attch same in SQL 2008R2
or
Take backup from SQL 2000 and Restore in SQL 2008R2
Here i need to provide the Logins/Users/ Access permissions of DB in SQL 2000 as same in SQL 2008 R2.
SQL 2000 is 32-Bit
SQL 2008R2 is 64-Bit
Any Info to proceed...
Thanks,
Sasidhar Pulivarthi

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 1

hi

Here are some of the ways of move the database

In-place Upgrade

Side by Side Upgrade

Detach/Attach

Copy Database Wizard

Manual Schema Rebuild and Data Export/Import

Choosing upgrade method

The two upgrade methods discussed above have several different advantages and disadvantages with it as given below, we have to evaluate it in terms of our requirements and current circumstances before moving for upgrade.

In-place Upgrade Advantages:

It's easier and faster, especially in small systems.

It's mostly an automated process.

The instance will be offline for a minimum amount of time.

The resulting instance after upgrade will have the same name as the original, as the new setup will replace the older version.

No additional hardware is required in many cases.

o Disadvantages:

It's very complex to rollback.

Not applicable in scenarios where we want to upgrade a part of system like upgrading just one single databases.

We cannot run an upgrade comparison after doing the upgrade.

Side by Side Upgrade

o Advantages:

More control over the upgrade, as we can upgrade the components, which we want to.

We can keep our application running even when we are installing SQL 2008 as the old instance or server will be available.

Easy to do a rollback as the original database server is untouched.

In-place Upgrade Disadvantages:

We might need additional hardware resources in terms of disk space, CPU and RAM.

Manual intervention is required to migrate databases, Jobs, logins etc.

There will be change in configuration settings, which are used by the application to connect to the database.

More time is required while moving VLDB to the new version of database.

also check

http://www.sqlservercentral.com/articles/Upgrade/65872/

Geoff A SSChampion Points: 11511 More actions · Answer 2

backup / restore is nice and easy.

syncing logins will be an issue. you will not be able to script out logins from 2000 and script them in to a SQL 2008 R2 instance. something about hash changes between versions.

it is possible to script out logins from 2000 to 2005. then script them out again from the 2005 to 2008 R2.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

This is one of those places where the Microsoft documentation, best practices and advice is the best place to go for information. They really do lay out the choices correctly. Make sure you're running the Upgrade Advisor on any 2000 databases and that you deal with the results prior to migrating your database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Pulivarthi Sasidhar (12/21/2010)
SQL 2000 is 32-Bit
SQL 2008R2 is 64-Bit
Any Info to proceed...
Thanks,
Sasidhar Pulivarthi

Sounds like you have 2 different machines ?? You're not upgrading an existing machine from 2000 to 2008 ?

Then you can detach - attach

You also need to import the logins as mentioned.

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 5

Hi All,

Thanks for Info...

Yes, two different Machines..

For logins i have below link

http://support.microsoft.com/kb/246133

I am almost done with Logins & Restore of DB's.

need to Generate script for all Jobs and Replication existed in SQL 2000 by SQL / T-SQL stmts only.

The same generated scripts are going to Run on SQL 2K8 R2.

Any further Info plz...

crazy4sql SSCoach Points: 19590 More actions · Answer 6

i dont think there would be any major difference in moving the database to 2008 or 2008R2, so same strategy is appicable.

Post upgrade, need to make sure that stats are updated and compatibility level changes to reflect the correct product version. Also revisit your scheduled jobs.

----------
Ashish

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 7

Sorry to hijack, but I also am now in an environment going 2000 > 2008R2 and wonder if there is a way to script out DTS packages?

Not sure migration will be worth it as I need to improve the processes and just need the workflow documented so I can build new in SSIS.

I am in second week on the job - EVERYTHING is someone elses work and I don't understand the business at all. Would rather the uphill go ONE WAY!

crazy4sql SSCoach Points: 19590 More actions · Answer 8

for DTS, i will suggest to save them as file and then import them as file in ssms.

----------
Ashish

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 9

For DTS, save as structured storage files. Use the runtime to execute them: http://msdn.microsoft.com/en-us/library/bb500440.aspx

Note that SQL 11 will not have support for DTS

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 10

Thanks, folks!

All will be SSIS within however many months it takes me to re-write...