Sometimes in SQL Server attempting to restore a database on server A (whose backup is taken on server B) fails due to servers A and B using different versions of SQL server. In such a situation restoring database is done by generating database scripts of both schema and data. So in this case we Script Database in server B and then run these scripts on database in server A. This SQL Script contains all the Schema with Data. So we can simply run this SQL Script in Server A database and our original database is recreated.
The below Steps outlines how to Script Database so that it now only has all the Schema but also all the data.
1. Right click on the database and select Tasks > Generate Scripts.
2. You will see Introduction page ‘Generate scripts for database objects’. Simple click ‘Next >’ button at the bottom to continue.
3. You will reach a new page which asks to select the database objects to script. Here we choose the first option ‘Script entire database objects to script’ then click ‘Next >’ button at the bottom.
4. A new page opens where you can specify how to save or publish the scripts. Click the ‘Advanced’ button which opens ‘Advanced Scripting Options’ window.
5. In this window find the option ‘Types of data to script’ and select ‘Schema and data’ for it. Click ‘OK’ button to close the window.
6. Click ‘Next >’ button to continue.
7. In the ‘Review your selections’ (which is the last step) click ‘Next >’ to generate your scripts.
Now you have the Full Database Script which you can run them in any database server for restoring purpose.