Copying a database in SQL Server 2005

Posted on 2007-07-23
Last Modified: 2011-04-14
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)"
Question by:Declan_Basile
    LVL 35

    Expert Comment

    by:David Todd

    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.

    LVL 3

    Expert Comment

    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' )

    LVL 35

    Expert Comment

    by:David Todd

    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.

    LVL 1

    Author Comment

    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.
    LVL 1

    Accepted Solution

    Closed, 500 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now