CODEWITHSUNDEEP
sql-server-2008-r2sql-server-2012
Randeep Singh
Randeep Singh

Reputation: 1018

Can't restore compatible database back to SQL Server 2008 R2

We have client with SQL Server 2008 R2 and database created on same server. So the compatibility is set as 10.

We're running mostly on SQL 2012. In event we backup database from 2008 and restore in 2012, make some data changes (no schema change), and then back it up again, we can't restore back to 2008.

It would prompt "No backupset selected to be restored". But seems that the compatibility is retained. Opening Management Studio as Administrator doesn't work either.

This method use to work fine with one previous client, but they have upgraded to 2012 now.

Upvotes: 0

Views: 404

Answers (1)

Mahavirsinh Padhiyar
Mahavirsinh Padhiyar

Reputation: 1179

You are having a compatibility issue.

Just set compatibility mode and try to restore database again.

ALTER DATABASE Databasename SET COMPATIBILITY_LEVEL = 80

You have to set COMPATIBILITY_LEVEL as per your database is suppose if backup database COMPATIBILITY_LEVEL is 90 than set COMPATIBILITY_LEVEL = 90.

Now restore database and if database restored successfully than again set previous COMPATIBILITY_LEVEL to the level it was before restore.

Upvotes: 1

Related Questions