Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Migrate SQL 2008 R2 Database to SQL Express

Posted on 2013-02-05
5
339 Views
Last Modified: 2013-02-20
Hello,

We are currently using SQL 2008 R2 in our VMware environment instead of SQL express.  We were looking at migrating our existing database from a seperate SQL 2008 Virtual machine to an instance of SQL Express on an our Vsphere Server as our environment is fairly small.  What would be the best way to migrate our database from SQL 2008 R2 to SQL Express?

We already attempted to install SQL Management Studio but receive a "Missing Instance ID" error and the searches preformed on migrating SQL databases are all referencing migrating from SQL Express to SQL Standard instead of the other way around.  

Thanks!
0
Comment
Question by:phonemate
5 Comments
 
LVL 23

Accepted Solution

by:
Nagendra Pratap Singh earned 125 total points
ID: 38859047
There is a management studio for SQL express.

http://www.microsoft.com/en-in/download/details.aspx?id=7593
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 125 total points
ID: 38859096
Once you install the SMSSE provided earlier. or you can use the SSMS on the server from which you are transferring.
the instance name provided you enabled TCP/IP on the express version
machinename\sqlexpress unless you customized the instance.

You could need to install two stored procedures on the existing server
http://support.microsoft.com/kb/918992
These will help you create the sql logins that might be needed on the new express instance to access/tie into this database.

The following information you have to know ahead of time
How active is this database? Can access to it be taken down and for how long?
How can the application that accesses this database be reconfigured to point to the new database? Is it an ODBC DSN, or is access coded/included within the application configuration, registry, etc.

once you have that information, you can disable the application from being accessed when downtime for the app is scheduled. Backup the existing database. Copy the backup to the new database server. Restore the database from backup. adjust the application to point to the new instance.
This usually all that is needed.

Depending on the data within the database, you would need to manually setup backup mechanism.
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 125 total points
ID: 38859186
You do need to check if you have any jobs related to databases. In that case you need to figure out if this can be run in an alternate way. I e windows scheduled task or equivalent.

You should also check if you have any stored procs thats been installed in the master database.

Regards Marten
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 125 total points
ID: 38859850
Also look at transferring logins as well:

How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/918992
0
 

Author Closing Comment

by:phonemate
ID: 38912505
Thank you for the feedback - this was a question on behalf of a customer and he has decided to keep what he has in place.  We did download the Management Studio, but did not get to the point of testing it out.  Thanks again for the fast responses
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Master DB with Masterkey 1 34
Setting up  FC, iSCSI on vSphere 6.0 for storage 1 45
Sql query with where clause 2 34
vSphere Clustering DRS 3 27
Last article we focus in how to VMware: How to create and use VMs TAGs – Part 1 so before follow this article and perform the next tasks, you should read the first article how to create the TAG before using them in Veeam Backup Jobs.
This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Advanced tutorial on how to run the esxtop command to capture a batch file in csv format in order to export the file and use it for performance analysis. He demonstrates how to download the file using a vSphere web client (or vSphere client) and exp…
This Micro Tutorial walks you through using a remote console to access a server and install ESXi 5.1. This example is showing remote access and installation using a Dell server. The hypervisor is the very first component of your virtual infrastructu…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question