Solved

Copy network version of SQL Server DB to Express Edition

Posted on 2011-03-23
14
382 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
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 32

Expert Comment

by:ewangoya
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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