• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1508
  • Last Modified:

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)"
0
Declan_Basile
Asked:
Declan_Basile
1 Solution
 
David ToddSenior DBACommented:
Hi,

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.

Regards
  David
0
 
AskeetoCommented:
I think Backup/Restore is not supported on SQL Server Express (http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx)
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' )
 FOR ATTACH ;
GO

0
 
David ToddSenior DBACommented:
Hi,

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.

Regards
  David
0
 
Declan_BasileAuthor 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.
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now