asked
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
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
+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 :(
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.
@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.
Thanks, edited the post so it doesn't look quite so crazy any more.
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
