Migration From SQL Server 2008 Express to SQL Server Web

Question

I have SQL Server Management Studio (for SQL Server Express R2) on my pc, and I'd like to export a few databases for use by a website that's running on a server with SQL Server Web. However, when I right click one of the databases in management studio and go to Tasks > Export Data ... a window comes up warning me that when migrating to another instance of SQL Server, I should use the Copy Database Wizard. However, I can't find this wizard in management studio. When I right click on the database and go to Tasks, there is no option for Copy Database. I must have done something wrong. Has anyone experienced this problem before?

Thanks,

Andrew

Answer 1

Okay, it turns out that there's an Advanced button that contains the option that I was looking for. The steps are as follows:

1. Right-click the database in SQL Server Management Studio.
2. Go to Tasks > Generate Scripts...
3. In the Set Scripting Options step of the wizard, click the button labeled Advanced and set "Types of data to script" equal to "Schema and data".

This will generate a script to recreate the database. Apparently, the Copy Database option in the context menu for right-clicking on the database just doesn't exist in the version of Sql Server Management Studio that I have.

Andrew

Answer 2

The Copy Database wizard isn't included in the Express tools, which is OK because it sucks anyway. The best bet would be to backup the database and restore it to the new server (as long as both servers are running the same major version).