How to connect to the embedded SQL Server (microsoft##ssee) used by WSS 3.0

Issue:

You would like to connect to the embedded SQL Database (microsoft##ssee) used by Windows Sharepoint Services. 

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$microsoft##ssee\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.  Backup the WSS_Content Database

BACKUP DATABASE WSS_Content

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

      WITH FORMAT

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

Article ID: 517, Created On: 12/12/2011, Modified: 12/12/2011