Having scripts to recreate your SQL Server environment is a crucial part of
a good backup/recovery plan. It is possible to use the GUI to “Generate SQL Scripts…”.
This can be time consuming and prone to the drudgery and errors of manual procedures.
It is not possible to schedule script creation using the GUI. SQL_DMO is a
useful to automate the process and assure the quality and consistency of the scripts produced.
Here are a set of stored procedures that will script the objects in a database for use in
recreating or documenting that database.
There are two driver procedures. One to script collections of the server object
(dmoScriptServer) and another to script collections of the
database object (dmoScriptDatabase).
Select either driver name for a walk through of the code for that process.
The server objects that support the script method are “Jobserver.Jobs”, “Logins” and “Database”. I encountered numerous problems with the “Database” object’s script method and have decided to use an alternate method to produce the CREATE DATABASE scripts. Also, I will still need to build a script that sets the passwords for the logins to the correct values in the event I have to use the script(s) produced from the Logins object. This password script will have to be built manually and kept in a secure location.
The database object is much more effective in producing good scripts.
The collections in the database include tables, stored procedures, views, databaseroles,
and views. There are several collections within each table object (indexes,
constraints, triggers, DRI, …). One unfortunate issue that was reported is that objects that don’t belong to dbo are not scripted. I don’t have this problem because I don’t have any objects that aren’t owned by dbo.
Instructions for installing the scripts.
Load these scripts onto a SQL7 Standard or Enterprise. They won’t work on
Desktop Edition SQL Server because trusted security is used throughout.
(You can modify to use SQL Server Security by changing the dmoConnect
and modifying the single osql call, located in dmoScriptDatabases, to include
a login name and password
These scripts were all produced with the utility.
I decided not to put them in a zip file to FORCE you to
have to look at the code.
- A database to hold the process:
- admin
- Two tables needed for the CREATE DATABASE workaround:
- databaseFileInfo
- databaseFileGroupInfo
- A handful of stored procedures:
- script_user_xp
- dmoConnectServer
- dmoScriptServer
- dmoScriptServerCollection
- dmoScriptDatabase
- dmoScriptDatabaseCollection
- dmoScriptTables
- dmoScriptTableCollection
- sp_displayoaerrorinfo (from BOL – mostly)
- sp_hexadecimal (from BOL)
- ScriptCreateDatabase
- dmoDisconnectServer
Create the database – use the script or just simply create a database with the defaults using the
wizard. I will be posting additional scripts for inclusion into this database as time permits.
Create the tables – The tables should never have more than a dozen rows and are truncated before
each use, so indexes are not useful.
Create the Stored Procedures.
Determine the path to where you want the scripts to be stored. Create and non existing
folders to establish the path.
This how you call the procedures. Replace the word in the brackets and the brackets with valid
values from your environment.
If you want to script logins make a call to
exec admin..dmoScriptServer [server name], [output path]
If you want to script a database make a call to
exec admin..dmoScriptDatabase [server name], [database name], [output path]
If you want to script each object to a separate file the command would look like:
exec admin..dmoScriptServer [server name], [output path], 1
or
exec admin..dmoScriptDatabase [server name], [database name], [output path] , 1
Let me know if you have any problems , questions, or corrections.