Managing SQL Server Express
by Michael Otey
Database management might not be the first thing that comes to mind when you think about SQL Server 2005 Express, but like any other database system, it has management requirements too. SQL Server Express has self-tuning capabilities that cut down on the number of daily tasks you need to perform to keep the database server running. In addition to daily maintenance tasks, database management involves such tasks as creating databases, tables, and views. For multiuser databases, you'll need to add users and configure the database to allow network access. And all databases need to have some type of backup-and-restore strategy in place to be able to recover from hardware, user, or application errors.
SQL Server Express has two management tools: the sqlcmd command-line utility and SQL Server Management Studio Express (SSMSE). sqlcmd and SSMSE are the primary tools you'll use to create database tables and views and add database users. The sqlcmd tool, which is included with SQL Server Express, is great, but you'll want to be reasonably fluent with T-SQL to use it effectively. To start this tool, enter sqlcmd on the command line. SSMSE is the preferred tool for beginners because it has an easy-to-use graphical-management interface. This interface lets you perform SQL Server Express management tasks, and advanced users can perform command-based management by using the integrated Query Editor that's included with SSMSE. SSMSE is delivered as a part of SQL Server 2005 Express with Advanced Services; alternatively, you can download it separately from the Microsoft Web site at
Note that you can use both sqlcmd and SSMSE to manage multiple instances of SQL Server Express.
Configuring network access is another aspect of managing SQL Server Express. By default, SQL Server Express ships with network access disabled. To enable and disable network access, you can use the SQL Server Surface Area Configuration tool that ships with SQL Server Express and SQL Server Express with Advanced Services. To use the SQL Server Surface Area Configuration tool to change the network access setting, click Start, All Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Surface Area Configuration option, Surface Area Configuration for Services and Connections option. Finally, click Remote Connections from the "Select a component and then configure its services and connections" list.
The other vital database-management tasks are backup and restore. Backing up your databases lets you use the backed-up version to recover your database in the event that a server failure, user error, or application error corrupts the data in your database. You can back up your database interactively or automate the process. To interactively back up databases, you can use either sqlcmd with the BACKUP T-SQL command or SSMSE. To use SSMSE, right-click the database to open the database node you want to back up, then click Tasks, Backup option from the pop-up menu. Performing automated backups is easier than performing manual backups because once the process is in place, it doesn't require any extra manual work. Although SQL Server Express doesn’t have the built-in capability to create an automated backup procedure, you can use a combination of sqlcmd, T-SQL, and the Windows Task Scheduler to create an automated backup solution.
For more information about managing SQL Server Express, you might want to read the Microsoft whitepaper "Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition" at
SQL Server Express Starter Kitsby Michael Otey
There's no doubt that the quickest, easiest way to get started with any new project is to take some existing code and modify it rather than writing everything from scratch. To help you get a jump-start on your SQL Server 2005 Express projects, Microsoft has provided a collection of Starter Kits that you can use as-is or modify to suit your needs. The current set of Starter Kits includes:
You can download any or all starter kits for Visual Studio 2005 Express and SQL Server Express at
Backing Up Your Database
by Michael Otey
Backing up your databases isn’t just a good idea, it's a necessity--even if it's a single-user SQL Server Express installation. Murphy's Law applies to computer systems just like everything else, so be sure to back up your database regularly so that you don't lose your data and all the hard work you put into creating the database.
You can use either SQL Server Management Studio Express (SSMSE) or T-SQL commands from the sqlcmd command-line utility or Query Editor to back up your databases. However, if you want to create an unattended backup process, you really need to use T-SQL. Fortunately, the T-SQL BACKUP command is easy to master. The following code creates a disk device on which SQL Server Express can save the backup, then executes the T-SQL BACKUP command to back up the MediaCollection database.
EXEC sp_addumpdevice 'disk', 'MediaBackup',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\ MediaBackup.bak'
BACKUP DATABASE MediaCollection