How to migrate a database from SQL Server 2005 to 2008 as is?

Question

I want to migrate a database completely or partially. Right now, I will do a complete database. Partial will be posted as a separate question.

Strategies I am considering:

1. copy database wizard
2. convert 2005 database to script. Run script on 2008
3. simple SQL query

My question - I want to use method 2. Is it even possible to do this? If yes, how to do it? Any limitations/risks ?

NOTE - The source server is a SQL Server 2005 database with one IP. Destination is a SQL Server 2008 instance with another IP.

I don't know if you need to be sysadmin to do this. I am not even sure if I am sysadmin. If yes, then how to check if I am a sysadmin ?

Answer 1

Most simple way to do this is to restore backup or copy MDF and LDF files to new server. If your servers are in the same network you can do this by creating shared folders on the second server and copying files there.

If that is not an option for any reason then you can zip the backup (make sure to add strong password) upload it to some online storage and then download it from second server.

Final option is to use third party comparison and synchronization tools from RedGage or ApexSQL (there are a lot of these on the market and they all have fully functional trials)

Answer 2

As Pondlife said, Just backup the database at SQL 2005, restore as a new database at SQL 2008. You change the compatible mode to SQL 2005 which is version 9, or you can leave it to 2008 which is version 10 or 10.5. I think you want to keep the database as is, so you could set the compatible more to 9.0.xxxx and you all be done.

As usual grant access to users, create them as new if you have to, or migrate them from SQL 2005.

Generating script from 2005 and running on 2008 is a long route. There may be some possibilities for errors and TSQL compatibility.

Hope it helps !!

Answer 3

My question - I want to use method (2). Is it even possible to do this ? (...) any limitations/risks ?

Option 2 could be a problem if database is too large. Worked for me with up to 2 GB databases.

any limitations/risks ?

You may need to increase SQL's buffer and/or run the script through command line, since a large script in SQL Manager eats up plenty of memory.

If yes, how to do it ?

To generate the script, simply right click and choose generate script. Select both data and model for whole database, and choose appropriate options for others.

Answer 4

Just RESTORE it on the SQL2008 server and it will be automatically upgraded. And you can check if you're a sysadmin using IS_SRVROLEMEMBER.