Copy network version of SQL Server DB to Express Edition

How would I copy a network version of my SQL Server DB (SQL Server 2005) to Express Edition on my PC?
schmir1Asked:
Who is Participating?
 
Paul JacksonConnect With a Mentor Software EngineerCommented:

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
 
Ephraim WangoyaCommented:

Take a backup of the database, copy the backup file to you PC
Then run a restore on your PC
0
 
schmir1Author Commented:
How do I do that?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Paul JacksonSoftware EngineerCommented:
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
 
schmir1Author Commented:
I get a "Execute permission was denied" error.  Is there any other way to do it?
0
 
Paul JacksonSoftware EngineerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
CoolBurn28Commented:
you can copy the .mdf itself and attach it.

step1-ms.bmp
step2-ms.bmp
step3-ms.bmp
0
 
schmir1Author Commented:
My db is on a network server so I don't have any mdf files that you mentioned.
0
 
Anthony PerkinsCommented:
>>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
 
Paul JacksonSoftware EngineerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
schmir1Author Commented:
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
 
schmir1Author Commented:
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
All Courses

From novice to tech pro — start learning today.