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.