March 23, 2010 at 3:41 am
Hi all,
We need to move our production sql server 2005 to 2008,pls give the steps we need to do,and what are the things we need to check.
pls help me.
March 23, 2010 at 5:22 am
Set All user dbs to RESTRICTED_USER
Verify no user connections
Disable SQL Agent
Script out DB_Mail profiles
Script out SQL Jobs
Script out Users/Logins
Script out DB_Mail profiles
Save out All SSIS packages
Script out, Linked Server connections, Operators, Alerts, Proxies.
Full Backup of all DB's
Remove(Script out) All replication, publisher, subscriber and distributor
Restore only user databases (Don't restore System databses)
Run add user connections
Run link_users script
Install DB_Mail profiles from script.
Run script to add link servers
test link servers
Install Operators, Alerts, Proxies from script.
Install SSIS packages
Install jobs from script
Test at least one job
Run publisher replication script
Run subscriber replication script - If this server is also a subscriber.
March 23, 2010 at 5:22 am
Change compatability mode from 90 to 100
March 23, 2010 at 9:10 am
DBA_1276 (3/23/2010)
Hi all,We need to move our production sql server 2005 to 2008,pls give the steps we need to do,and what are the things we need to check.
pls help me.
I have suggested run the upgrade advisory check all the t-sql queries then change the compatibility level.
First do it in your development server once everything is fine then go ahead for production.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 25, 2010 at 6:14 am
I am just completing a migration from SQL 2005/Win2003 to SQL 2008/Win2008. Here are some links to review:
1) Review this forum entry: http://qa.sqlservercentral.com/Forums/Topic843135-1550-1.aspx
2) Here is a good article on moving Users from one server to another: http://support.microsoft.com/kb/918992
I have migrated servers two times now (2005 -> 2005 and now 2005 -> 2008). The steps I use:
a) Backup all of the databases.
b) Write a script to copy all of the database names, name of backups, etc. and put them into a table.
c) Create a temp database on the new server, put the table and data from step (b) in the server.
d) Using a USB drive, copy the backups from the old server to the new server (USB drive has the advantage of speed, not having to use UNC connections to get to those backups).
e) Copy the users from the old server to the new server (if it is a clean new server the SIDs will remain the same between the two servers).
f) Copy any SQL Agent jobs from one server to another (I use the Idera SQL Job Manager to copy the jobs from one server to another. I believe it may still be a free download? http://www.idera.com/Products/Free-Tools/SQL-job-manager/ ).
Unfortunately, the migration does take a while... necessary weekend work in my case. I was lucky enough that our Production servers are not highly transactional, so we could afford to take them down for 8 hours.
I can post a few of my scripts, if you are interested. 😀
Hope this helps! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply