March 19, 2008 at 7:55 am
Hi folks,
I want to upgrade one database from sql server 2000 to sql server 2005. Can I get some idea step by step on this procedure.
Thanks in advance.
Thanks
March 19, 2008 at 10:42 am
Just do backup and restore to SQL 2005 then set mode of database 90 on SQL 2005.
March 19, 2008 at 12:47 pm
Follow http://msdn2.microsoft.com/en-us/library/ms190775.aspx
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 20, 2008 at 2:49 am
You might want to take a look at this otherwise your database might get broken by simply restoring and setting the compat level to 90:
http://www.innovartis.co.uk/topical/migratingto2005.aspx
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
March 20, 2008 at 4:42 am
Please refer these links and see if it helps,
http://www.sqlservercentral.com/columnists/ssamuel/2987.asp --- Inplace upgrade
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en --- Upgrade Document for SQL Server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2534409&SiteID=1 - Upgrade from Sql 2000 to Sql 2005
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2477578&SiteID=1 - Upgrade from Sql 2000 to Sql 2005
Refer this for migrating dts to ssis, http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration
[font="Verdana"]- Deepak[/font]
March 20, 2008 at 8:27 am
I've done the SQL 2000 to SQL 2005 quite a few times, with a few different methods.
Copy Database Wizard, http://msdn2.microsoft.com/en-us/library/ms190775.aspx,
Detach / Attach, http://msdn2.microsoft.com/en-us/library/ms189625.aspx, and
backup and restore, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2477578&SiteID=1
I prefer the backup / restore method, I believe it's quicker and more simple.
But of course, make sure you do this in a test environment and really test the front-ends to make sure everything is peachy before moving on to production.
You don't have to buy special tools for this!
Good Luck!
jim
March 20, 2008 at 11:34 am
Thanks folks..Will let you know the result...I Appreciate
March 20, 2008 at 1:15 pm
First, I would recommend you to run the database upgrade wizard to make sure you do not run into any compatibility issues-great wizard that will provide different levels of warnings. Then the next best method, as I see everyone is doing and my favorite way, is to backup and restore. This way if you run into any issues on the SQL 2005 converted DB, then you can always revert back to your SQL 2000 DB.
Just remember to do the following once you have restored the SQL 2000 DB over to SQL 2005: Run sp_updatestats.
This is imperative for SQL2k5 will update the schema and stats information.
March 20, 2008 at 7:57 pm
As a shop that has been using Microsoft products for close to 20 years, we are quite leery of in place upgrades so our version upgrades tend to include server migrations as well.
We installed SQL 2005 on the new boxes, created the database shells, created the logins, and ended up simply attaching the databases from the 2000 server after we shut it down. We had them on a SAN so we remapped the SAN on the new server.
Unfortunately we are still running in mode 80 as we are unable to get priority on changing code to be SQL2005 compliant.
The biggest issue we ran into was we ended up have to resynchronize a lot of replication (around 100+ targets) when we migrated to a 2005 distributor with it.
Make sure to check to associate all profiles to logins and such if using this method, but it saved quite a bit of downtime as opposed to an in place upgrade.
September 2, 2008 at 9:00 am
What about licensing when upgrading? Do you use the same key? Where can I find the current key?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply