Move database from SQLS2k5 to SQLS2k5Express

Hello,

1.) Can I move a database complete with tables, diagrams, relationships, stored procedures, etc. from SQL Server 2005 to SQL Server 2005 Express and preserve all the work I did?

2.) Can I do the same from SQL Server 2000 to SQL Server 2005 Express and preserve all the work I did?

Thank you,

dbuchanan

Hi, there should be no problem with either of these scenarios, the only thing to be careful of are the restrictions in SQL Server Express.

Most restrictions are around size and performance, ie 4GB database size, 1 CPU, 1GB Buffer Cache, but there are also some others for instance, SQL Server Express does NOT support SSIS (DTS in SQL Server 2000), so if you are using these features of SQL Server Express, it would not be a simple move.

For more information on SQL Server Express features, see the books online http://msdn2.microsoft.com/en-us/ms165636(SQL.90).aspx

|||As Scott says this is very doable. You can use detach/attach functionality or the Copy Database Wizard for this pretty easily.|||

That isn't actually true, at all.

I've been fighting with SQL express for 3 days now to try and get it to attach a database correctly.

Firstly it will not attach it as a System Database, it creates a new instance for it, which while weird I understand.

Then when any script connects its throws this error :

Could not locate entry in sysdatabases for database 'attacheddatabase'. No entry found with that name. Make sure that the name is entered correctly. (severity 16)

Unless you connect to the system databases then it allows you to select the databases, though your not allowed to copy any tables, or move any databases to that area ........

Oh how I pray for a actual developer focused database from MS, i.e. one that has permissions that allow you do to what you want opposed to hindering you every step of the way ........

I'm fighting tooth and nail to get this whole project moved to a decent database now.

|||

Jemue, what connection string are you using to connect to your databse?

I have used SQL Express extensively, and never had any real problems connecting to database, but the user instance feature can get a bit confusing at times. Maybe if you give me a bit more detail about what you're trying to achieve, what technologies you are using, we may be able to help.

Also keep in mind that you don't have to use User instances, you can just connect to a database in exactly the same way you do in any other SKU of SQL Server.

Cheers

|||

Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.