By Bill Wunder Consulting Services
When used in conjunction with Microsoft's Visual SourceSafe 6 application included in their Visual Studio 6 Suite this utility will produce a historical record of T-SQL scripts for the user objects found within a specified Microsoft SQL Server 2000 instance.
The SQL Server connection uses the "Windows Authentication" method and expects the user to be a member of the sysadmins SQL Server server role on any server to be scripted.
A folder on the local file system should be set aside for exclusive use by the utility. All files stored in this folder or any subfolders are subject to deletion by the utility. The is important to protect the integrity of the Archive Utility's SourceSafe project hierarchy.
The User must provide the information necessary to connect to SourceSafe database and the path on the local system where scripts will be staged in text files for transfer to the SourceSafe database. The SourceSafe user must have the ability to add, check in, and delete items from SourceSafe. The utility never permanently destroys items that are deleted from SourceSafe. This means you can recover any script provided the deleted items are never purged through a method other than this utility.
The script format and the file system/SourceSafe sub-hierarchy is fixed so that format changes will not cause false differences as frequently happens when Enterprise Manager or Query Analyzer are routinely used to generate scripts.
The tool uses the SQL-DMO object model (sqldmo.dll) to pull scripts from the SQL Server database , the Scripting.FileSystemObject (wshom.ocx) to access the local file system, and the SourceSafe API (ssapi.dll) to interface with the SourceSafe database.
If a SourceSafe database is not available, the utility can be used to generate the SQL Server DDL scripts by disabling the Check In option in the menu, however the ability to see the history of changes will be lost.
This tool has two primary activities:
- Generate T-SQL scripts for the users objects on a SQL Server 2000 server to a local folder
- Synchronize a Visual SourceSafe 6 database to a local folder hierarchy
You may also be interested in the hierarchy used to store scripts in SourceSafe.
Generate T-SQL scripts details
The tool generates all scripts to be non-destructive. That is, if a script is executed, the object will be re-created exactly as it existed when the script was generated.
For all scripts this means:
the database object will be created in the original database
all permissions associated with the database object are included in the script
In addition, for table scripts this means:
the table is never dropped in the script to assure no loss of data
all indexes and constraints are included in the script
Each T-SQL statement within a script is isolated by the "GO" batch separator. This prevents other statements from failing if a particular statement becomes invalid. For example, if a the table script is run from Query Analyzer the indexes and permissions will be created even though the table already exists or if a user has been dropped from the database a procedure script will still recreate the procedure and grant permissions to all valid users.
Note that passwords from SQL Server logins are not available for scripting from the SQL Server so are not included in the scripts produced by this Archive Utility. It is recommended that the DBA maintain a secured copy of a script that sets the correct password for each SQL Server login. "Trusted" logins, of course, do not require this manual step. Also note that server configuration, server role membership information, user and role information in system databases nor user defined extended stored procedures are currently extracted by this utility.
Check In will add all folders --- as projects --- and all files --- as text items --- found in the local file system folder specified as the "Local Working Folder" that are not found beneath the matching SourceSafe project hierarchy you specify as the "SourceSafe Project Path" when configuring the Archive Utility's SourceSafe Connection information. Once all new objects are added to a project level, the utility will consider a Check In of each sub-project and file that exists in both the "Local Working Folder" and the "SourceSafe Project Path". If no changes are detected, the utility will simply undo the checkout resulting in no change to the SourceSafe archive. Naturally, if changes are detected, the new version of that script will be added to SourceSafe. After all new files are added and all files existing in both SourceSafe and the local directory are processed, the Archive Utility will perform the standard SourceSafe logical delete on all items found in the "SourceSafe Project Path" that are not in the "Local Working Folder". At the end of each Check In the scripts in SourceSafe will be an exact representation of the server.
Check In processing provides the optional feature (enabled by default) to produce an archive a log of each Check In that will show all objects added, updated, and deleted for each execution of the Check In Process. The Check In of this log will always result in a new version of that file in SourceSafe. The log is stored in the "SQL Server name" project in SourceSafe. This log file will provide a valuable view for audit and monitoring of change --- particularly useful on a multiple database server or where many users can implement changes. By examining this one item in SourceSafe it is possible to determine the frequency and time that each Check In occurred. compute how long each Check In took and , most importantly, identify all changes on the server detected by any execution of the Check In.
This representation shows what how the folders are laid out under the "Local Working Folder" that you specify and also haw the projects are laid out under the "SourceSafe Project Path" that you specify.
|
|___SQL Server name
|______AgentAlerts
|______AgentOperators
|______BackupDevices
|______Jobs
|______Logins
|
|______user database 1
| |______DataTypes
| |______Defaults
| |______Functions
| |______Procedures
| |______Roles
| |______Rules
| |______Tables
| |______Triggers
| |______Users
| |______Views
|
|______user database n
|______DataTypes
|______Defaults
|______Functions
|______Procedures
|______Roles
|______Rules
|______Tables
|______Triggers
|______Users
|______Views