jump to navigation

Restoring a SQL Server database to an older version of Sql Server June 20, 2012

Posted by fofo in Sql Server, SQL Server 2008.
Tags: ,
trackback

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!!!

Comments»

1. Dot Net Rules : Restoring a SQL Server database to an older version of Sql Server - June 20, 2012

[…] there was no problem with that.I had a different opinion.Ι know that this is not possible. (read more) Share Posted: Τετάρτη, 20 Ιουνίου 2012 6:41 μμ από το μέλος […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: