Applying SP4

Hi,
I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
This Test Server will be made the new Production Server. I have to apply SP4
to the Test server. Do I -
First apply SP4 to the test server and then Backup system and user databases
on Production Server and restore them on the Test Server or
Do a Backup of system and user databases on Production Server, restore them
on test server and then apply SP4 on the test server?
Will there be a issue after restore on the test server as it has SP3 and the
Production Server has SP2?
Is Backup and restore a better way here in comparison to detach and attach
database?
Thanks
That all depends, what is your goal?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply
> SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user
> databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore
> them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and
> the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
>
>
|||Thanks for the response. My goal is to promote the Test Server to a Full
Production Server running SQL Server 2000 (SP4)
"Aaron Bertrand [SQL Server MVP]" wrote:

> That all depends, what is your goal?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
>
>
|||Hi
"sharman" wrote:

> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
Although I don't think you will have a any major problems with the migration
from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
server and test you application, then backup the databases, upgrade the
production server to the same level before backing up the database again and
migrating them.
John
|||> Thanks for the response. My goal is to promote the Test Server to a Full
> Production Server running SQL Server 2000 (SP4)
With the exact same databases on it?
I would upgrade production to SP4, take the backups, then apply SP4 to test,
and then restore the backups.
Remember to get the 2040 hotfix (and maybe even the 2187 cumulative hotfix)
if you are using AWE.
|||This would help me understand Service Packs. If I apply SP4 to Test server
and then migrate databases from Production Server (running SP2) (both system
and user databases) to the Test Server,would it undo the changes done by SP4
on Test Server?
If I migrate system and user databases from Production to Test Server and
then apply SP4 to Test Server and test it. After successful testing I just
migrate the user databases to the test server, would it undo the changes done
by SP4 on Test Server?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Although I don't think you will have a any major problems with the migration
> from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
> server and test you application, then backup the databases, upgrade the
> production server to the same level before backing up the database again and
> migrating them.
> John
|||Hi
"sharman" wrote:

> This would help me understand Service Packs. If I apply SP4 to Test server
> and then migrate databases from Production Server (running SP2) (both system
> and user databases) to the Test Server,would it undo the changes done by SP4
> on Test Server?
> If I migrate system and user databases from Production to Test Server and
> then apply SP4 to Test Server and test it. After successful testing I just
> migrate the user databases to the test server, would it undo the changes done
> by SP4 on Test Server?
>
The exact changes to a database made by a release or hotfix is not usually
made available to people outside MS, although I guess you could raise an
incident to ask. Even if the database files are compatible, if you tested on
one version the behaviour of your application will not necessarily be the
same when run on a different version, therefore invalidating your tests. It
is therefore best to upgrade in such a way as everything is in step.
HTH
John