August 5, 2010 at 5:36 am
looking for a little a advice from the experts..
I have a pair of Clustered (Active Passive) SQL Servers 2005 Standard Edition (32 bit) on Service Pack 2.
We are moving to a new hosting company and they have built the new cluster with SQL Server 2005 Standard Edition (64 bit) and applied Service pack 3
Will I be able to restore or attach my system databases to this new system given that it is 64 bit instead of 32 and SP3 instead of sp4??? If we backed out SP3 would that make a difference?
August 5, 2010 at 6:21 am
I haven't heard of a SP4 for sql 2005, but you should be able to detach,reattach or backup and restore with no problems on the new system -- as far as user databases.
As far as system databases, msdb with all of your job information should be no problem. I believe you just stop the sql agent service while restoring your backup of msdb.
We're facing the same thing soon, an upgrade of our cluster from sql 2005 enterprise 32bit to running on Server 2008 64bit with sql 2005 enterprise 64bit, and we've already tested this in a dev environment, no problem. What we haven't done is restore msdb since jobs aren't used in the dev environment. I'm not sure about maintenance plans but I believe they're stored in msdb also.
You'll want to script out all of your logins as well as any code such as stored procedures in the master database so you can reapply them. Restoring the master database from backup is a pain -- I don't recall all the specifics now but for our upgrade I'm just planning to re-apply all logins and any code such as procs, custom tables, etc stored in master. Any such code or database users associated with user databases will be restored when you restore from backup or re-attach.
August 5, 2010 at 6:26 am
the sp4 was a typo.. I am going from 2005 32bit with SP2 TO 2005 64bit with SP3
I'm not concerned about the logins, those will get scripted out and recreated before the databases are restored.. I'm more concerned about all the SSI and legacy DTS packages.. They should come over with the MSDB restore.. correct??
I'm just wondering what wonderful fun I'm in store for trying to restore JUST the MSDB and the difference in the service pack levels... any ideas?
August 5, 2010 at 6:27 am
You will be able to restore system databases. You may refer this article for getting started.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 5, 2010 at 6:36 am
If you have legacy DTS packages they may not port to 64bit. SQL 2005 maintenance plans are SSIS packages and I've read about using SSIS to save them for later application to a new server. Hopefully a restore of msdb will handle that, but not the legacy DTS packages.
August 5, 2010 at 6:57 am
so it is your experience that I should be able to restore just the MSDB system database from my SQL Server 2005 sp2 install to my new SQL Server 2005 sp3 install??
any suggestions for porting over or recreating the legacy DTS packages?????
August 5, 2010 at 8:14 am
Adiga (8/5/2010)
You will be able to restore system databases. You may refer this article for getting started.
so I WILL be able to restore Service Pack 2 system databases to a Service Pack 3 install?????
August 5, 2010 at 8:19 am
Seems like we both need a way to test. You definitely want to restore msdb after master, if you're going to restore master.
http://msdn.microsoft.com/en-us/library/ms190749.aspx
Important Note:
System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2005 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2005 SP1.
Considerations for Restoring msdb
If msdb contains scheduling or other data used by the system, it is necessary to restore msdb from a backup when you rebuild master because the utility deletes and re-creates msdb. This results in a loss of all scheduling information, as well as the backup and restore history. If msdb is not restored, and is not accessible, SQL Server Agent cannot access or initiate any previously scheduled tasks. Therefore, if msdb contains scheduling or other data used by the system, you must restore msdb when you rebuild master.
Restoring model or msdb is the same as for performing a complete database restore of a user database.
You cannot restore a database that is being accessed by users. If SQL Server Agent is running, it can access msdb. Therefore, before restoring msdb, stop SQL Server Agent.
Best Practice RESTORE disconnects users when necessary; however, it is a best practice to shut down applications beforehand.
If you are using the full recovery model for msdb, as recommended, you can bring the database up to the time of your most recent log backup.
August 5, 2010 at 12:59 pm
Is it true that I cannot recovery my Legacy DTS packages by restoring the System databases? If that is the case, than I do not see any use at this point in restoring the system databases at all. I will recovery my logins from a script and then restore my databases and recreate any scheduled maintenance jobs : )
Do you know how I WOULD recovery my legacy DTS packages?
August 5, 2010 at 1:23 pm
Google it with words like dts packages sql 2005 64bit and you find things like this: http://dotnetolympians.wordpress.com/2007/04/14/transferring-dts-to-64-bit-sql-2k5/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply