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?
ASKER
How do I do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
right click the database and select tasks, then select backup and specify a location to backup to.
ASKER
I get a "Execute permission was denied" error. Is there any other way to do it?
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.
>>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.
Yes. You need to do a SQL Backup. You cannot backup the database files.
ASKER
My db is on a network server so I don't have any mdf files that you mentioned.
>>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.
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.
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.
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.
>>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.
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.
ASKER
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.
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.
ASKER
Thanks. Had to get a systems guy to give me a copy of the backup but then everything worked as you describe. Good Answer!!!
Take a backup of the database, copy the backup file to you PC
Then run a restore on your PC