Script Database With All Data From Microsoft SQL Server Management Studio

Script Database With All Data From Microsoft SQL Server Management Studio

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.

Generating SQL Script For Database with Full Data

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.

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 we 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 in 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.

Enter your email address

Subscribe to this blog and receive notifications of new posts by email. Join over 81,000 other subscribers

Don't worry we won't spam.

subscribers