CODEWITHSUNDEEP
sql-server-2008sql-server-2012ssms
vishal shah
vishal shah

Reputation: 316

Will MSSQL 2012 database work on MSSQL 2008 server?

I have MsSQL database which is created in MSSQL 2012 SQL server.

On my local machine I have installed MSSQL 2008 SQL server.

Will the 2012 database work in MSSQL 2008?

Also,I have installed MSSQL 2012 SQL server on my local system.Do I need to install MSSQL management studio for 2012 SQL server?

Is it required for user interface?

Upvotes: 0

Views: 191

Answers (7)

Gökhan Açıkgöz
Gökhan Açıkgöz

Reputation: 1

There is no backward compatibility between SQL Server versions. MSSQL Server 2012 contains different data structures compared to MSSQL Server 2008.Therefore, even if you restore from a backup to 2008, it may not work stably.

I recommend exporting your data from 2012 using SSMS and then importing that data into 2008,

or

exporting all your objects from 2012 using a T-SQL script, making some adjustments, and then executing that script in 2008.

Upvotes: 0

Scott
Scott

Reputation: 180

Just reiterating PingPongOng - a backup and restore wont work as you can't install a newer database onto an older server. You can however go the other way, so a 2008 database could migrate to a 2012 server.

As others have said, you need to create scripts. Right click on the 2012 database, and on the menu that appears -> Tasks -> Generate Scripts and follow the wizard that appears. This will allow you to create just the schema or schema and data (if required).

Upvotes: 0

Bavithra Mohan
Bavithra Mohan

Reputation: 21

it is not neccesarily required to have ssms 2012 . it will work as we have the sql server installed

Upvotes: 0

PingPongOng
PingPongOng

Reputation: 96

A full database backup and restore to the new server will not work. SQL server does not allow that to a previous version.

You can however create the schema and load the data in manually through SSIS or BCP.

For SSMS you can connect to a previous version.

Upvotes: 1

Dave Poole
Dave Poole

Reputation: 1068

Whether a 2012 database will work on a 2008R2 server depends. If you are deploying to your 2008R2 server by running T-SQL scripts then it will only work if

  • The database objects used in your 2012 database were available in 2008R2
  • You are not using SQL2012 T-SQL enhancements in your scripts
  • Your scripts do not depend on system objects or views that were introduced in SQL2012
  • The local edition of SQL2008R2 is compatible with the SQL2012 edition. For example SQL2008R2 Express will not like SQL2012 Enterprise Edition features.

Ideally you will be using the Developer Edition for your local instance as this is simply a throttled version of Enterprise Edition.

If you are trying to restore to 2008R2 from a 2012 backup then no, it won't work.

Given that SQL Management Studio is a free download why would you stick with the SQL2008R2 version? If you have licenced SQL2012 then you are licenced for the SQL2012 version of SSMS. Why not go the whole hog and install SSMS for SQL2017?

If you do not like SMSS then a paid for alternative might be Aquafold Data Studio. This has the advantage of supporting many database platforms from a single IDE.

Upvotes: 0

SharK
SharK

Reputation: 2215

You don't need to use Management Studio of SQL Server 2012 in order to browse SSMS-2012 database. You can still continue to use the 2008 R2's IDE.

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

Most of the features will work, But features specific to SQL Server 2012 may not work.

Upvotes: 0

Related Questions