How can I migrate a SQL Server 2005 database to SQL Server 2008?

Question

I am trying to migrate my one SQL Server 2005 database to SQL Server 2008, with various option, like Script Database.

I am able to create table structure but without any objects like data, functions, stored procedure....

How can I do this? I tried to copy .mdf and .ldf file and put them into 2008 folder, but this is also not working.

Answer 1

I was getting this error:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘database_name’ database. (Microsoft.SqlServer.Smo) 

This can be solved by selecting option REPLACE... That's how I solve this one.

Answer 2

If you don't have the old SQL Server 2005 installation running anymore then you will have to use Attach from the SSMS 2008. So copy the .mdf ja .ldf to SQL Server 2008 data folder and under SSMS -> Databases -> Right click -> Attach

If you do have the old installation running the the only right way is already told by marc_s

Answer 3

Backup & restore ?!?!?

Backup your SQL Server 2005 database to a .bak file, and restore that on your SQL Server 2008 machine.