How to remove Service Pack from SQL Server 2005 :
We cannot directly uninstall the Service pack applied to SQL Server 2005. Uninstalling Service pack from Add/remove programs is introduced from SQL Server 2008 SP1 version.
We have to uninstall the SQL Server and reinstall it to roll back the update.
Steps to perform:
1. Uninstall SQL Server 2005 (DBA):
· To begin the uninstall process, on the Microsoft Windows desktop click Start, click Control Panel, and then double-click Add or Remove Programs.
· Select the SQL Server 2005 component to uninstall, and then click Remove. This will start the SQL Server 2005 Installation Wizard.
· On the Component Selection - Uninstall page, select any server and/or shared components to remove.
· Click the Report button to view the list of SQL Server 2005 components and features installed on your computer. The report includes version, edition, update level, and language information for each installed component and feature.
· To continue, click Next.
· On the Confirmation - Uninstall page, review the list of components and features that will be removed. To continue, click Next.
· The Setup Progress page will display Setup status.
· On the Completing Setup page, click Finish to exit the Installation Wizard. Accept license agreement and click Next
2. Software Installation (DBA):
· Install SQL Server 2005
· Apply Service pack (Apply earlier SP)
1. Recovery Method 1:
1. We already took the physical files backup (.mdf and .ldf)
2. Once server installation and patching is done (Make the server version same as the it was earlier before uninstalling it)
3. Stop the SQL Server services ( Engine and Agent)
4. Go to location where physical files are present
<Drive>\<Programfiles>\Microsoft SQL Server\MSSQL\Data
Move the ‘Data’ folder to backup location and replace it with physical files taken before uninstalling the server.
5. Now restart the services of SQL Server. It will be up and running fine.
2. Recovery Method 2:
A. Recover Master Database from the backup taken at beginning of process:
· Start SQL server from command line
· SQLpath\binn\sqlservr.exe –m -S<ComputerName>\<InstanceName> from command prompt
· GUI Method to start the server in Single user mode:
· Use SQLCMD to restore master from new window, sample command below
Syntax:
sqlcmd -S<ComputerName>\<InstanceName> -E -Q"RESTORE DATABASE master FROM DISK =’’”
· SQL Server will be down once master restore is completed.
· Restart SQL server in multi user mode. Starting server in multi user by just removing the command mention earlier for single user mode.
A. Restore model and msdb Database
Syntax:
Restore database <databasename>
From disk=’<Drive>\database_backupname.bak’
B. Recover User Databases:
Restore all the User databases using FULL Backup with RECOVERY.
Syntax:
Restore database <databasename>
From disk=’<Drive>\database_backupname.bak’
C. Post Recovery Steps for Method 1 and method 2:
· Execute DBCC on all databases and test consistency from query analyzer
· Execute sample queries and check
· Verify logins, Identify and fix orphaned users
· Backup system and user databases
· Check Jobs in SQL Server Agent.
Hope the above information helps
Thanks
