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.
Custom Paging in SQL Server is a technique to make the data retirval fast and light weight. Here only the need data of the current page is fetched.
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.
Generate scripts for database objects
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.
Script entire database objects to script
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.
Advanced Scripting Options
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.
Schema and data
6. Click Next> 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.
Review your selections
Now you have the Full Database Script which you can run them on any database server for restoring purpose.