Tag Archives: Best Practice

Best Practices for applying SQL Server Service Packs

Posted on by
3

This blog will make a list of best practices to follow while applying Service Packs or Cumulative Updates on your SQL Server Instance(s).

  1. Before applying Service Packs, read the list of bugs that have been addressed in the Service Pack (usually documentation is provided on the download page itself).
  2. Always apply Service Packs and Updates on Test / Development Instances. Once the Instance with the new Service Pack is up and running without any issues for a week or two, plan the same steps on your Production.
  3. List out all SQL Server Instance(s) on the box (if you have multiple instances) and make sure you are going to apply Service pack for the required instance(s) only.
  4. Broadcast an email to all the users who would be affected by Database Instance, about the system downtime. 
  5. Make a plan for a quick rollback, in case of unforeseen issues.
  6. Send out an email that the maintenance is beginning.
  7. Ensure that no SQL Agent jobs are running, it might be a good plan to disable the SQL Agent in order to avid any jobs starting / running while applying the Service Pack.
  8. Take the full backup for all databases, both User databases and System databases.
  9. Apply the Service Pack, only to that instance that you have planned to (in case of multiple instances on same machine)
  10. Restart the SQL Instance.
  11. Check for all entries in SQL Server Error Log about the Service Pack update and ensure there are no errors written.
  12. Check for the Service Pack level of the SQL Instance.
  13. Send an email that the Service Pack was applied and SQL Server is running smoothly.

Now is the time you monitor your applications and any custom scripts that you wrote, are working properly. As mentioned earlier, after a week or two follow the steps from points # 3 on your target Prod Instance…..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Verifying SQL Server Database Backups

Posted on by
Reply

We know that data in a database is important and also aware that database backups are required in case of a disaster in order to restore the database to a previous state (before the disaster). As a Database Admin it is crucial to perform regular database backups. One of the important tasks many people might not pay attention with respect to database backups is to verify whether or not the database backup is reliable. What I mean is “whether that particular database backup is good to be restored?“. It would be a real disaster to have a backup which is not good to be used, in case of recovery. In order to be on a safer side, we need to verify the backups that we have taken. There are 2 ways you can verify whether or not a backup is reliable; one way would be to try restoring from the backup and the other way is to try a restore verifyonly. When a restore the database from a backup (procedure 1), you actually need to have sufficient disk space to hold the database being restored, where as using restore verifyonly option, you do not need to have the free disk space for the database. It verifies whether or not the particular database backup is reliable or not (indicating that it is possible to restore the database from that particular database).

Let’s say you stored your database backup on C: Drive as dbbackup.bak, use the following command to verify if that backup is valid.

RESTORE VERIFYONLY FROM DISK = 'C:\dbbackup.bak'

The image below shows screen shot of the scripts used in this example.

It would be a good practice to verify the backup files using this option on a regular basis.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty