Jump to content

Copying User Accounts, Tables, Views, and Other Objects from One SQL Server Database to Another

0
  adfm's Photo
Posted Apr 22 2010 02:29 PM

If you manage a Microsoft SQL Server 2008 installation and would like to automate some of your migration tasks you'll appreciate this excerpt from William R. Stanek's Microsoft SQL Server 2008 Administrator's Pocket Consultant. In it you'll learn how to use the Script Wizard to copy user accounts, tables, views, and other objects from one database to another.


Using the Script Wizard, you can generate T-SQL scripts that allow you to re-create the objects contained in a specified database. Scripts can be written to the Query Editor window so that you can run them immediately, or they can be saved to files so that you can run them later. By running a script against a database other than the one from which it was generated, you can create copies of objects in other databases.

With SQL Server 2008 R1, you can create copies of objects by completing the following steps:

  1. Start SQL Server Management Studio, and then access the server of your choice.

  2. In the Object Explorer view, right-click the Management folder, and then select Generate Scripts. This starts the Generate SQL Server Scripts Wizard. Click Next.

  3. Select the database you want to script, and then click Next.

  4. Set the script options summarized in Table 16.1 to determine how the copy operation works, and then click Next.

  5. Select the objects you want to script, and then click Next. Objects you can script include database triggers, database roles, schemas, stored procedures, tables, user-defined functions, users, and views.

  6. You will see one Choose page for each type of object you select. Use these pages to choose the individual objects to script. For example, if you are scripting tables and views, you can choose the tables to script and then the views to script.

  7. Choose an output option. You can create the script as a file, copy it to the Clipboard, or send it to the New Query Editor window. Click Next when you are ready to continue.

  8. When you click Finish, the script is created and copied to the destination you specified. Click Close. You can then run the script as needed against a specified database. For example, if you are copying users from Customer to Projects, you insert USE PROJECTS at the beginning of the script before running it on the server containing the Projects database.

Table 16.1. Script Options for the Script Wizard

SCRIPT OPTION

DEFAULT

WHEN TRUE

ANSI Padding

True

Generates statements about ANSI padding.

Append To File

False

Appends the script to an existing file rather than overwriting the file.

Continue Scripting On Error

False

Continues writing the script if an error occurs.

Convert UDDTs To Base Types

False

Converts user-defined data types (UDDTs) to base types.

Generate Script For Dependent Objects

False

Scripts dependent objects.

Include Descriptive Headers

True

Includes descriptive header comments for each object scripted. (Does not affect how objects are created later; it only sets comments.)

Include If NOT EXISTS

False

Scripts the objects so that they are re-created only if they do not already exist.

Include System Constraint Names

False

Includes system-generated constraint names to enforce declarative referential integrity.

Include Unsupported Statements

False

Includes statements that are not supported on the specified server instance type (R2 only).

Schema Qualify Object Names

True

Prefixes object names with the object schema.

Script Bindings

False

Includes options to set binding.

Script Change Tracking

False

Includes the change-tracking information.

Script Check Constraints

True

Checks constraints for each table or view scripted.

Script Collation

False

Writes the collation settings of the object to the script.

Script Create

True

Creates designated objects (as opposed to dropping designated objects).

Script Data

False

Scripts the data for each table scripted.

Script Data Compression Options

False

Includes the data-compression information.

Script Defaults

True

Scripts the default values for the object.

Script Drop

False

Drops designated objects (as opposed to creating designated objects).

Script Extended Properties

True

Scripts the extended properties of objects.

Script For Server Version

SQL Server 2008 or SQL Server 2008 R2

Creates the script to be compatible with the specified SQL Server version.

Script For The Database Engine Type

Stand-alone Instance

Scripts only features compatible for the specified server instance type. You can specify a stand-alone instance or a SQL Azure database (R2 only).

Script Foreign Keys

True

Scripts foreign keys for each table or view scripted.

Script Full-Text Indexes

False

Scripts full-text indexes for each table or view scripted.

Script Indexes

False

Scripts indexes for each table or view scripted.

Script Logins

False

Scripts all logins available on the server. Passwords are not scripted.

Script Object-Level Permissions

False

Scripts permissions for the object as per the original database.

Script Primary Keys

True

Scripts primary keys for each table or view scripted.

Script Statistics

Do not script statistics

Controls whether statistics for table or indexed view objects are scripted.

Script Triggers

True

Scripts triggers for each table or view scripted.

Script Unique Keys

True

Scripts unique keys for each table or view scripted.

Script Use Database

True

Sets a USE statement with the name of the original database at the top of the script.

Types Of Data To Script

Schema only

Controls the types of included elements as schema only, schema and data, or data only (R2 only).


With R2, you can create copies of objects by completing the following steps:

  1. Start SQL Server Management Studio, and then access the server of your choice.

  2. In the Object Explorer view, expand Databases, right-click a database, point to Tasks, and then select Generate Scripts. This starts the Generate And Publish Scripts Wizard. If the Introduction page is displayed, click Next.

  3. Select the check boxes for the objects you want to script, and then click Next. Objects you can script include database triggers, database roles, schemas, stored procedures, tables, user-defined functions, users, and views. You can also choose to script the entire database and all database objects.

  4. Choose an output option. You can create the script as a file, publish it to a Web service such as SQL Azure, copy it to the Clipboard, or send it to a New Query Editor window.

  5. Click Advanced. Set the script options summarized in Table 16.1 to determine how the copy operation works, and then click Next. When you publish a script to a Web service, you have a similar (but reduced) set of options, and the default values are different in some instances. You also need to specify a provider and target database.

  6. Click Next to review the options, and then click Next again to create the script or publish it to the destination you specified.

  7. Review the save or publishing results. If any errors occurred, use the links provided to get more information, correct any problems, and then repeat this entire procedure. Optionally, click Save Report to generate a detailed report. Click Finish.



1 Reply

 : Oct 31 2012 11:43 AM
I know this is old, but I just needed it. In the wizard on SQL Server 2008 Standard, there is no place to select the database. it selects Master automatically. Master is set up much differently than the one I want to script. I only want to copy the Users, Roles, and Schemas. Any ideas?