Differences between sp_who and sp_who2

There is a very useful system stored procedure called sp_who on SQL Server that lists the users and processes running on the system. It comes handy when you want to know the loading on the SQL Server or see if any process is blocked.

You run:

exec sp_who

The result will be similar to the following list:

You can filter the data returned to show only those processes that are not idle. If you specify a particular user like sp_who [@login_name =] 'login' then you get processes for that user, otherwise all processes for all users will be listed.

Look at http://msdn.microsoft.com/en-us/library/aa260384(v=sql.80).aspx to get the meaning of each column in the list.

There is another stored procedure called sp_who2 that gives some more information like total CPU time for each process, total amount of disk reads for each process, etc.

If you want see what is running under each process you can use below command.

 DBCC INPUTBUFFER (SPID)

The SPID will get from SP_Who or SP_Who2 commnad.

Using sp_change_users_login ‘auto_fix’ to fix user/login

How to use sp_change_users_login to fix SQL Server orphaned users

Firstly, there may be a number of orphaned users, so the best thing to do is run this inside each database you are checking:

1	USE DatabaseName

2	EXEC sp_change_users_login 'Report';

You will see output like the screenshot attached if there are any orphaned users. In this example, user “db_login1″ is showing up as an orphaned user.

If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1′ is the user in the database, the second instance is the login to be mapped to) :

1	EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77′ as an example.

1	EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';

Testing SQL Server connectivity using SQLCMD.EXE

Testing SQL Server connectivity using SQLCMD.EXE

Our admin guide recommends running tests before installation: "We recommend testing remote database SQL server connectivity before beginning an installation." The steps below detail one way to accomplish some connectivity tests.

Using SQLCMD you can use it to test connectivity to a SQL Server instance, to test that you can connect to the host, it is reachable, TCP/IP connectivity is enabled and that you have at least the right needed to connect to the sql server instance. This simple test will not however determine if you have the necessary priviledges needed to connect to a particular named database, create databases or schema or read, insert or modify data.

These steps assume that Windows Authentication will be used to connect to the SQL Server instance. If SQL Server Authentication instead will be used and not Windows Authentication then the steps are slightly different, there is an additional -P parameter needed. See the SQLCMD.exe help for more information.

Steps to test for connectivity

1. Login as the user that will be used for the service account for GMC on the machine where the prospective GMC install would be performed. IMPORTANT: Please make sure you run the test on the server you wish to install GMC on or the Server that already has GMC installed.
2. If SQLCMD is not installed, install it. Use these instructions to do it manually: Manually Installing Microsoft SQL Server 2005 Command Line Query Utility (SQLCMD)
3. Open a new command window. [Start] -> "Run..." -> Open: cmd.exe
4. In the command window run the a sqlcmd to test the connectivity:

To connect using port number follow the general syntax:

sqlcmd.exe -S tcp:<computer name>,<port number> -Q "select getdate()"

Example of successfully connecting to a SQL Server instance running on a computer named "april" that allows connections to the instance on the static port of 1433.

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:april,1433 -Q "select getdate()"

-----------------------
2009-08-26 00:08:27.847

(1 rows affected)

To connect using a named instance follow the general syntax:

sqlcmd.exe -S tcp:<computer name>\<instance name> -Q "select getdate()"

Example of connecting to a SQL Server instance named "GMC" running on a computer named "mjang-opt745".

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"

-----------------------
2009-08-26 00:09:54.550

(1 rows affected)

Troubleshooting

This is the error you might see if the SQL Server instance doesn't have TCP/IP enabled for remote connections:

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively re
fused it.

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

If you see this error try to run the command without the "tcp:" prefix in front of the computer name; if that is successful then you know that the sql server intance has "named pipes" enabled for remote connections but not "TCP/IP". Have the settings for remote connections on the SQL Server instance changed to enable "TCP/IP" connections as well as "named pipes" using the surface area configuration tool that is installed with the sql server instance.
Example of not specifying forced use of TCP/IP which allows SQLCMD to use named pipes to connect.

C:\Documents and Settings\mjang>sqlcmd.exe -S mjang-opt745\GMC -Q "select getdate()"

-----------------------
2009-08-26 00:09:54.550

(1 rows affected)

GMC requires TCP/IP for its database connectivity to SQL Server because it uses the Microsoft JDBC driver which only supports TCP/IP database connections.

This is the error you might see if the SQL Server instance service isn't running, is fire-walled or an invalid or non-existant computer name was used. (not a very informative error unfortunately)

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

How to schedule and automate backups of SQL Server databases in SQL Server Express

