Restoring a SQL Server database to an older version of Sql Server June 20, 2012Posted by fofo in Sql Server, SQL Server 2008.
Tags: backup, restore
In this post I would like to talk about a common issue that confuses SQL Server DBAs and developers.Recently I had to deploy an ASP.Net web site from the development server to the live production server.The database server on the deployment server was SQL Server 2008 R2.The hosting provider could only provide us with a SQL Server 2008 database server.Most of my colleagues thought there was no problem with that.I had a different opinion.Ι know that this is not possible.
You cannot take a backup of a SQL Server 2008R2 database and try to restore it to a SQL Server 2008 server.Similarly you cannot take a backup of a SQL Server 2012 database and restore it to a SQL Server 2008 R2 server.
You probably think that this is some sort of a bug.It is not.Each database has a physical number.SQL Server can understand a minimum and a maximum database physical version number.
I will create a short hands-on example to demonstrate that.I have an SQL Server express 2008 edition instance running on my machine.I also have SQL Server 2008 R2 Developer edition.
I will connect to this instance of SQL Server 2008 Developer edition and in a new query window I will type
USE master; GO IF DATABASEPROPERTYEX ('MyDB', 'Version') >; 0 DROP DATABASE MyDB; GO -- Create database CREATE DATABASE MyDB ON (NAME = MyDB_Data, FILENAME = N'C:\SqlData\MyDB_data.mdf') LOG ON (NAME = MyDB_Log, FILENAME = N'C:\SqlData\MyDB_log.ldf'); GO -- Take full database backup BACKUP DATABASE MyDB TO DISK = N'C:\SqlData\MyDB.bak' WITH INIT; GO
I have created a database and have taken a full database backup.
I am going to connect to the SQL Server 2008 Express edition running in my machine.I will connect to this instance and try to restore the backup.
USE master; GO RESTORE DATABASE MyDB2 FROM DISK = N'C:\SqlData\MyDB.bak' WITH MOVE N'MyDB_Data' TO N'C:\SqlData\MyDB_data2.mdf', MOVE N'MyDB_Log' TO N'C:\SqlData\MyDB_log2.ldf', REPLACE, STATS = 10; GO
I will receive an error after executing the query above
Have a look at the picture below
The only way I could solve my problem was to use another functionality through SSMS-Generate Scripts.I selected the MyDB database and then right-clicked on Tasks—>;Generate Scripts. The wizard pops up.I clicked Next on the first screen.In the next screen I selected “Script entire database and all database objects” .Then I clicked Next.I chose to save the script to a file and not to clipboard or query window. Then I clicked the Advanced button. I had to select a few options. Have a look at the picture below.
In your case you would probably need to select different options…Then I clicked OK and then Next.Then I clicked Next once more, the script was generated and then I closed the wizard by clicking Finish.Then I just had to run the new script in the SQL Server 2008 R2 developer edition.My database was finally” restored” in the production server.
Finally I want to mention that compatibility level has nothing to do with trying to restore a backup to an earlier version of SQL Server.If you set the compatibility level of a database to e.g compatibility level 90 or 100 that does not mean that an SQL Server 2012 database backup can be restored on a SQL Server 2008 database server.
Hope it helps!!!