SQL Server Express UPDATE, January 8, 2007: Moving Data

Once you learn where the data lives, how do you move it from system to system? In a follow up to his article about data location, Mike Otey shows you step-by-step instructions for how to move data with ease.

Consolidating SQL Server Deployments for High Availability and Ease of Management

Does your SQL Server deployment suffer from low utilization rates, sub-optimal availability, and management challenges? Learn to overcome these difficulties by deploying a single database utility that can help lower your cost of ownership by 70 percent and react to changes within 30 seconds by downloading this on-demand Web seminar.

Moving Data

by Michael Otey, [email protected]

In the last Express Essentials column, you saw that SQL Server 2005 Express Edition actually stores its database data in two OS files. The first, the primary file, is the Data file, which ends in the extension .mdf and stores the database data. The second is the Log file, which ends with the extension .ldf and stores the database transactions that have taken place. The primary purpose of the Log file is to enable you to roll back changes that have occurred in the database--restoring it to a known state at some specified point in time.

Knowing where your data is located is an important step in knowing how to move your SQL Server Express databases between different systems. Although moving databases isn’t difficult, it's not quite as simple as just copying files from one system to another. Copying files is an essential part of the process of moving SQL Server Express databases between systems, but the other part is telling the SQL Server Express instance how to identify those files. You give the SQL Server Express instance that information by using the sp_attach and sp_dettach stored procedures. The sp_attach stored procedure tells SQL Server Express the location of the data files to use, and the sp_dettach stored procedure tells SQL Server Express to stop using a set of data files.

Now, I'll show you the steps and T-SQL commands you'll use to move a SQL Server Express database from one system to another:

1. Detach the database from the source system:

EXEC sp_detach_db 'MyDatabase'

2. Copy the Data and Log files to the target system and move them into the SQL Server\Data directory:

XCOPY MyDatabase.mdf \\server\share XCOPY MyDatabase.ldf \\server\share

3. Attach the database to the target system:

EXEC sp_attach_db 'MyDatabase', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.LDF'

You can run the sp_attach and sp_detach stored procedure from SQL Server Management Studio Express (SSMSE). Run the XCOPY command from the command prompt.

High Availability Solutions for Windows, SQL Server, and Exchange Servers

When disaster strikes your servers, you need answers, whether those servers are dedicated to Windows, SQL Server, or Exchange. Make sure that if an emergency occurs, you’re prepared. Get the full eBook and get started on your recovery plan today!

Connecting to Instances with SQL Server Enterprise Management Studio Express

by Michael Otey, [email protected]

One of the hurdles that faces new SQL Server 2005 Express Edition users is getting connected to the database. The tool that you'll probably use first is SQL Server Management Studio Express (SSMSE). To connect to SQL Server Express, you need to supply the instance name of the database that you want to connect to. You can use SSMSE to connect to either local or remote instances of SQL Server Express.

The default local installation of SQL Server Express uses the instance name SQLExpress but you have the option of changing this name during the installation process. To connect to the default instance of SQL Server Express, first start SSMSE by using the SQL Server 2005, SQL Server Management Studio Express option located in the Start menu. In the Connect to Server box, fill in the Server Name dialog box with one of the following values: .\sqlexpress, (local)\sqlexpress, or \sqlexpress. The .\sqlexpress and (local)\sqlexpress values essentially instruct SSMSE to connect to the local system. One point to remember is that by default, the (local)\sqlexpress value uses the Shared Memory provider, which is the fastest client database connection. However, the Shared Memory provider can be used only when the connection is taking place on the same physical system as the SQL Server Express database. As you might expect, if you want to connect to a remote SQL Server Express database, you need to specify the \sqlexpress instance name.

SQL Server Management Studio Express SP2

by Michael Otey, [email protected]
If you’re running SQL Server 2005 Express Edition on the x64 platform, you'll definitely want to check out the Microsoft download for SQL Server Management Studio Express Service Pack 2 (SSMSE SP2). Earlier versions of SSMSE wouldn't run on the x64 platform even though SQL Server Express ran as a 32-bit application using Windows x64 Windows on Windows (WOW) support. SSMSE SP2 will run on Windows x64 as long as the 64-bit version of the .NET Framework 2.0 is available.

The Essential Guide to Jump Starting Your SQL Server Skills

Got information? Good! Now, can you use it? Get up to speed on database design and hierarchy, including columns and datatypes, creating databases, and using the Query Editor. Download your free copy today!

Deploy Cross-Platform Data

Are you an Oracle professional who has cross-platform responsibilities, or do you need to transfer your skill set to SQL Server? If so, register free to attend the Cross Platform Data online event January 30 and 31 and February 1, 2007. In a seminar featuring SQL Server and Oracle experts Andrew Sisson from Scalability Experts and Douglas McDowell from Solid Quality Learning, you'll learn key concepts about SQL Server 2005, including how to deploy SQL Server's BI capabilities on Oracle, proof points demonstrating that SQL Server is enterprise-ready, and how to successfully deploy Oracle on the Windows platform.

Find the buried treasure by uncovering the secrets to Web filtering. Complete this quiz correctly and you could be a winner!

Avoid User Downtime

When your systems go down, your users’ productivity grinds to a halt. User downtime is one of the fastest growing concerns among businesses. This free Web seminar teaches you how to keep your users continuously connected and your business up and running. View the on-demand Web seminar now!

Use Fax Technology to Benefit Your Bottom Line

Integrate fax services with business applications for major increases in ROI. Find out how fax technology can benefit your bottom line and improve business processes. Download the free ebook today!

Upgrade Your Disaster Recovery Knowledge

Disaster recovery isn’t just a theory for most businesses—it's a harsh business reality. Improve your own disaster recovery efforts today and learn from real-life disaster survivors. Make sure that your plan is ready before a disaster strikes--download this free white paper today!

Layton Technology Simplifies Database Management

Layton Technology's administration tool, Layton Database Manager, helps you manage your SQL Server Express databases by providing a GUI to perform all database functions. With Database Manager, you can add, edit, and delete databases, tables, views, roles, stored procedures, and functions. You can also use Layton Technology's software to create and schedule jobs, change column properties, and back up and restore databases. You can download a free, 7-day trial of Database Manager at Layton Technology's Web site. Pricing for Database Manager starts at $795. For more information, contact Layton Technology at 813-319-1390.

Special Invitation for VIP Access

Become a VIP subscriber and get continuous, inside access to ALL the content published in Windows IT Pro magazine, SQL Server Magazine, Exchange & Outlook Pro VIP, Scripting Pro VIP, and Security Pro VIP. Subscribe now and SAVE $100: https://store.pentontech.com/index.cfm?s=1&promocode=eu276buv

Ring in the New Year with SQL Server Magazine

Don't miss SQL Server Magazine in 2007! As a subscriber, you'll have full access to must-have coverage of high availability, SQL Server and Microsoft Office integration, business intelligence, clustering, Reporting Services, and much more. Order now and get 58% off the cover price.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.