January, 8 2006: 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.
Subscribe to SQL Server Magazine and make sure you add sql_express@lists.sqlmag.com to your list of allowed senders and contacts:
https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491
PLEASE VISIT OUR SPONSORS, WHO BRING YOU THIS UPDATE FOR FREE:
Consolidating SQL Server Deployments for High Availability and Ease of Management
http://www.sqlmag.com/go/seminars/polyserve/consolidatesql/?partnerref=SQLExpTop0108
High Availability Solutions for Windows, SQL Server, and Exchange Servers
http://www.windowsitpro.com/go/ebooks/neverfail/hasolutions/?code=SQLExpMid0108
The Essential Guide to Jump Starting Your SQL Server Skills
http://www.sqlmag.com/go/essential/quest/jumpstart/?code=SQLExpHot0108
======= Contents ======================
January 8, 2006
Express Essentials
- Moving Data
Features
- Jump Start: Connecting to Instances with SQL Server Enterprise Management Studio Express
- Check It Out: SQL Server Management Studio Express SP2
Resources and Events
- Deploy Cross-Platform Data
- Find the Buried Treasure
- Avoid User Downtime
- Use Fax Technology to Benefit Your Bottom Line
Featured White Paper
- Upgrade Your Disaster Recovery Knowledge
SQL Server Products
- Layton Technology Simplifies Database Management
Exclusive Email Offers
- Special Invitation for VIP Access
- Ring in the New Year with SQL Server Magazine
===== Sponsor: PolyServe ====================================
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.
http://www.sqlmag.com/go/seminars/polyserve/consolidatesql/?partnerref=SQLExpTop0108
1. ======= Express Essentials ==================================
Moving Data
by Michael Otey, mikeo@windowsitpro.com
In the last Express Essentials column, "Where the Data's At" ( http://www.sqlmag.com/articles/index.cfm?articleid=94776& ), 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.
==== Neverfail =============================================
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!
http://www.windowsitpro.com/go/ebooks/neverfail/hasolutions/?code=SQLExpMid0108
2. ==== Features =======================
===== Jump Start =====================================
Connecting to Instances with SQL Server Enterprise Management Studio Express
by Michael Otey, mikeo@sqlmag.com
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.
==== Check It Out ======================================
SQL Server Management Studio Express SP2
by Michael Otey, mikeo@sqlmag.com
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. You can download SSMSE SP2 at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=ccbda432-8ecf-4c82-bdef-b575eaf07f55&DisplayLang=en
==== HOT SPOT: Quest Software ==================================
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!
http://www.sqlmag.com/go/essential/quest/jumpstart/?code=SQLExpHot0108
3. ==== Resources and Events ===========
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.
http://events.unisfair.com/rt/sql/?code=0103emailannc
Find the Buried Treasure
Find the buried treasure by uncovering the secrets to Web filtering. Complete this quiz correctly and you could be a winner!
http://popquiz.windowsitpro.com/stbernardtreasurehunt/
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!
http://www.windowsitpro.com/go/seminars/neverfail/usercontinuity/?code=0103emailannc
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!
http://www.windowsitlibrary.com/Ebooks/faxservers/Index.cfm?code=0103emailannc
4. ==== Featured White Paper ============
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!
http://www.windowsitpro.com/go/whitepaper/neverfail/disaster/?code=0103featwp
===================================
WANTED: Your reviews of products you've tested and used in production. Send your experiences and ratings of products to "whatshot@windowsitpro.com" and get a Best Buy gift certificate.
5. ==== SQL Server Product ====
by Blake Eno, products@sqlmag.com
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.
http://www.laytontechnology.com
6. ==== Exclusive Email Offers ====
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:
https://store.pentontech.com/index.cfm?s=9&promocode=eu216cus
==== 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.
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
http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%
To manage your email account, simply log on to our Email Preference Center. http://www.sqlmag.com/email
View the SQL Server Magazine Privacy Policy.
http://www.sqlmag.com/aboutus/index.cfm?action=privacy
SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department
Copyright 2007, Penton Media, Inc. All Rights Reserved.
End of Article
