x

migrating from sql server 2005 to sql server 2008

hi all, can you help me some steps or optimal setting to be implemented while migrating from2005 to 2008.. now we are using sql server 2005 but database compatability mode is in 2000. this time i want to change compatability from 80 to 100
more ▼

asked Jul 14 '10 at 04:18 AM in Default

ramesh 1 gravatar image

ramesh 1
2.1k 59 67 69

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

2 answers: sort voted first

Moving a database from SQL Server 2005 to 2008 is pretty painless. There were very few, if any, things deprecated. The one issue you will need to worry about is the use of the ANSI 89 style outer joins in TSQL, "*=" or "=*". Only ANSI 92 outer join syntax is supported in 2008, "LEFT JOIN" or "RIGHT JOIN".

Microsoft has a [SQL Server 2008 Upgrade Advisor][1] available. Run that on the 2005 system just like you would on a 2000 system. It'll inform you if there are any problems.

Other than that, the upgrade should go smoothly. I recommend doing what is called a side-by-side, as opposed to in-place, upgrade. This means you set up a new 2008 server and then move your databases to it using backup & restore. That way, if there is a problem during the upgrade process, the old server is waiting for immediate recovery. In place upgrades, if they go wrong, are much more difficult to recover from.

[1]: http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en
more ▼

answered Jul 14 '10 at 04:24 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.9k 19 21 74

+1 for side-by-side. We had an application owner who insisted it be done in-place... some corrupted install files and 16 hours later we were up and running :(
Jul 14 '10 at 05:39 AM Blackhawk-17
Oh, ouch! That would not have been fun. Especially sitting there with "Told you so, told you so, told you so..." running on a loop through your head while you try to recover.
Jul 14 '10 at 06:06 AM Grant Fritchey ♦♦

@Grant I am happy to report that it was my vote just now which flipped you into 12k range, so congratulations!

On the unrelated note, I finally was able to figure out the solution to that problem I am sure you noticed too when typing "=*" or "*=" but getting "=*" or "*=" as a result appearing in your answer. The solution is to use the backslash character to escape whatever the script honors as the special characters (such as ** to denote a beginning of the bold text and * to denote the beginning of italics.
Jul 14 '10 at 09:14 AM Oleg
Thanks, edited the post so it doesn't look quite so crazy any more.
Jul 14 '10 at 10:15 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

As Grant Fritchey said side-by-side is the best choice, you can move the database by simple attach and detach method, or by Restoring the latest backup, or by Copy database method

follow this link for copy database method http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard

I suggest you after performing upgrade copy the necessary objects like the maintenance task, jobs & alerts to the new SQL Server, and also don't forget to run the DBCC commands DBCC UPDATEUSAGE , DBCC CHECKDB(DBNAME) WITH DATA_PURITY, DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS SP_UPDATESTATS for all database

more ▼

answered Jul 14 '10 at 04:44 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

(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:

x1933
x1816
x58

asked: Jul 14 '10 at 04:18 AM

Seen: 2482 times

Last Updated: Jul 14 '10 at 04:20 AM