February 11, 2008 at 9:04 am
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]
February 11, 2008 at 9:34 am
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 @sqlscribeFebruary 11, 2008 at 9:43 am
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]
February 11, 2008 at 9:57 am
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.
---------------------------------------------------------------------
February 11, 2008 at 10:33 am
Stand corrected - there were so many bugs pre SP2 that I never bothered checking again 🙂
Tommy
Follow @sqlscribeFebruary 12, 2008 at 8:27 am
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]
February 13, 2008 at 1:03 pm
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.
February 13, 2008 at 1:19 pm
As George pointed out earlier - either or.
Tommy
Follow @sqlscribeFebruary 13, 2008 at 3:48 pm
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
... 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
See also ...
SQL Server 2005 Books Online (September 2007)
Upgrading to SQL Server 2005
http://technet.microsoft.com/en-us/library/ms144245.aspx
Other Resource Links ...
Upgrading to SQL Server 2005
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
This is a portal page that has links to FAQ's for upgrading, whitepapers, a link to download the SQL Server 2005 Upgrade Advisor Tool and more.
Happy T-SQLing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply