HOW TO MOVE A SQL 2005 EXPRESS DATABASE TO A WEB HOST
This is a monstrously difficult procedure for something that should be so simple. Here’s how:
1. In SQL 2005 Express, create a SQL 2000 script that generates the database with all Table /View Options set to true.
2. In SQL 2000, create a empty new database.
3. Modify the script. In our case, we added:
exec sp_fulltext_database @action='Enable'
exec sp_fulltext_catalog @ftcat='ABCFullText', @action='CREATE'
Above these statements:
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[Site_Search]', @action=N'create', @keyname=N'PK_Site_Search', @ftcat=N'ABCFullText'
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[Site_Search]', @colname=N'Text', @action=N'add'
And we removed this statement at the top of the file:
SE [1300PhoneWords]
4. Run the modified script on the SQL 2000 database. This should execute with no errors. If it doesn’t, delete the database, recreate it, modify the script and try again. The script must execute with no errors.
5. In SQL 2000, script this database with CREATE ticked and all Table Scripting Options in the third tab ticked. Copy this to an editor.
6. You will notice that SQL 2000, unlike SQL 2005 generates the script with the table creation first and indexes and constraints generated after all table create statements.
7. Copy the table creation statements in the first half of the script and apply them to the database you want to transfer data into. This should successfully execute – you have now transferred all the table structures
8. Install SQL 2005 evaluation version – this is the full version of the SQL 2005, not express and is only 1GB to download. But only install the components necessary for data transfer. I think these were called the Legacy Components.
9. Run DTSWizard in:
10. Use the Wizard to copy all tables to the new database. This should work with no errors.
11. Copy the Index and Foreign Key scripts to a new query window for the database you are transferring to. Move any full text creation scripts to the end of this screen in case they don’t work – you don’t want the system to crash half way through and not be sure what indexes/constraints were created.
12. Execute this script. It should run with no errors.