March 10, 2010 at 8:24 am
Hi,
I need to move my master DB which is currently residing on SQL 2000 instance, I am performing a side by side upgrade, I know that the user DBases will be restored by attached/Detached method, but master DB has a seprate process.
I have some steps, but those are in vague format, I only know that I need to get the master DB on 2005 instance to single user mode from the command prompt & then fire the restore command mentioning the path of sql 2000 master DB.
Can somebody give step by step guide to perform this critical task?
Thanks
Asir
March 10, 2010 at 8:51 am
asir83 (3/10/2010)
Hi,I have some steps, but those are in vague format, I only know that I need to get the master DB on 2005 instance to single user mode from the command prompt & then fire the restore command mentioning the path of sql 2000 master DB.
You will not be able to restore a SQL Server 2000 system database backup to a SQL Server 2005 instance, no matter what mode you are in.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 10, 2010 at 9:03 am
asir83 (3/10/2010)
Hi,I need to move my master DB which is currently residing on SQL 2000 instance, I am performing a side by side upgrade, I know that the user DBases will be restored by attached/Detached method, but master DB has a seprate process.
I have some steps, but those are in vague format, I only know that I need to get the master DB on 2005 instance to single user mode from the command prompt & then fire the restore command mentioning the path of sql 2000 master DB.
Can somebody give step by step guide to perform this critical task?
Thanks
Asir
Best practices is not to attach\Detach or Backup\Restore system databases, Script objects in system databases and run them in new server.
Here is the step by step process for upgrade.
Have Server guys Disable IIS on Application Servers
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 DTS packages
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
Shut down the SQL 2000 box
Copy Install files to new server.
Start SQL 2005 32\64 bit Ent install
Apply latest SP
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 DTS packages
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.
re-initialize subscribers.
Run row counts on both publisher and subscriber.
Enable IIS on on application servers.
Test
March 10, 2010 at 9:08 am
Thanks for the prompt reply.
If that is the case, then what about the information of the user DB of 2000 that will be moved to 2005? Master DB also contain the login/user information, DB name, size. Isn't this info not required when we upgrade sql from 2000 to 2005?
Also wanted to know about the maintainace plan that I made on 2000? Is there any way to move that as well to 2005? For that I may also need to move the MSDB of 2000 to 2005 as it contains the maintainance details, is that possible? or what is the alternate way of moving the maintainace plan from 2000 to 2005?
RegaRDS
aSIR
March 10, 2010 at 9:17 am
asir83 (3/10/2010)
Thanks for the prompt reply.If that is the case, then what about the information of the user DB of 2000 that will be moved to 2005? Master DB also contain the login/user information, DB name, size. Isn't this info not required when we upgrade sql from 2000 to 2005?
Also wanted to know about the maintainace plan that I made on 2000? Is there any way to move that as well to 2005? For that I may also need to move the MSDB of 2000 to 2005 as it contains the maintainance details, is that possible? or what is the alternate way of moving the maintainace plan from 2000 to 2005?
RegaRDS
aSIR
Read the steps carefully, Script out Logins and users from SQL 2000, Scripting out jobs will also have maintenance plan jobs. Don't forget to run link orphaned users script after upgrade. You can find tons of Script for Logins\Users and Link orphan users in internet and SSC.
When you restore User Databases, master database system table will update the new info in new instance.
March 12, 2010 at 9:08 am
Thanks for the update, I have followed ur steps & all looks gud except the maintaince plan.
As per ur advice i have scripted all the jobs of maintainance plan from sql 2000 to sql 2005, job are now created on 2005 & running fine, but I donot have the maintainance plan that we see under Management.
Is there a way to copy that from 2000 to 2005, there is no option to script the maintainace plan.
Please advice.
Rgrds
Asir
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply