MSDE Tips and Tricks

I got MCDBA but I wasn't really skilled on MSDE so I spent some time with it and I found these useful tips and tricks:

Install MSDE on Win2003
MSDE installation failed on Win2003 because a strong password is required, to set the 'sa' password during installation run Setup.exe SAPWD="MyNewPassword"

Manage MSDE remotely with Enterprise Manager
By default you are not allowed to manage it remotely with Enterprise Manager because the network libraries are disabled, you can enable it during setup processs running Setup.exe DISABLENETWORKPROTOCOLS=0 or later configuring them from SVRNETCN.exe (located in C:\Program Files\Microsoft SQL Server\80\Tools\Binn)

Create a Named Instance
If you want to create a Named Instance, you should run Setup.exe "INSTANCENAME="myinstance"

Enable Mixed Autentication
By default you are not authorized to logon with SQL Authentication, if you would like to do it (I don't suggest it because it's not really secure) you can set it during installation with Setup.exe SECURITYMODE=SQL

Manage MSDE locally
You can install two free programs:
DBAmgr2K: http://www.asql.biz/DbaMgr/DownLoad2k.shtm
MSDE Query Tool: http://www.msde.biz/

Create Backup in a script
OSQL -E -n -Q "BACKUP DATABASE msdb TO DISK = 'E:\Backup\msdb.bak'"

Verify version
You can do it like SQL 2000 running select @@version
8.00.194 - MSDE 2000
8.00.384 - MSDE 2000 SP1
8.00.534 - MSDE 2000 SP2
8.00.760 - MSDE 2000 SP3

Share on Google Plus

About Vittorio Pavesi