Solved

Copy network version of SQL Server DB to Express Edition

Posted on 2011-03-23
14
394 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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