Sql Server 2008 to R2 Restore Backup from R2

Question

I need to restore SQL Server 2008 R2 Backup to SQL Server 2008, but as you know, it's not possible. So, I decided to upgrade my SQL 2008 to R2 but I don't know which instance to select in Instance to upgrade that contains ("MSSQLSERVER","SQLEXPRESS","<<Upgrade shared features only>>"

Answer 1

Most likely the first one "MSSQLSERVER". Thats the default name for a new installation. The express one is the free version and limits your DB size to 10GB. The 3rd one isn't an instance, just shared components so ignore that one.

You should verify by logging into the server from SSMS and typing in SELECT @@SERVERNAME first to verify. If it only returns the server name, then the MSSQLSERVER is the right instance. If it returns ServerName\Express then you're on the express edition.

Answer 2

There is a way to get the data from a SQL Server 2008 R2 instance to SQL Server 2008/2005 (I had to do this a few weeks ago).

1. Generate a script for the SQL Server R2 database, setting the "Script for Server Version" to SQL Server 2008. (right click on the database, Tasks/Generate Scripts and then when saving to a file, click on the Advanced button).

2. Run the script on the destination server (you may encounter errors relating to user accounts that you might need to fix up).

3. Disable all foreign keys on the newly created database by using "EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"".

4. Using SQL Server Import/Export wizard, you can export the data from the 2008 R2 instance to the 2008 instance.

5. Enable all foreign keys on the new database by using "EXEC sp_msforeachtale "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all""

6. Check that the newly restored database works.

I did have a few issues getting the data to go across properly at first, but in the end, I did have a working database and the application that used it could use it properly. It IS a long way of doing things, but if you find yourself in a bind, this is a way out.

This question was helpful when I was working through the same issue you have: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql