x

Upgrade 2005 to 2008

Can anyone tell me the potential/frequent issues noticed during upgrade from sql 2005 to 2008 version.

What are the solutions and preventive measures?

Rgds

Aj

Matt,

Can you please advise,what do you mean by "Make sure your backups are usable first",unabl to add comments via "Add new comment" button,hence posted my comments here.

Matt,thank you for asking, When I click on "Add new comment" button there is no change in the screen,nither I see the comment box where I can input my comments.I am not sure how can I update my comments?

Rgds, Ajit
more ▼

asked Jul 27, 2010 at 12:13 AM in Default

pits gravatar image

pits
830 79 91 92

Ajit - what do you mean about add new comment? Can you describe a bit more about what it says? Because I tested this last week and it was OK - so maybe I'm doing something different?
Jul 27, 2010 at 04:39 AM Matt Whitfield ♦♦

Ajit - Ok thanks - one final question - can you let me know what O/S and Browser you are using?

Thanks again
Jul 28, 2010 at 05:03 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

2005 to 2008 is generally a pretty smooth upgrade, but it's worth taking all usual precautions:

  • Make sure your backups are usable first
  • Upgrade side-by-side if possible, rather than in-place
  • Make sure all statistics are updated, ideally take the opportunity to reindex all indices beforehand
  • Make sure you have a backout plan if it all goes pear shaped

[This question][1] also has a couple of useful links.

[1]: http://ask.sqlservercentral.com/questions/86/upgrading-to-sql-server-2008-from-sql-server-2005
more ▼

answered Jul 27, 2010 at 12:28 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

By "Make sure your backups are usable first", I assume you mean "make sure you can restore from the backup to a new similar server"...

(Clarifying for Ajit...)
Jul 27, 2010 at 02:07 AM ThomasRushton ♦
@ThomasRushton - absolutely, yes.
Jul 27, 2010 at 04:37 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

There are a few T-SQL commands that have become deprecated in 2008. One of the big ones is BACKUP LOG WITH NOLOG and BACKUP LOG WITH TRUNCATEONLY no longer works. You can trace for Deprecation Final Support Event in Profiler which will let you know if your using any commands that will not work in 2008.

It's also a very good idea to run the [Upgrade Advisor][2] and review the output.

[2]: http://www.microsoft.com/downloads/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en
more ▼

answered Jul 27, 2010 at 12:34 AM

Nick Kavadias gravatar image

Nick Kavadias
476 3 3 7

(comments are locked)
10|1200 characters needed characters left

Personally, I would always go for upgrading to new hardware / vm. That way, you've got no problems with going back to the previous version.

  • Check your backups anyway
  • Check the database structures (DBCC CheckDB)
  • Matt suggests rebuilding index stats - I would leave that until it's on the new box, assuming that the new box is a more powerful server...
  • Detach the database
  • Copy the database & log files to the new server
  • Reattach on the old server
  • Attach on the new server
  • Side-by-side testing

Backout plan? Well, you've not destroyed anything...

I've prepared a [little list of other things to look at when migrating / upgrading][1] on my blog. If you can spot anything missing, let me know!

[1]: http://thelonedba.wordpress.com/2010/06/08/sql-server-upgrades-ahoy/
more ▼

answered Jul 27, 2010 at 02:13 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1944
x1834
x68

asked: Jul 27, 2010 at 12:13 AM

Seen: 1387 times

Last Updated: Jul 28, 2010 at 04:48 AM