SQL Server Express UPDATE--August 7, 2006:User Instances

In this issue, you'll learn how to run multiple SQL Server Express User Instances and use the T-SQL RESTORE command. I also mention a Web site that provides connection strings for many database products.

Subscribe to SQL Server Magazine and make sure you add sql_express@lists.sqlmag.com to your list of allowed senders and contacts:
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

User Instances is a feature that makes SQL Server 2005 Express different from other SQL Server editions. Before I explain User Instances, you need to understand that a SQL Server instance is essentially an in-memory occurrence of the sqlservr.exe executable program. Different SQL Server editions support different numbers of instances. For example, the SQL Server 2005 Enterprise Edition supports 50 instances, and the SQL Server 2005 Standard, Workgroup, and Express editions each support 16 instances. Each instance runs separately and has its own set of databases that aren't shared by any other instance. Client applications connect to each instance by using the instance name.

Typically, the first SQL Server instance you install becomes the "default" instance. The default instance uses the name of the computer on which it's installed. You can assign a name to subsequent instance installations, so they're called "named" instances. During the installation process, you can assign any name to a named instance. Client applications that want to connect to an instance use the \ convention. For example, if the default instance name is SQLServer1 and the instance name is MyInstance, the client application would connect to the named instance by using the server name SQLServer1\MyInstance.

As with the other SQL Server editions, SQL Server Express supports the default instance and named instances, but SQL Server Express uses SQLExpress as the default instance name rather than the name of the computer system.

In addition to regular SQL Server instances, SQL Server Express also supports User Instances. User instances are similar to named instances, but SQL Server Express creates user instances dynamically, and these instances have different limitations. When you install SQL Server Express, you have the option of enabling User Instances. By default, User Instances aren't enabled. After installation, you can enter the sp_configure command in SQL Server Management Studio Express (SSMSE) or the sqlcmd tool by using the following syntax:

sp_configure 'user instances enabled','1'

To disable User Instance support, replace 1 with a 0 in the sp_configure command.

User Instances were designed to make deploying databases along with applications easier. User Instances let users create a database instance on demand even if they don't have administrative rights. To utilize User Instances, the application's connection string needs to use the attachdbfilename and user instance keywords as follows:

When an application opens a connection to a SQL Server Express database in which User Instances are enabled and the application uses the attachdbfilename and user instance keywords, SQL Server Express copies the master and msdb databases to the user's directory. SQL Server Express starts a new instance of the sqlserver.exe program and SQL Server Express attaches the database named in the attachdbfilename keyword to the new instance.

AVIcode SQL WP
&nbsp &nbsp &nbsp 80 percent of all software released into production will fail due to quality issues, but proactively monitoring applications throughout the lifecycle will improve quality and reliability. Learn about the two fundamental categories of application errors and methods for quickly pinpointing the root cause of functional errors. Download the whitepaper today!
&nbsp &nbsp &nbsp http://www.sqlmag.com/go/whitepapers/avicode/nettroubleshooting/?code=sqlexp87

*****************************************************

Take the SQL Server Magazine Salary Survey!
&nbsp &nbsp &nbsp We need your help! SQL Server Magazine is launching its third SQL Server Magazine Industry Salary Survey, and we want to find out all about you and what makes you a satisfied database professional. When you complete the survey (about 10 minutes of your time), you’ll be entered in a drawing for one of five $100 American Express gift certificates. Look for the survey results--and how you stack up against your peers--in our December issue. To take the survey, go to
&nbsp &nbsp &nbsp https://websurveyor.net/wsb.dll/12237/SQLSalarySurvey06.htm

You Could Be a SQL Server Innovator!
&nbsp &nbsp &nbsp If you've developed a resourceful solution that uses SQL Server technology to solve a business problem, you qualify to enter the 2006 SQL Server Magazine Innovators Contest! Grand-prize winners will receive airfare and a conference pass to SQL Server Magazine Connections in Las Vegas, November 6-9, 2006, plus more great prizes and a feature article about the winning solutions in the January 2007 issue of SQL Server Magazine. Contest runs through September 1, 2006, so enter today!
&nbsp &nbsp &nbsp http://www.sqlmag.com/awards/?type=innovator

2. ==== Features =======================

Jump Start: Database Restore
&nbsp &nbsp &nbsp by Michael Otey

A good data-protection plan involves two core activities: backup and restore. In "Backing Up Your Database" (in the archive at http://www.sqlmag.com/Article/ArticleID/93004/sql_server_93004.html )I explain how to use T-SQL commands to back up your SQL Server 2005 Express databases. Although you can use SQL Server Management Studio Express (SSMSE) to back up your databases, T-SQL also lets you automate and schedule backups.

In this article, I cover basic full-backup restore. Before jumping into the T-SQL RESTORE commands, I should point out that similar to SQL Server 2005, SQL Server Express supports multiple types of backup and restore options including full, differential, and log backups.

As with the SQL Server Express database backup options, you can restore a database by using either SSMSE or T-SQL commands from SQLCMD or Query Editor. In "Backing Up Your Database," I used T-SQL commands to show you how to back up a database, so I'll show you the T-SQL RESTORE commands in this article. The following code restores the MediaCollection database from a previous full-database-backup disk file named MediaBackup.bak:

As you can see, the RESTORE command for a full backup is straightforward. You provide the name of the database you want to restore after the RESTORE DATABASE command, the name of the disk backup file, followed by the FROM DISK = clause. It's that simple.

When you're writing client applications for SQL Server Express (or any other database for that matter), you always need to use the proper syntax for the connection string that your application uses to connect to a database. One handy resource site that provides connection strings for many database products is http://www.connectionstrings.com . If you aren’t familiar with this Web site, you should check it out.

3. ==== Resources and Events ===========

SQL Server Magazine Connections Conference
&nbsp &nbsp &nbsp Now in its sixth year, SQL Server Magazine Connections returns November 6-9, at Mandalay Bay Resort in Las Vegas. Get down to business today with SQL Server 2005. Interact with and learn from an all-star lineup. Register for one event and attend sessions of the concurrently run events for FREE!
&nbsp &nbsp &nbsp http://www.devconnections.com

Save $40 On Windows IT Pro Magazine
&nbsp &nbsp &nbsp Subscribe to Windows IT Pro magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire Windows IT Pro online article archive, which houses more than 9,000 helpful IT articles. This is a limited-time offer, so order now:
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=1&promocode=eu2068uw

==== Contact Us ====

About the newsletter--letters@sqlmag.com
About the commentary--mikeo@teca.com
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product news--products@sqlmag.com
About your subscription--sqlupdate@sqlmag.com
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, rresnick@sqlmag.com

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

Manage Your Account
You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
&nbsp &nbsp &nbsp http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%