Follow the below steps before installing service packs on production server.
Step 1: Get all the information about the latest service packs and their reviews
Step 2: Install Service Pack on test server
Step 3: Test the application functionality with the SQL Server with new service pack
Step 4: If application works properly with the Test database server, take the sign off from business team and development team.
Step 5: Before applying service pack on production server, please do the below steps
Cold Backup
Take a full server backup from the Operating System level with the SQL Server services turned off.
Make a note of startup parameter, SQL Server Services Users/Network rights, memory allocation etc.
This will guarantee you can return to a previous state if something goes wrong during the upgrade
Hot Backup
Turn on the SQL Server Services and take the backup of all system databases (Master, Model, MSDB and Copy the Resource database file) and user databases
Step 6: Plan the downtime of the production server for the Service Pack installation and inform the users for the same.
Step 7: Create the backup and restore plan for “What to do if the application is not working properly after service pack installation?”
Step 8: Download the latest service pack and install it using “Setup.exe”
Step 9: Again test the production application and production database server are working properly or not.
