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

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.
    Generate scripts for database objects

    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

    Script entire database objects to script

  • 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

    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

    Schema and data

  • 6. Click Next> button to continue.
  • 7. In the Review your selections (which is the last step) click Next > to generate your scripts.
    Review your selections

    Review your selections

Now you have the Full Database Script which you can run them on any database server for restoring purpose.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.