Anytime you deploy multiple SQL Servers or multiple instances of SQL Server within an organization, you will need a way to handle routine server-to-server administration tasks. For example, if you have a database on one server, you may need to copy users from one server to another. SQL Server 2005 allows you to automate routine server-to-server administration tasks using scripts. You can write the scripts to the Query Editor or save them to a file for later use.
The server-to-server administration tasks you can automate include:
Copying users, tables, views, and other objects from one database to another.
Copying alerts, jobs, and scheduled jobs from one server to another.
The subsections that follow explain how you can automate these administration tasks.
Using the Script Wizard, you can generate T-SQL scripts that allow you to recreate the objects contained in a specified database. Scripts can be written to the Query Editor window so you can run them immediately, or they can be saved to files so you can run them later. By running the script against a database other than the one from which it was generated, you can create copies of objects in other databases.
You can create copies of objects by completing the following steps:
Start SQL Server Management Studio, and then access the server of your choice.
In Object Explorer view, right-click the Management folder, and then select Generate Scripts. This starts the Script Wizard. Click Next.
Select the database you want to script, and then click Next.
Set the script options summarized in Table 15-1 to determine how the copy operation works, and then click Next.
Table 15-1. Script Options for the Script Wizard
Script Option
Default
WhenTrue...
AppendTo File
False
Appends to an existing file rather than overwriting.
Continue Scripting On Error
False
Continues writing the script if an error occurs.
Convert UDDTs To Base Types
False
Converts user-defined data types to base types.
Generate Script For Dependent Objects
True
Scripts dependent objects.
Include Descriptive Headers
False
Includes descriptive header comments for each object scripted. (Does not affect how objects are created later, only sets comments.)
Include If NOT EXISTS
True
Scripts the objects so that they are only re-created if they do not already exist.
Script Behavior
Generate CREATE statements only
Script creates designated objects. (as opposed to dropping designated objects).
Script Check Constraints
True
Scripts check constraints for each table or view scripted.
Script Collation
False
Writes the collation settings of the object to the script.
Script Defaults
True
Scripts the default values for the object.
Script Extended Properties
True
Scripts the extended properties of objects.
Script for Server Version
SQL Server 2005
Creates the script to be compatible with the specified SQL Server version.
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 originaldatabase.
Script Owner
True
Scripts the owner for the object.
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
False
Sets a USE statement with the name of the original database at the top of the script.
Select the objects you want to script, and then click Next. Objects you can script include database roles, schema, tables, user-defined functions, shared stored procedures, users, and views.
You will have one Choose ... page for each type of object you selected. Use this page to choose the individual objects to script. For example, if you are scripting tables and views, you will be able to choose the tables to script and then the views to script.
Choose an output option. You can create the script as a file, copy it to the Windows clipboard, or send it to the New Query Editor window. Click Next when you are ready to continue.
When you click Finish, the script is created and copied to your chosen destination. 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 would insert USE PROJECTS at the beginning of the script before running it on the server containing the Projects database.
You use alerts, operators, and scheduled jobs to automate routine administration tasks. If you have already created alerts, operators, and jobs on one server, you can reuse them on another server. To do this, you would create a script for the alert, operator, or job you want to copy, and then run the script against a target server. You would then need to edit the job properties to ensure that they make sense for the target server. For example, if you created a set of jobs to periodically check the Support database and then added custom steps to handle various database states, you could copy these jobs to another server and then edit the job properties to apply the tasks to the Customer database on the target server.
You can copy alerts, operators, or jobs from one server to another server by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Expand the SQL Server Agent, and then expand the Alerts, Jobs, or Operators folder as appropriate for the type of object you are copying.
Right-click the alert, operator, or job, point to Script ... As, Create To, and then select File. In the Select A File dialog box, specify the save location and name for the T-SQL script file.
Connect to the server on which you want to create the new alert, operator, or job in Object Explorer view. Right-click the server in Object Explorer view, and then select New Query.
Click the Open File button or press Ctrl+O. Select the script file you previously created.
The script is set to use the msdb database because alert, job, and operator objects are stored in that database.
Click Execute or press F5 to run the script and create the object in the msdb database.