Category Archives: SQL Server Administration

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

Backups of a higher version cannot be restored on a lower version of SQL Server

Posted on by
2

SQL Server has always allowed a database backup of a lower version to be restored on a higher version where as the opposite is not allowed. That is, if you have backup of a higher version, that cannot be restored on a lower version. We shall see this in this blog post.

This is true even for SQL Server 2008 R2 and SQL Server 2008, even though they both are from the same Major Version SQL 10, there is a change in the minor version SQL Server 2008 is 10.0.xxxx, SQL Server 2008 R2 is 10.50.xxxx

First we create a database by using the simple command “Create Database databasename”, in this case the database name is 2008R2db. In the query I included @@version to show what SQL Instance I am creating this database on.. When the query is run, the database is created and also the SQL Server Version Information…

Next I backup the newly created database to C:\2008R2db.bak using backup database command in T-SQL. The below is the screenshot when I ran the backup command.

Next I login to a SQL Server 2008 Instance in this case, which is on the same machine, and open a new query and run the restore database T-SQL command trying to restore from the backup file that was just created on 2008 R2 instance. 

As mentioned earlier, the restore command fails, giving the error details in detail that the database versions do not match and what SQL Server version the backup was taken on..

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

Finding Sysadmins on a SQL Instance.

Posted on by
11

In this blog we shall see, how to find the users who have sysadmin rights on your SQL Server.

It is very important to know who are the users who have sysadmin rights, because sysadmin is the highest level of security permission on a SQL Server. A user having this permission can do everything on the SQL Instance, such as Create / Delete / Disable other users including other sysadmins. Create / Drop databases, Start / Stop SQL Server and the list can go on..

Using a T-SQL

We can query the catalog view syslogins and find those rows which have a value of 1 in sysadmin column. The query used below filters to find those users who have been granted access to the SQL Server.  

SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1

The result when run on one of my personal SQL Server looks as shown below..

I selected an additional column isntname, in order to see what type of login is that user. When isntname = 0, it indicates the login is a SQL login and 1 indicates a windows based login.

Using SSMS

You can also find out using Management Studio. When connected to the SQL Server,  expand Security node, and expand Server Roles. From the list of Server Roles right click sysadmin and choose Properties from the popup Menu item.

You will see the properties dialog box for sysadmin role as shown below..

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