How can restore a backup on lower version of SQL Server:

How can restore a backup on lower version of SQL Server:

We have SQL command to find out SQL version.

SELECT @@VERSION  by this commant we can find out our SQL version.

Out Put:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Meaning of 4 digit output - 10.50.1600.1

1^st digit product version (8 means, SQL 2000, 9 means SQL 2005, 10 means SQL 2008, 11 means SQL 2012).

2^nd digit product level - different releases have different numbers. 10.00 is SQL 2008, 10.50 is SQL 2008 R2.

3^rd digit - Service Pack to you SQL Server installation.

4^th digit - one is a build number

Three cases we will face when restore SQL database one version to other version:

Source server is an older version and target server is a newer version of SQL Server.

For example:

Source: 9.00.2234.0 (SQL 2005)

Target: 10.00.5500.0 (SQL 2008 SP2)

Backups are compatible. You can restore a database backup on a newer version of SQL Server.

Source server and target server are the same product version and product level. They may differ on 3^rd  or 4^th  digit  in the version number.

For example:

Source server is 10.50.2876.0 and target is 10.50.2799.0.

Backups are compatible. Microsoft does not introduce significant changes in BACKUP/RESTORE functionality that breaks backward compatibility by a hotfix or Service Pack.

Source server and target server differ by 1^st  or 2^nd  digit - a product version or a product level and the target is older than the source.

For example:

Source: 11.00.2100 (SQL 2012)

Target: 10.00.5500 (SQL 2008 SP2)

Backups are incompatible.