Solved

Copy network version of SQL Server DB to Express Edition

Posted on 2011-03-23
14
391 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 500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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