How to downgrade the MS SQL 2012 DB to 2008 R2

[former_member290907]

Hi Folks,

We have upgraded MS SQL 2008 R2 SP1 database to 2012 and during post-upgrade activity (executed with Databases tool in SWPM) we came across to an error that "Minimum SP11 BASIS component support package is required". Our system is presently on SAP NW 7.0 EHP1 SP08. Hence now I am stuck in situation where I can not proceed further without downgrading the database to 2008. Also BASIS component support package upgrade is not possible because we are getting "Database Consistency" ABAP dump (attached screenshot for your reference) while executing SICK t-code

Please help me to guide the steps to downgrade the MS SQL database to 2008 R2 or if it is feasible then we can install another SQL instance on same machine and connect SAP application to new instance.

Thanks

[clas_hortien · Accepted solution]

Hello,

there is no SQL Server way to downgrade a SQL Server 2012 DB to a SQL Server 2008 (R2) DB. For an SAP DB you might do an export/import and unload/reload the data with r3load. But this is a lengthly process.

The best way is to restore the SQL Server 2008 DB from a backup.

Best regards

   Clas Hortien

[former_member290907 · Accepted solution]

Thanks Clas. As far I know we cannot restore 2008 R2 backup on 2012 DB. If it is feasible can you please guide me through steps.

Regards.

[clas_hortien · Accepted solution]

You are right, this is not possible. I meant you have to use the backup that you have created before you did the SQL Server upgrade (you did one, right ?) and restore it to an SQL Server 2008 R2 instance.

Regards

Clas

[former_member290907 · Accepted solution]

Thanks clas for clarifying. I just have one MS SQL instance which is pertaining to 2012 version. To restore 2008 backup should I install 2008 R2 instance? After that one machine will contain two MSSQL instance (2012 & 2008 R2).

Regards.

[clas_hortien · Accepted solution]

yes, that is a possible way. Just install a SQL Server 2008 R2 named instance side-by-side to the existing SQL Server 2012 instance and restore the DB there. You then have to change the SAP profile to point to this new SQL Server instance. The parameter for this is

dbs/mss/server=<servername>\<instancename>

e.g.

dbs/mss/server=prdserver\prd

Regards

Clas

[former_member290907 · Accepted solution]

Thank clas for confirming. Please help me to understand one more thing. Can the new installation impact my existing instance though I will name it different while installing. Actually I have not done side by side installation for MS SQL so can you please guide me what option should I select for such installation.

Regards

[clas_hortien · Accepted solution]

It depends on the installation you are using. If you use the DVD from SAP with the sql4sap script it will recommend to install a named instance anyway. If you are using a regular SQL Server DVD and run the setup.exe you will have to specify a named instance on this box, as the default instance already exists. SAP recommends to install the named instance with the same name as the SID of the system. So when you have an PRD system you have to specify PRD as the named instance name. The complete SQL Server servername is then <machinename>\PRD.

The new instance will not interfere with the existing one, a restart of the server might be necessary during installation.

Regards

Clas

[former_member290907 · Accepted solution]

Thanks Clas. I will ad the additional 2008 R2 instance. But before that should I uninstall the existing old 2008 R2 from control panel. This 2088 R2 was upgraded to 2012.

Please refer screenshot for reference.

Regards.

[clas_hortien · Accepted solution]

It seems as if you have two working instances already, one 2008 R2 and one 2012. Do you have multiple SQL Server Services as well ?

Regards

Clas

[former_member290907 · Accepted solution]

Thank you for all your help.