Help
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:
Start SQL Server Management Studio, and then access the server of your choice.
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.
Select the database you want to script, and then click Next.
-
Set the script options summarized in Table 16.1 to determine how the copy operation works, and then click Next.
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.
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.
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.
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
With R2, 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 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.
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.
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.
-
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.
Click Next to review the options, and then click Next again to create the script or publish it to the destination you specified.
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.
Learn more about this topic from Microsoft® SQL Server® 2008 Administrator's Pocket Consultant.
Portable and precise, this pocket-sized guide delivers ready answers for administering SQL Server 2008. Zero in on core support and maintenance tasks using quick-reference tables, instructions, and lists. You’ll get the focused information you need to solve problems and get the job done—whether at your desk or in the field!
Get fast facts to:
- Plan, deploy, and configure SQL Server 2008
- Administer server and surface security, access, and network configuration
- Import, export, transform, and replicate data
- Use SQL Server Management Studio for core administrative tasks
- Manipulate schemas, tables, indexes, and views
- Automate maintenance and implement policy-based management
- Monitor server activity and tune performance
- Manage log shipping and database mirroring
- Perform backups and recovery
