SQL 2000 database to SQL 2005

Question

SQL 2000 database to SQL 2005

Wilko-290364

SSC Journeyman

Points: 93
More actions
June 11, 2008 at 3:37 am

#124152

I'm in the process of moving a SQL 2000 database to SQL 2005, once I've attached the database, either from a restore bak file or attach the mdf & ldf. Do I need to run sp_updatestats and DBCC UPDATEUSAGE ?
Thanks

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

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

carlos.campos-hidalgo SSC Enthusiast Points: 175 More actions · Answer 1

You only need to backup the database in the SQL2000 and a restore in the SQL2005..

Be carefull with the logins, and in the Options, you can change the way of work of the DB with the parameter Compatibility Level (In the Options "Tab") you can select SQL Server 2005, SQL Server 2000 or SQL Server 7.0 (My Recomendation is SQL Server 2005 .. but the application that use this database would required change this issue)

Regards

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 2

Yes you need to run DBCC commands try to delete all orphened user which is no longer with your organization.

Microsoft provided script for transfering logins from 2000 to 2000 and from 2000 to 2005.

With the help of 2 sp you should be able to do that,

1) sp_help_revlogin and

2) sp_hexadecimal

You can find those sp's from MS site as well.

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

For jobs and DTS package you may need to script it out.

Manoj

MCP, MCTS (GDBA/EDA)

Wilko-290364 SSC Journeyman Points: 93 More actions · Answer 3

Hi there,

Thanks for your comments, much appericated although when I'm resotoring the DB back file from SQL 2000 I'm getting the Microsoft SQL Server, Error: 3154 message.

I have a DB on a sql 2000 srv and want to restore the DB from backup file to a SQL 2005 SRV, although I getting the above error message. I've created a blank database on SQL 2005 with the same name, although even though I select 'overwrite the existing database' I am then presented with this message.

I've made sure that the users of the SQL 2000 db are currently in the SQL 2005 security logins too.

Regards

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 4

Try to do this exercise...

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command.

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than other DBA but I use it to fix my database issue successfully.

3) Sample Example :

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:\BackupAdventureworks.bak’

WITH REPLACE

Example:-

While moving some of the script from SQL SERVER 2000 to SQL SERVER 2005 our migration team faced following error.

Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database "AdventureWorks" has not been backed up.

Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.

Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.Following is the similar script using AdventureWorks samples database as example. This scripts works perfectly fine with SQL SERVER 2000. It gives the error in SQL SERVER 2005.

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:BackupAdventureworks.bak’

WITH MOVE ‘AdventureWorks_Data’ TO ‘C:Data’,

MOVE ‘AdventureWorks_Log’ TO ‘C:Data’The reason of error is already explained in the error detail. Requirement of backing up tail of the log for the database can be overridden by using RESTORE command. Most of the WITH clause statements can be used in combination with the others. Change the syntax of above script with addition of REPLACE in WITH clause.

ALTER DATABASE AdventureWorks

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:BackupAdventureworks.bak’

WITH MOVE ‘AdventureWorks_Data’ TO ‘C:\Data\datafile.mdf’,

MOVE ‘AdventureWorks_Log’ TO ‘C:\Data\logfile.ldf’,

REPLACE

Reference : Pinal Dave (http://www.SQLAuthority.com),BOL

Posted in Author Pinal, DBA, SQL, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQLAuthority Book Review, T SQL, Technology | 7 Comments

Manoj

MCP, MCTS (GDBA/EDA)