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.Sql
If the database doesn't exist:
"System.Data.SqlClient.Sql
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.Sql
If the database doesn't exist:
"System.Data.SqlClient.Sql
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\
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
FOR ATTACH ;
GO
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\
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
FOR ATTACH ;
GO
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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