How to connect to the embedded SQL Server (SQLEXP_VIM) used by VirtualCenter

Author: NetworkAdminKB.com
Created: 2009-02-12
Modified: 2009-06-02

Issue:

You would like to connect to the embedded SQL Database (SQLEXP_VIM) used by VirtualCenter. 

 

After connecting to the database you can use SQL commands to export data, backup, restore, etc.

 

Solution:

On the VirutalCenter server perform the following steps

1)      Open a command prompt

2)      Type the following command and press enter

a.       sqlcmd -S \\.\pipe\mssql$sqlexp_vim\sql\query -E

3)      You can now run various SQL commands to gather information about the SQL Server and its databases.  Below are common/useful commands.

a.       Show all databases (assumes master database is in use)

select name, filename from sysdatabases

 

b.      Detach a database with following command:

exec sp_detach_db 'db_name'

 

c.       Attach a database with following command:

exec sp_attach_db @dbname = N'db_name',

      @filename1 = N'C:\path\file.mdf',

@filename2 = N'C:\path\file.ldf'

 

d.      Specify which DB to run all future commands against.

Use db_name

 

e.       Show all User objects (tables) in the current database

select name from sysobjects where type='u'

 

f.        Show DNS name and IP address for ESX Hosts

Use VIM_VCDB

select dns_name, ip_address from VPX_HOST

 

g.       Backup the VIM_VCDB Database

BACKUP DATABASE VIM_VCDB

      TO DISK = 'C:\Backup\ VIM_VCDB.bak'

      WITH FORMAT

 

4)      Type GO and press enter after one or more SQL statements to run them.

Article ID: 168, Created On: 9/18/2011, Modified: 9/18/2011