A couple of days back, a friend of mine called me and asked for the help. He is a developer, mainly working in Oracle and Oracle form. He wanted to generate an insert statement script for his database along with the schema, but he is unaware and unsure if there is such option available with SQL Server. I told him that it is available for sure, and the best part is, it is an inbuilt feature.
The case was like, one of his clients gave him an assignment where he has to develop an application. once he is done with the thing, he wanted to do the test run of his application at the client end and it failed. I have asked him what is the error that is reported to him? He said it reads like:
The database was backed up on a server running version 12.0.2000.8 That version is incompatible with this server, which is running version 9.0.1399.06.
After reading the error message it turned out that he is restoring a database to an incompatible version, meaning restoring a database to lower version of the SQL Server that the database was created on. He asked me if I have any solution to this problem, and I said Yes! Microsoft has provided a feature within the SQL Server Management studio itself. He asked me again, but I don’t know how to get it work. I told him, let’s run a wizard to Generate and Publish Scripts and it will help you generate an insert statement script for your database. Here is how it works:
1: Right-click on the database you want to generate a script for, select task, select Generate script and a wizard window will pop-up that looks like below
2: Click Next, select an entire database or select the objects you want to generate a script for
3: Provide details on where and how you want to save your script, Click on advance
4: In the General section select the target version
5: Here sect the Type of the script, in my friend’s case he has selected Schema and Data
This is how he was able to generate an insert statement script for his database along with the schema and was able to restore his data from the higher version of SQL Server to lower version of SQL Server. There are two more things that I have suggested him – 1) He should always try to convince the client to use the latest version available, but in this case, there are some legacy applications so he ruled out this option for now. 2) The version his client is running is RTM version, I suggest him to ask his client to update the SQL Server to most recent service pack level.
Here are some more interesting troubleshooting tips that you may want to explore