Summary

SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.   

Currently SQL Server Express users can back up their databases by using one of the following methods:

• Use SQL Server Management Studio Express. This is installed together with either SQL Server Express Advanced Service or SQL Server Express Toolkit. For more information, go to the following Microsoft Developer Network (MSDN) website: 
  Create a Full Database Backup (SQL Server)
• Use a Transact-SQL script that uses the BACKUP DATABASE family of commands. For more information, go to the following MSDN website:
  
  BACKUP (Transact-SQL)

This article describes how to use a Transact-SQL script together with Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.

More Information

You have to follow these three steps to back up your SQL Server databases by using Windows Task Scheduler:

Step A: Use SQL Server Management Studio Express or Sqlcmd to create the following stored procedure in your master database:

// Copyright © Microsoft Corporation.  All Rights Reserved.

// This code released under the terms of the

// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL

      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

-- Execute the generated SQL command

       EXEC(@sqlCommand)

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Step B: In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the following examples into that file, depending on your scenario:

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

      sqlcmd -S tcp:<hostname / ipaddress>\EXPRESS,<portnumber> -Q "EXEC sp_BackupDatabases      @backupLocation='D:\SQLBackups\', @backupType='F'" 

sqlcmd -S .\EXPRESS,1433 -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'" 

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS,1433 -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS,1433 -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS,1433 -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.

Step C: Schedule a job by using Windows Task Scheduler to execute the batch file that you created in step B. To do this, follow these steps:

1. On the computer that is running SQL Server Express, click Start, point to All Programs, point to Accessories, point toSystem Tools, and then click Scheduled Tasks. 
2. Double-click Add Scheduled Task. 
3. In the Scheduled Task Wizard, click Next. 
4. Click Browse, click the batch file that you created in step B, and then click Open. 
5. Type SQLBACKUP for the name of the task, click Daily, and then click Next. 
6. Specify information for a schedule to run the task. (We recommend that you run this task at least one time every day.) Then, click Next.
7. In the Enter the user name field, type a user name, and then type a password in the Enter the password field.
   
   Note This user should at least be assigned the BackupOperator role at SQL Server level if you are using one of the batch files in example 1, 3, or 4.
8. Click Next, and then click Finish. 
9. Execute the scheduled task at least one time to make sure that the backup is created successfully.

Note The folder for the SQLCMD executable is generally in the Path variables for the server after SQL Server is installed, but if the Path variable does not list this folder, you can find it under <Install location>\90\Tools\Binn (For example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).

 Be aware of the following when you use the procedure that is documented in this article:

• The Windows Task Scheduler service must be running at the time that the job is scheduled to run. We recommend that you set the startup type for this service as Automatic. This makes sure that the service will be running even on a restart.
• There should be lots of space on the drive to which the backups are being written. We recommend that you clean the old files in the backup folder regularly to make sure that you do not run out of disk space. The script does not contain the logic to clean up old files. 

enable remote connections in SQL Server 2008

How to enable remote connections in SQL Server 2008

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

How to solve this issue?

There are a couple of things that might be going on here… (All of the following configurations are made on the computer running your SQL Server 2008 instance)

Allow remote connections to this server

The first thing you want to check is if Remote Connections are enabled on your SQL Server database. In SQL Server 2008 you do this by opening SQL Server 2008 Management Studio, connect to the server in question, right click the server…

… and open the Server Properties.

Navigate to Connections and ensure that Allow remote connections to this server is checked. Check if this solves the problem. If it does, here you go, continue with whatever you were doing and have a nice day.

Protocols for MSSQLServer

If you’re still running in issues let’s dig a bit deeper. The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Make sure that TCP/IP is enabled and try again. Even though I hope that this resolved your problems there might still be an issue with…

The Firewall

If there is still no communication happening between your computer and the remote SQL Server you most likely need to configure your firewall settings. A good first step is to figure out which port is being used by TCP/IP (and which you need to open in your firewall). You can do this by right clicking TCP/IP and selecting Properties.

Click on the tab IP Addresses and voilà – Port 1433 it is :-) That was easy enough and all there is left to do is to allow inbound TCP/IP traffic on Port 1433 in your firewall. In Windows 7 this works something like this. Open the Control Panel and navigate to Windows Firewall.

Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.

This opens the New Inbound Rule Wizard which you can use to allow inbound traffic on Port 1433 for TCP/IP (and which is exactly how you configured your SQL Server in the steps above). Just follow the steps outlined below and you should be good :-)

That’s it, success! From here you should be able to access your SQL Server remotely. Enjoy!