Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy network version of SQL Server DB to Express Edition

Posted on 2011-03-23
14
Medium Priority
?
398 Views
Last Modified: 2012-05-11
How would I copy a network version of my SQL Server DB (SQL Server 2005) to Express Edition on my PC?
0
Comment
Question by:schmir1
[X]
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
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35203479

Take a backup of the database, copy the backup file to you PC
Then run a restore on your PC
0
 

Author Comment

by:schmir1
ID: 35203485
How do I do that?
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 2000 total points
ID: 35203489

Take a backup of the database on the network.
copy the backup file to your local pc
Create a new database in sql server express with the same name
Right click the newly created database, select tasks -> restore database
select to restore from device and then click add file and browse to the backup file.
select options in the left hand pane and select the checkbox to overwrite existing database.
then restore the database.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35203502
to take a backup open SQL Management Studio on the server
right click the database and select tasks, then select backup and specify a location to backup to.
0
 

Author Comment

by:schmir1
ID: 35203591
I get a "Execute permission was denied" error.  Is there any other way to do it?
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35203658
No not really. You get this error when trying to do the backup? You need to make sure you are logged onto the server as an administrator with backup operator permissions for the sql server.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35203825
>>I get a "Execute permission was denied" error.  Is there any other way to do it? <<
Yes.  You need to do a SQL Backup.  You cannot backup the database files.
0
 
LVL 2

Expert Comment

by:CoolBurn28
ID: 35203992
you can copy the .mdf itself and attach it.

step1-ms.bmp
step2-ms.bmp
step3-ms.bmp
0
 

Author Comment

by:schmir1
ID: 35211130
My db is on a network server so I don't have any mdf files that you mentioned.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35212107
>>My db is on a network server so I don't have any mdf files that you mentioned.<<
I see.  So what database files do you have?

If you do not know the answer to that question, than do the following from a SSMS query window:
SELECT physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'YourDatabaseNameGoesHere'

This will show you all the database files for a particular database

If you get a permissions error from doing that, contact your DBA.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35212132
Why would you copy the mdf files when you can just backup the database and then restore it locally.
It is unlikely that if you do not have permissions to backup the database you will have the rights to query the master database.
The suggestion of copying the mdf files without first detaching the database just seems wrong to me, maybe I'm just too careful.
I think it has been established from all the above expert comments that you really need to seek help from whoever looks after you network sql server and get them to provide you with a backup of the database or a copy of the mdf files.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35212187
>>Why would you copy the mdf files when you can just backup the database and then restore it locally.<<
I have tried, unfortunately as you can see not very successfully.

>>The suggestion of copying the mdf files without first detaching the database just seems wrong to me<<
Actually more than wrong, impossible, at least to use it successfully.

>>I think it has been established from all the above expert comments that you really need to seek help from whoever looks after you network sql server and get them to provide you with a backup of the database or a copy of the mdf files. <<
Absolutely.  This is not MS Access.
0
 

Author Comment

by:schmir1
ID: 35215635
I've ask the SQL Server people to make a backup then with follow the instructions from Jack as follows:

Take a backup of the database on the network.
copy the backup file to your local pc
Create a new database in sql server express with the same name
Right click the newly created database, select tasks -> restore database
select to restore from device and then click add file and browse to the backup file.
select options in the left hand pane and select the checkbox to overwrite existing database.
then restore the database.
0
 

Author Closing Comment

by:schmir1
ID: 35244627
Thanks.  Had to get a systems guy to give me a copy of the backup but then everything worked as you describe.  Good Answer!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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