Migrating SQL 2000 to SQL 2008 database

Question

Migrating SQL 2000 to SQL 2008 database

bhaskar varada

Ten Centuries

Points: 1337
More actions
May 3, 2010 at 8:40 am

#224093

Hi All,
we are planning to migrate the SQL 2000 database to SQL 2008 database,
what are the steps needs to be followed for migrating using side by side method

Viewing 15 posts - 1 through 15 (of 20 total)

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

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

There are different ways of migrating database to new enviornment. MSDN has nice article which will help you to plan your upgradation.

here is the link...just go-through it. If not understood SQL community is always here to guide you. 🙂

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

Abhijit - http://abhijitmore.wordpress.com

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

The basics are easy:

Run the Upgrade Advisor

Fix any issues

Migrate the logins

Backup the databases on the old server

Restore the databases on the new server

Run DBCC checks (a little paranoia goes a long ways)

Update Statistics

Fix login/user issues

Have a nice day. Test it a couple of times to be sure you're doing it right before you do it with a production system.

"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

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 3

I ran the SQL Upgrade Advisor and got the following issue that needs to be fixed before I upgrade. Have you heard about this:

--Object reference not set to an instance of an object

and the object that was listed

FTUNSIGNEDCOMPONENTS

FTCOMPONENTREG

SVCACCTREQ

I tried looking for answers and not able to figure out what needs to be done. Have you come across anything like this.

Thank you,

Asha

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

avanch-990710 (5/7/2010)
I ran the SQL Upgrade Advisor and got the following issue that needs to be fixed before I upgrade. Have you heard about this:
--Object reference not set to an instance of an object
and the object that was listed
FTUNSIGNEDCOMPONENTS
FTCOMPONENTREG
SVCACCTREQ
I tried looking for answers and not able to figure out what needs to be done. Have you come across anything like this.
Thank you,
Asha

I have seen that, but I can't recall what the issue was.

"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

khursheed.siddiqui Old Hand Points: 312 More actions · Answer 5

Are you running the upgrade Advisor with local Administrator accont?

khursheed.siddiqui Old Hand Points: 312 More actions · Answer 6

avanch-990710 (5/7/2010)
I ran the SQL Upgrade Advisor and got the following issue that needs to be fixed before I upgrade. Have you heard about this:
--Object reference not set to an instance of an object
and the object that was listed
FTUNSIGNEDCOMPONENTS
FTCOMPONENTREG
SVCACCTREQ
I tried looking for answers and not able to figure out what needs to be done. Have you come across anything like this.
Thank you,
Asha

See my response above 🙂

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

specifically, ensure you run DBCC UPDATEUSAGE against the database.

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Perry Whittle (5/8/2010)
specifically, ensure you run DBCC UPDATEUSAGE against the database.

And, you also want to make sure you run DBCC CHECKDB {database} WITH DATA_PURITY and get a clean result. If you don't get a clean run, then you have to fix the integrity errors before moving forward.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 9

I tried running the DBCC dbs upgradeusage; and now there is one error that reads

Access is denied: (Exception from HResult: 0X8007005 (E-Access Denied)

I am not sure what permissions need to be set.

I need help..

Thank you,

Asha

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 10

Yes, I am running the SQL Upgrade Advisor as an administrator

Akkare SSCrazy Points: 2848 More actions · Answer 11

Akkare

SSCrazy

Points: 2848

May 13, 2010 at 1:07 am

#1166666

Pls fix the logins and try

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 12

Now, does the sa account for SQL server 2000 and SQL server 2008 have the same password--I have them set up with different passwords?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

avanch-990710 (5/14/2010)
Now, does the sa account for SQL server 2000 and SQL server 2008 have the same password--I have them set up with different passwords?

That depends on what was set when the server was installed. They can have the same or different passwords.

"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

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 14

I tried to use the Copy Database Wizard to try and copy one database from SQL 2000 to SQL 2008 as part of migrating the database. I had some errors when I ran the SQL Upgrade wizard which needed to be fixed before the migration and fixed all of them.

Now I get an error when I run the Copy Database Wizard-

"Property Sid is not available for user '[Administrator]', this property may not exist for this object, or may not be retrievable dur to insufficient access rights."

I am not sure hwo to fix this one. Does anybody have an idea?

Thank you for all your help.

Asha