Copying a database in SQL Server 2005

We have an enterprise edition trial version of SQL Server 2005 installed on our file server, and SQL Server Express installed on a workstation.  What is the best way to copy a database from the enterprise edition installed on the file server to the SQL Server Express installed on the workstation?
    The "Copy Database" option isn't available in the Express Edition, when I backup the database to a file and try to restore the database I get the error messages:

If the database already exists:
"System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Northwind' database."

If the database doesn't exist:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Northwind.mdf'. (Microsoft.SqlServer.Express.Smo)"
Who is Participating?
Vee_ModConnect With a Mentor Commented:
Closed, 500 points refunded.
Community Support Moderator
David ToddSenior DBACommented:

In either case, on the options page, there is a box - overwrite existing. And do check that the proposed path and filenames for the restored database are appropriate.

I think Backup/Restore is not supported on SQL Server Express (
But you should be able to attach databases to SQL Server Express. (so don't take a backup, but just copy the mdf and ldf file from your source database.)

CREATE DATABASE [database_name] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<dbname>.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<dbname>.ldf' )

David ToddSenior DBACommented:

I do agree that it appears on the surface that SQL Express can't backup.

But I don't think that that is what it is saying - the category is Enterprise Management Tools, and SQL Express at one time didn't have any.

I have just installed SQL Express, and it can backup. (I connected to it from SSMS from my Developer Edition install.

Declan_BasileITAuthor Commented:
Askeeto, thanks for the comment.  I did think of that but SQL Server won't let me copy the mdf while it's in use.
dtodd, I didn't find that setting on the options page.
My solution, which I found by searching the error message and reading another person's posting who had the same problem, was to use the configuration utility to set the SQLEXPRESS service to log in as "Local System" instead of "Network Service".  After doing this I was able to restore from a backup file to a database the didn't exist.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.