Wednesday, September 8, 2010

MSDE

What is MSDE?
MSDE is a limited version of the Microsoft SQL Server. In short, it is the Microsoft SQL Server 2000 database engine without any of the fancy UI tools, and with some limitations in the database size and the number of connections. The MSDE database is free, and can be distributed embedded in your own applications or as a small stand alone SQL server. It is ideal for small websites and small businesses with less than 25 simultaneous users. The database is limited to 2 GB of data storage space, but you can easily upgrade it to a full Microsoft SQL Server without any limitations.

To login into MSDE there is a tool called osql.
-E -- Windows Authentication
-S -- ServerName
C:\> osql -E -Slocalhost\myinstance
1>select @@VERSION
2>go
3>quit

C:\ >osql -E -S localhost\myinstance
1> use master
2> go
1> select name from sysdatabases
2> go

To Login into MSDE using SQL Server Authentication

C:\> osql -Usa -Ppassword -Slocalhost\myinstance

To Detach Database
1> exec sp_detach_db 'mydatabase'
2> go

To Attach Database
1> exec sp_attach_db @dbname = 'mydatabase',
2> @filename1 =
'C:\Program Files\Microsoft SQL Server\MSSQL$LITBASE\Data\mydatabase.mdf',
3> @filename2 =
'C:\Program Files\Microsoft SQL Server\MSSQL$LITBASE\Data\mydatabase_log.LDF'
4> go