How to Script Database With All Data From Microsoft SQL Server Management Studio

Sometimes in SQL Server when 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.

Generating SQL Script For Database with Full Data

The below Steps outlines how to Script Database so that it not 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 an Introduction page – Generate scripts for database objects. Simple click Next > to continue.
• 3. You will reach a new page which asks to select the database objects to script. Here you have to choose the first option Script entire database objects to script then click Next > button at the bottom.
  CRUD operations in SQL Server is the first most tutorial you should read before starting with SQL Server.
• 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 OK button to continue.
  When working with data stored in SQL Server, you will need the help of sp_executesql statement in order to execute dynamic statements and stored procedures.
• 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 on any database server for restoring purpose.