The Automating Common SQL Server Tasks using DMO article describes the sort of tasks that can be automated using SQL Server Distributed Management Objects (DMO), and provides practical examples of how to automate tasks such as enumerating all database on a given server, or creating a database on any given server.

This article extends further your automation toolkit, providing the following procedures:

• spDBTransfer
  • Copy an entire database within a server or between servers
  • Copy selected tables or stored procedures between databases

• spScriptObjects
  • Write out a complete build script for a database
  • Write out build scripts for every database on a server
  • Write out a series of source files for the objects in a database in a suitable format for source-control systems

• spDatabaseRoles
  • List the database roles and the users assigned to them, for a particular database

• spJobHistory
  • Check the jobs on a server for their success or otherwise and access the history records

Each of these procedures is designed to work either with SQL Server or Windows authentication. To download each of these procedures – which are fully commented and with example execution commands – simply, click the “Code Download” link in the box to the right of the article title.