Copy a database from SQL Server to SQL Server Express

Since Microsoft's SQL Server doesn't support direct database copy from a full SQL Server version to their Express version (why?  who knows, perhaps they felt it would drive sales?) you can't use their GUI tools to copy a database.  You also can't import into Express from an SQL Server Export.

But I did find a way to do it manually.

Step 1: in MS SQL Server Management Studio, right click on the database you want to copy and choose Tasks-->Generate Scripts...

Step 2: Work through the wizard, but if you want to copy the data, take care to click the "Advanced" button on the "Set Scripting Options" step and find "Types of data to script" and choose "Schema and Data".  Finish the wizard and if all goes well it will save an SQL script you can use to reconstitute the database on your Express instance.

Step 3: Copy that sql script somewhere that you can access from the target computer running SQL Server Express.

Step 4: Using MS SQL Management Studio on your target computer, create the database you want to copy the schema and data into.  Create a user with ownership or at least create permissions.  Now, if the database you are copying is small, you can open the file in a query window in Management Studio and run it.  If your file is large, however, that won't work.

Step 5: Once step 4 is complete, then you can use a command line tool to run the sql script.  You will probably need to open the file in something like Notepad++ and tweak the initial USE command to use the database you have created.  You may also need to tweak other things.  Then you can run:

sqlcmd -S .\SQLEXPRESS -U myuser -P SECRETPASSWORD -i My_File_Export.sql

Good luck!

ken.