How to Get DB Server Information from the Command Line

  • Article

Life is simple in SharePoint development environments. You typically have administrative access to all servers. Need to check database permissions or grant access to another account? Want to create or drop a database? Want to do a backup or restore? No problem, logon the DB server and open up SQL Server Management Studio.

But what would it be like to develop in a SharePoint environment if you didn’t have database server access? This is a typical situation at large enterprises. The database team is a separate group, possibly located in another time zone on the other side of the world. This team closely guards their turf, not allowing anyone outside their team to logon their database servers. You panic because you can’t touch SQL Server!

CAVEAT: Remember, directly accessing SharePoint databases is NOT supported. The following procedures illustrated what could be done in an emergency when a DBA is not available. This information is presented as a troubleshooting alternative in development and test environments only. These procedures should NOT be performed against a production farm.

But wait, you are not totally out of luck. First, let’s analyze the situation. What do you know?

  1. You probably have an installation account (local server administrator) and password.
  2. Similarly, you probably have the SharePoint farm account and password.
  3. You know the farm account can connect to the SQL Server, after all, that is how the configuration database is accessed.

With this information, you probably have a door to the SQL Server. It may not be as easy as the point-and-click SQL Server Management Studio UI, but it is all you need. So what do you do?

  1. Download the SQLCMD command line utility. This is a small (12 MB) msi package you can install on one of the SharePoint servers. Scroll down to the heading Microsoft SQL Server 2008 R2 Command Line Utilities. Quoting from that page:
    The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 instances.
    Click the link X64 Package (SqlCmdLnUtils.msi) to download. If you are downloading to your local machine because of proxy or firewall restrictions on the SharePoint server, map a drive to the SharePoint server and copy the installation package to the SharePoint server.
  2. Logon the SharePoint server as the installation account. Install the package, taking all the default options. The installation only takes a few seconds.
    sqlcmd1
  3. Optionally, update the PATH environment variable to make it easier to use SQLCMD in command prompt. Open the Control Panel > System > Advanced system settings. Click the Advanced tab, and then the Environment Variables button. In the bottom pane, System variables, scroll down to the Path variable and then click the Edit button.
    Go to the end of the path, and then add a semicolon and C:\Program Files\Microsoft SQL Server\100\Tools\Binn or whatever path where you installed SQLCMD. Click OK to close all the dialog windows.
    sqlcmd2
  4. Logoff off as the installation account and logon as the farm administrator account so you are running in the security context of the farm account, or alternatively, open a command prompt and Run as the farm account.
  5. If you don’t know the SQL Server instance name from memory, open Central Administration > System Settings > Manage servers in this farm, and find the database server under Farm Information at the top of the page.
  6. Open a command prompt and begin entering Transact-SQL commands.

The farm administrator has securityadmin and dbcreator roles already (else the farm wouldn’t work), so you can submit T-SQL commands.

First, connect to the server by entering:

sqlcmd –S <database-server-instance-name>

To find what accounts have server roles, enter:

EXEC sp_helpsrvrolemember
GO

sqlcmd3

To display all the database names, enter:

SELECT SUBSTRING(name,1, 64), database_id FROM sys.databases ORDER BY name
GO

sqlcmd5

To look at information for a specific database, you must switch context to that database with the USE command. The following switches context to the Secure Store Service database.

USE SecureStore2_DVPROD_shared
GO

To find the DBOs for the database, enter:

EXEC sp_helprolemember 'db_owner'
GO

sqlcmd4

To get the database sizes, create a SQL batch file with notepad. Enter the following SQL statements and save the file as DatabaseFileSizes.sql.

SELECT
    databasename = DB_NAME(dbid),
    name,
    fileid,
    drive = LEFT(filename,1),
    filename,
    filegroup = FILEGROUP_NAME(groupid),
    'size KB' = CONVERT(nvarchar(15), CONVERT(bigint, size) * 8),
    'maxsize KB' = (CASE maxsize WHEN-1 THEN
              N'Unlimited'
          ELSE
              CONVERT(nvarchar(15), CONVERT(bigint, maxsize) * 8) END),
    'growth' = (CASE status & 0x100000 WHEN 0x100000 THEN
              CONVERT(nvarchar(15), growth) + N'%'
          ELSE
              CONVERT(nvarchar(15), CONVERT(bigint, growth) * 8) END),
    'usage' = (CASE status & 0x40 WHEN 0x40 THEN 'log only' ELSE 'data only' END)
FROM sys.sysaltfiles
ORDER BY drive, size

Next, exit the current SQLCMD session by entering EXIT.

Enter the following command which references the file just created batch file as the input file:

sqlcmd –S <database-server-instance-name> –I .\DatabaseFileSizes.sql –O .\DatabaseFileSizes.txt

The output file (DatabaseFileSizes.txt) can be imported into Excel for further analysis; such as, tracking trends from month-to-month.

sqlcmd6 

These examples should give you a good taste of what is possible. You are limited only by the farm account permissions and your knowledge of T-SQL.