If we try to restore a database on an SQL Server 2008R2 or a
lower version from the database backup which was performed on an SQL Server 2012
instance then it will fail.
We have two database instances here and both instances are in same Domain, one of them is SQL 2012
and another one is SQL 2008R2:
1. SQL 2012 database instance: XYZ\SQL2K12.
2. SQL 2008 R2 database instance: ABC\SQL2K8.
Now, I backed up the database on XYZ\SQL2K12 server and
copied the backup file over ABC\SQL2K8 server for restoration purpose.
Below error Message
received when restoring SQL Server 2012 database backup over an SQL Server 2008
R2 or lower versions.
The reason for the
failure is the internal version number of SQL Server 2012 is different from
that of SQL Server 2008 R2 or lower versions. Since the internal version number
of SQL Server 2012 is higher than that of SQL Server 2008 R2 database the
restore process fails.
Note: - we will not be able to restore a database backup from a higher
version of SQL Server to a lower version of SQL Server. Downgrade of a database
is not supported using the database backup and restore method.
Process to Downgrade SQL Server Database:
1. Connect to SQL
Server Database Instance Using SQL Server Management Studio and then expand
Databases Node.
2. Right click the database
on SQL 2012 Instance, expand Tasks and choose Export Data option from the drop
down menu to open up SQL Server Import and Export Wizard as shown in the image
below.
3. In SQL Server
Import and Export Wizard click Next as shown in the image below to continue
with the wizard.
4. In Choose a Data
Source wizard screen, you need to specify the Data Source as SQL Server Native
Client 11.0 and provide the Source Server Name, Authentication information and
also choose the Source Database from the drop down list as shown in the image
below.
Click Next to continue with the wizard
5. In Choose a Destination wizard screen, you
need to specify the Destination as SQL Server Native Client 11.0 and provide
the Destination Server Name, Authentication information and also you need to
specify the Destination Database. As shown in the image below click on "New" button
under database to create a Destination Database
6. In "Create Database" Window prompt you need to specify the Destination Database Name, Initial Size
for Data and Log files, Growth Parameters for Data and Log files as shown in
the image below.
Once you click "OK" button you
will return to choose a Destination wizard screen.
Click Next to continue with
the wizard.
7. In “Specify Table
Copy or Query wizard” screen you need to choose the first option which is "Copy
data from one or more tables or views" as shown in the image below.
Click Next to continue
with the wizard.
8. In Select Source
Table and Views wizard screen, as shown in the below image choose all the
tables.
Click Next to continue with the wizard.
9.
In “Save and Run Package wizard” screen you will be given an option to either
run the package immediately or else to Save SSIS Package on to SQL Server or to
a File System.
Choose "Run Immediately" option and click Next to continue with the wizard.
10. In Complete the
Wizard screen you will be able to see a quick summary of options which you have
selected so far.
Click Finish to start the SSIS package
execution to downgrade the SQL Server database from higher version to lower
version.
11.
In the image below you could see that the SSIS package executed successfully
and it transferred the data from SQL Server 2012 to SQL Server 2008 R2.
12.
In the image below you could see that using SQL Server Import and Export wizard
we were able to successfully downgrade the database from SQL Server 2012 to SQL
Server 2008 R2.
Conclusion
In
this article you have seen how to downgrade an SQL Server 2012 database to SQL
Server 2008 R2 database. The steps mentioned in this article are also
applicable to downgrade a SQL Server 2008 R2 database to SQL Server 2005 or SQL
Server 2000.
0 comments: