LibraryTech NotesCopy the Structure of a Database Schema

Database administrators, IT professionals, and others who organize large amounts of information often want to explore database enhancements without risking critical live data. A backup copy of a database structure is useful to:

• Act as a test platform for developing modifications to existing tables and relationships
• Assist in migrating data to a new server or database platform
• Create a snapshot of the database schema for backup or documentation

DatabaseSpy® 2012 is a multi-database query, design, and database compare tool with features that let you efficiently examine and copy database schemas. For this example we’ll use the AdventureWorks database with Microsoft SQL Server, however the same strategy and steps can be performed on any of the popular databases supported by DatabaseSpy.

Suppose you want to duplicate the tables in the AdventureWorks HumanResources schema. You can open AdventureWorks in the Online Browser window, shift-click to select all the tables in the HumanResources schema, then choose the SQL Editor Create option from the right-click context menu.

This selection generates a SQL script and opens it in a new DatabaseSpy® 2012 SQL Editor window. The script contains CREATE statements for each table.

Since you want to duplicate these tables in a new schema, you can use search and replace in the DatabaseSpy SQL Editor to replace all occurrences of HumanResources in the path that specifies each table location.

This replaces HumanResources everywhere in the script, including references to foreign keys that are in some of the CREATE statements.

Next, you can edit the script to add a CREATE SCHEMA statement at the top.

When you execute the script, the Message window alerts you that an error occurred.

You can move the cursor over the error message to identify the statement that failed.

Click image to enlarge screenshot

The CREATE statement for the EmployeeDepartmentHistory table failed because it tried to add a foreign key referencing the Shift table, but the Shift table did not yet exist. The Shift table was added by the last statement in the script.

Click image to enlarge screenshot

You can highlight the command that failed by clicking the error in the Message window. Whenever a portion of a script is highlighted the SQL Editor Execute button only processes tha selection. Since the Shift table does exist, you can now run the CREATE EmployeeDepartmentHistory statement successfully.

(If you want to save the SQL script to run again later, you could edit the order of the statements so they execute successfully on the first pass.)

Click the Browser Window Update button to display the new tables.

 Click here to download a fully functional, free 30-day trial of Altova DatabaseSpy® 2012!