How to export SQL data script for SQL Express 2008 R2
Before deployment of new version of my application I wanted to make sure that everything is alright, by doing development test on my machine. I backed up production database and copied it my local environment. Task was easy, restore database from backup and run all unit/integration tests, run the smoke test of application manually. But as I started to restore database I got such error from my SQL Express Management Studio:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'LOCAL\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)
Basically it meant that my production and local environment are different. On production I have SQL Express 2008 R2, locally I have SQL Express 2008. It was a surprise to me, cause my WebPI shows me that I’m upgraded up to R2.
Anyway, I had no time to upgrade my local environment and I did very simple workaround that might work for you as you don’t have very big database to handle.
There is a feature of SQL Express (and Server) that allows you to export your database as SQL script and you can run this script on local SQL instance and got exactly same version.
It is easy taks, but by doing that initially I successfully exported schema only, but not data itself. So, script generated didn’t contain and INSERT statement. As it turns out you have to change some default options, to export both schema and data. Follow the instructions:
-
Expand your database list and right click on target database.
-
Select “Tasks” -> “Generate Scripts” context menu item. Generate scripts wizard will open.
-
Press “Next” on welcome screen and select your database from databases list.
-
Make sure you checked option “Script all object in selected database” if you want to script all db. In case you need only some particular table you might consider to select it later. Click “Next”.
-
On Script option step, make sure to select “Script Data” option (this is exactly what I missed and didn’t understand why data is not present in final script).
-
Proceed to the end of wizard to complete it.
At the end you will got SQL script with schema and data that you can run on SQL Express 2008 and got exactly the same database as on R2.
This helped me quickly to workaround “version incompatibility” issue, hope it might help you as well.
Before deployment of new version of my application I wanted to make sure that everything is alright, by doing development test on my machine. I backed up production database and copied it my local environment. Task was easy, restore database from backup and run all unit/integration tests, run the smoke test of application manually. But as I started to restore database I got such error from my SQL Express Management Studio:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'LOCAL\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)
Basically it meant that my production and local environment are different. On production I have SQL Express 2008 R2, locally I have SQL Express 2008. It was a surprise to me, cause my WebPI shows me that I’m upgraded up to R2.
Anyway, I had no time to upgrade my local environment and I did very simple workaround that might work for you as you don’t have very big database to handle.
There is a feature of SQL Express (and Server) that allows you to export your database as SQL script and you can run this script on local SQL instance and got exactly same version.
It is easy taks, but by doing that initially I successfully exported schema only, but not data itself. So, script generated didn’t contain and INSERT statement. As it turns out you have to change some default options, to export both schema and data. Follow the instructions:
- Expand your database list and right click on target database.
- Select “Tasks” -> “Generate Scripts” context menu item. Generate scripts wizard will open.
-
Press “Next” on welcome screen and select your database from databases list.
- Make sure you checked option “Script all object in selected database” if you want to script all db. In case you need only some particular table you might consider to select it later. Click “Next”.
-
On Script option step, make sure to select “Script Data” option (this is exactly what I missed and didn’t understand why data is not present in final script).
- Proceed to the end of wizard to complete it.
At the end you will got SQL script with schema and data that you can run on SQL Express 2008 and got exactly the same database as on R2.
This helped me quickly to workaround “version incompatibility” issue, hope it might help you as well.