SQL Server database backup restore on lower version

**Will not necessarily work**

* **Backup / Restore** - will not work when the target is an earlier MS SQL version.

* **Copy Database** - will not work when the target is SQL Server Express: ["The destination server cannot be a SQL Server 2005 or later Express instance."][1]

* **Data import** - Will not copy the schema.

**Will work**

* **Script generation** - *Tasks* -> *Generate Scripts*. Make sure you set the desired target SQL Server version on the *Set Scripting Options* -> *Advanced* page. You can also choose there whether to copy schema, data, or both. Note that in the generated script, you may need to change the DATA folder for the mdf/ldf files if moving from non-express to express or vice versa.

* **Microsoft SQL Server Database Publishing Services** - comes with SQL Server 2005 and above, I think. Download the latest version from [here][2]. Prerequisites: `sqlncli.msi`/`sqlncli_x64.msi`/`sqlncli_ia64.msi`, `SQLServer2005_XMO.msi`/`SQLServer2005_XMO_x64.msi`/`SQLServer2005_XMO_ia64.msi` (download [here][3]).


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

You'd have to use the Import/Export wizards in SSMS to migrate everything

There is no "downgrade" possible using backup/restore or detach/attach. Therefore what you have to do is:

1. Backup the database from the server running the new SSMS/SQL version.
2. Import data from the generated .bak file, by expanding the "Tasks" menu(after right-clicking the target database) and selecting the "Import Data" option.

Go to *Task->Generate Scripts*...

In *Advanced* in *"Types of data for script"* select `"Schema and data"` and try to run this script in your lower version.

You can generate script from Task menu

For detailed reference

[How to migrate a SQL Server database to a lower version][1]


[1]:

[To see links please register here]