Scripting MSSQL database schema and data

Recently ran into problems trying to export data from one MSSQL server to another. Turns out, you can't back up a database on a server running the 2012 copy and then restore it on a server running the 2008 copy.

To get arround this, I needed to script the entire database so that I could then run the script on the 2008 server and it was actually quite somple, I took the following steps:

In the MSSQL Management Console Object Explorer, right click on the database in question hover over 'Tasks' and then click 'Generate Scripts'.

Generate Scripts

It will allow you to generate scripts for one or all of the tables within your database. Just running through the wizard without changing any settings will only script the tables and this is where some people get confused.

On the first window, you need to select 'Script entire database and all database objects' then click 'Next >'.

Advanced Scripting Options

Once on the next screen, you need to make sure 'Output type' is set to 'Save scripts to a specific location', then choose a file name and click 'Advanced'.

If you're using anything older than 2008, your settings will look like this, you need to change 'Script Data' to 'True'.

alt text

However if you're using 2008 or newer, the option changes name and section, look for 'Types of data to script' , then change it to 'Schema and data'.

enter image description here


By Luke Alderton at 5 Aug 2014, 23:15 PM

Tags: MSSQL

Comments

Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Tags
Latest Comments
By dipbleds on Raspberry Pi - Bluetooth using Bluecove on Raspbian
12 Dec 2017, 21:29 PM
By Ich on Replacing the Xamarin Header/Navigation bar with a custom view/template
26 Nov 2017, 17:11 PM
By Ravi Motha on My experience at Umbraco UK Festival 2017
12 Dec 2017, 08:39 AM
By Borges on How to add a Xamarin Forms Loading Screen/Overlay
7 Nov 2017, 19:11 PM
By Rutul Mehta on Auto/Custom height on Xamarin Forms WebView for Android and iOS
4 Nov 2017, 03:15 AM
By Pablo on Xamarin MasterDetailPage has large margin/padding at top
26 Oct 2017, 15:31 PM
By faiza on Using MaryTTS or OpenMary in Java
22 Oct 2017, 12:54 PM
By Nietoperz on Using MaryTTS or OpenMary in Java
29 Sep 2017, 14:34 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development