Upgrade from 2000 via restore

Question

Upgrade from 2000 via restore

JacekO

SSCertifiable

Points: 6610
More actions
February 11, 2008 at 9:04 am

#203465

I know I can get a 2000 backup restored on 2005. Any potential problems using this approach as an upgrade path?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

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

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Some third-party products (i.e. Idera's SQLSafe, Quest's Litespeed) will allow you to restore the backup of a 2000 database to 2005 however this isn't an option using SQL Server's native backup/restore method. You will need to dettach and reattach the database in order to perform the upgrade.

Tommy

Follow @sqlscribe

JacekO SSCertifiable Points: 6610 More actions · Answer 2

Sorry, I think I did not properly ask the question. I am looking to find out if using a 2000 database (restored or reattached) on a 2005 server creates some kind of issues that are not apparent at the first glance. Did anyone tried this approach for upgrade and what kind of issues (if any) this creates?

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

george sibbald SSC Guru Points: 104210 More actions · Answer 3

I'd have to disagree with Tommy and say you can upgrade by restoring a backup from 2000 to 2005, for user databases only, not the system databases. Its a perfectly viable method to upgrade a database.

following the restore should also:

exec sp_dbcmptlevel 'dbname','90'

change dbowner to same as it was under SQL 2000, it is usually 'sa', but check

update all statistics, needed for performance, SQL 2005 interprets these differently

update usage stats (correct sp_spaceused values) can be incorrect after SQL 2005 upgrade

ensure torn page detection set. Use page_verify rather than checksum on busy servers due to less overhead, use checksum on quieter servers

and check out whether you want all the schemas created.....

-- upgrade creates a schema for all users and sets this as default, use following to create SQL to

-- modify users default schema to dbo and then run in a new window

-- if SQL 2000 objects were not owned by dbo modify script accordingly

set nocount on

select 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers

where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0

order by name

-- now drop all the user schemas created

select 'drop schema '+ name+ ' ' from sys.sysusers

where uid > 4 and issqlrole = 0 and isapprole = 0

order by name

-- if not done before backup used in migration, check database integrity

if database reasonable size, take this opportunity to reindex it (run dbcc dbreindex maint plan job)

-- if a large no of ad-hoc queries run on this database, consider forced parameterization option

-- could reduce cpu usage significantly

-- now back the database up (optional at this point - but will need including in backup strategy)

...and of course you will need to copy those objects outside user database such as logins, agent jobs, DTS, in which case you need the compatibility tool.

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

Stand corrected - there were so many bugs pre SP2 that I never bothered checking again 🙂

Tommy

Follow @sqlscribe

JacekO SSCertifiable Points: 6610 More actions · Answer 5

Thanks for the input.

george, thanks for the extensive 'script' to go by.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Vivien Xing SSChampion Points: 12297 More actions · Answer 6

Need to clarify here. Should use third-party products (i.e. Idera's SQLSafe, Quest's Litespeed) to back up SQL2000 user database and restore to SQL2005 as part of the upgrade, or must use SQL server 2000 native backup then restore to SQL2005 for upgrade?

Very helpful list for post-upgrade. Thank you George.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 7

As George pointed out earlier - either or.

Tommy

Follow @sqlscribe

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

If you still have access to the SQL Server 2000 database(s). You can use the SQL Server 2005 Upgrade Advisor. It will run checks against your existing SQL Server 2000 SP3a (or SQL Server 7.0 SP4 even) databases and generate a report. Any existing SQL Server 6.5 databases will unfortunately need to be upgraded to at least SQL Server 7.0 SP4.

SQL Server 2005 Books Online (September 2007)

Version and Edition Upgrades

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

Upgrade Advisor will need to be installed onto your SQL Server 2000 machine(s). The Advisor installer is located on the SQL Server 2005 installation CD's/DVD or can be downloaded at

Microsoft SQL Server 2005 Upgrade Advisor

http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

... once the tool is installed you can have it check for updates as well.

For reference ...

SQL Server 2005 Books Online (September 2007)

Using Upgrade Advisor to Prepare for Upgrades

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